MS SQL connection

[KatoXY]

Hi,
For a few days I'm trying connect to MSSQL Server. My test environment is easy: my computer on W10 with Autoit and second machine Windows 2016 Server with MS SQL 2016 Standard Edition.
When I use PowerShell, there is no problem. I can connect to this sql and do query.
When I use Autoit I get connection error.
 

1. I used ADO.au3 UDF  from mLipok and I get:

  

2. I use _sql.au3 from Chris Lambert and I get

 

Quote

[Microsoft][ODBC Driver Manager] data source name not found and no default driver specified
err.windescription:    an exception has occurre

 

What I do wrong? 

What is the easiest way to connect to SQL?

[Zedna]

Post your code and DSN you use ...

 

EDIT: I use this DSN

$DSN_MSSQL = "Driver={SQL Server};Server=%server%;Database=master;Uid=admin;Pwd=%pwd%;"

 

Edited August 5, 2020 by Zedna

[KatoXY]

Global $sDriver      = 'SQL Server'
Global $sDatabase    = 'MyMSDB' ; change this string to YourDatabaseName
Global $sServer      = 'MySQLSRV' ; change this string to YourServerLocation
Global $sUID         = 'localsql' ; change this string to YourServerLocation
Global $sPWD         = 'mypass' ; change this string to YourServerLocation

Global $sConnectionString = "DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUID &';PWD=' & $sPWD & ';"

Generally I use domain user but I created  local sql user additional. The same result: Connection error 5

[ajag]

Beware of the quotation marks

$sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUID &';PWD=' & $sPWD & ';'

 

[Zedna]

Original wrong:

Global $sConnectionString = "DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUID &';PWD=' & $sPWD & ';"

Fixed:

Global $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUID &';PWD=' & $sPWD & ';'

 

[KatoXY]

I replaced this line and still the same error

[ajag]

I think you have to show your code, not only the connection string.
This works for me to open a connection:
 

Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'
Local $oConnection = _ADO_Connection_Create()
_ADO_Connection_OpenConString($oConnection, $sConnectionString)

With $sDriver ="SQL Server"

[Zedna]

The simplest testing code:

$objErr = ObjEvent("AutoIt.Error","MyErrFunc")

$sServer = 'server1'
$sDatabase = 'master'
$sUID = 'admin'
$sPWD = 'pwd'

$DSN = 'DRIVER={SQL Server};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUID &';PWD=' & $sPWD & ';'
$oConn = ObjCreate ("ADODB.Connection")
$oConn.ConnectionTimeout = 3 ; default is 15 s (must be supported by data provider DSN)
$oConn.Open($DSN)
If @error Then Exit
$oConn.Close
MsgBox(64, 'OK', 'Finished')

Func MyErrFunc()
    MsgBox(48, 'COM Error', $objErr.description)
    SetError(1)
EndFunc

 

[KatoXY]

@Zedna it works. I get OK Finished. When I set wrong password I get error. Later I'll test with domain user.
Next question is how it use to sql query i.e.:

Quote

SELECT * FROM Userlist

 

[Zedna]

Here is more general example showing working with ADO directly without any includes:

expandcollapse popup

; https://www.w3schools.com/asp/ado_intro.asp

$objErr = ObjEvent("AutoIt.Error","MyErrFunc")

$sServer = 'server1'
$sDatabase = 'master'
$sUID = 'admin'
$sPWD = 'pwd'

$DSN = 'DRIVER={SQL Server};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUID &';PWD=' & $sPWD & ';'
$oConn = ObjCreate ("ADODB.Connection")
$oConn.ConnectionTimeout = 3 ; default is 15 s (must be supported by data provider DSN)
$oConn.Open($DSN)
If @error Then Exit

; simple select returns 1 value (1 column and 1 row: count() max() TOP 1)
$value = $oConn.Execute('select max(col1) from admin.table').Fields(0).Value
ConsoleWrite($value & @CRLF)

; simple SQL command INSERT/UPDATE/DELETE without return value
$oConn.Execute('update admin.table set col1 = value where key = id')

; general SELECT returns more columns/rows
$oRS = ObjCreate ("ADODB.Recordset")
$oRS.CursorType = 2
$oRS.LockType = 3

$oRS.Open ('select * from admin.table', $oConn)
If @error Then Exit

If $oRS.RecordCount Then ; -1
    While Not $oRS.EOF
        $value = $oRS.Fields(0).Value & ' | ' & $oRS.Fields(1).Value ; read only first and second column
        ConsoleWrite($value & @CRLF)
        $oRS.MoveNext
    WEnd
EndIf

; execute stored procedure, doesn't support input params and result set
Local $rowcount
$oConn.Execute('admin.sp_my_procedure', $rowcount, 132) ; adCmdStoredProc=4 + adExecuteNoRecords=0x80 (128)

$oRS.Close
$oConn.Close
MsgBox(64, 'OK', 'Finished')

Func MyErrFunc()
    MsgBox(48, 'COM Error', $objErr.description)
    SetError(1)
EndFunc

 

[mLipok]

@KatoXY When you back to forum please answer to question: Were you able to solve this problem with ADO.au3 UDF , or do you need further asistance ?

Edited November 9, 2020 by mLipok

[Zedna]

Just for the reference, here is simple but very good description/tutorial/reference of ADO on W3SCHOOLS:

https://www.w3schools.com/asp/ado_intro.asp

 

[robertocm]

On 8/7/2020 at 3:02 PM, Zedna said:

; execute stored procedure, doesn't support input params and result set
Local $rowcount
$oConn.Execute('admin.sp_my_procedure', $rowcount, 132) ; adCmdStoredProc=4 + adExecuteNoRecords=0x80 (128)

I'd try this for stored procedures returning a recordset:

Global Const $iCursorType = 3 ;0 adOpenForwardOnly, 3 adOpenStatic
Global Const $iLockType = 1 ;1 adLockReadOnly, 3 adLockOptimistic
Global Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call

$oRS.Open ("EXEC admin.sp_my_procedure '" & $StringParam & "', " & $NumParam ";", $oConn, $iCursorType, $iLockType, $iOptions)