Select Statement from SQL in Autoit

[CaptainBeardsEyesBeard]

So in my C# a working connection string looks like 

SqlConnection conn = new SqlConnection("Data Source=server\\server;Initial Catalog=database;Integrated Security=True");

with Windows Authentication selected in drop down 

 

So using this code and connection string (I input the integrated security to match my c#) 

$DSN = 'DRIVER={SQL Server};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';Integrated Security=True' & ';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)
_Example_5_MSSQL_WinAuth($sServer, $sDatabase, "Select * from QATable1")
If @error Then Exit
$oConn.Close
MsgBox(64, 'OK', 'Finished')

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

I get this error 

 

If I change the connection string (to remove the integrated security line)  to 

$DSN = 'DRIVER={SQL Server};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUID &';PWD=' & $sPWD & ';'

I get authentication error 

 

 

[CaptainBeardsEyesBeard]

1 hour ago, mLipok said:

btw.
in ADO_EXAMPLE.au3

there are:

Func _Example_MSSQL_SQLServerAuthorization()
    Local $sDriver = 'SQL Server'
    Local $sDatabase = 'YourBASENAME' ; change this string to YourDatabaseName
    Local $sServer = 'localhost\SQLExpress' ; change this string to YourServerLocation
    Local $sUser = 'sa' ; change this string to YourUserName
    Local $sPassword = 'AutoIt' ; change this string to YourPassword

    Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    _Example_1_RecordsetToConsole($sConnectionString, "Select * from SOME_TABLE")
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _Example_2_RecordsetDisplay($sConnectionString, "Select * from SOME_TABLE")
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _Example_3_ConnectionProperties($sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _Example_4_MSSQL_SQLAuth($sServer, $sDatabase, $sUser, $sPassword, "Select * from SOME_TABLE")
    Return SetError(@error, @extended)
EndFunc   ;==>_Example_MSSQL

Func _Example_MSSQL_WindowsAuthorization()
    Local $sDatabase = 'AdventureWorks2016_EXT' ; change this string to YourDatabaseName
    Local $sServer = 'localhost\SQLExpress' ; change this string to YourServerLocation

    _Example_5_MSSQL_WinAuth($sServer, $sDatabase, "Select * from person")
    Return SetError(@error, @extended)
EndFunc   ;==>_Example_MSSQL

 

I attempted the WinAuth function as below 

_Example_5_MSSQL_WinAuth('server\server', 'db', 'Select * from QATable1')

and the console returns 

! ---> @error=4  @extended=-2147352567 : _Example_MSSQL_SQLServerAuthorization()
>Exit code: 0

 

[mLipok]

if you have 

35 minutes ago, CaptainBeardsEyesBeard said:

SqlConnection conn = new SqlConnection("Data Source=server\\server;Initial Catalog=database;Integrated Security=True");

so in AutoIt this following is not accurate:

35 minutes ago, CaptainBeardsEyesBeard said:

$DSN = 'DRIVER={SQL Server};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';Integrated Security=True' & ';UID=' & $sUID &';PWD=' & $sPWD & ';'

as you should use:

$DSN = 'DRIVER={SQL Server};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';Integrated Security=True;'

IMO (not tested) you can even use the same connection string... I mean:

SqlConnection conn = new SqlConnection("Data Source=server\\server;Initial Catalog=database;Integrated Security=True");

can be converted to AutoIt like this:

$DSN = 'Data Source=server\\server;Initial Catalog=database;Integrated Security=True;'

 

Edited April 12, 2023 by mLipok

[Skeletor]

31 minutes ago, CaptainBeardsEyesBeard said:

I attempted the WinAuth function as below

_Example_5_MSSQL_WinAuth('server\server', 'db', 'Select * from QATable1')

and the console returns

! ---> @error=4  @extended=-2147352567 : _Example_MSSQL_SQLServerAuthorization()
>Exit code: 0

 

Error code 4 corresponds to "ADODB.Connection: Provider cannot be found. It may not be properly installed." This error typically occurs when the required OLE DB driver or ODBC driver for connecting to the Microsoft SQL Server is not installed on the system where the script is running.

[CaptainBeardsEyesBeard]

22 hours ago, Skeletor said:

Error code 4 corresponds to "ADODB.Connection: Provider cannot be found. It may not be properly installed." This error typically occurs when the required OLE DB driver or ODBC driver for connecting to the Microsoft SQL Server is not installed on the system where the script is running.

Many thanks. Does that mean I have to create an ODBC connection to the SQL? 

I created one under USerDSN tab (using test button to test connection worked) but still get same error 

 

[mLipok]

Your screenshot shows "SQL Server" which is "DRIVER" and this is not "PROVIDER"

  

22 hours ago, mLipok said:

$DSN = 'DRIVER={SQL Server};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';Integrated Security=True;'

 

and example from:

https://www.connectionstrings.com/sql-server-native-client-11-0-oledb-provider/

Quote

Provider=SQLNCLI11;Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=yes;

 

Edited April 13, 2023 by mLipok

[mLipok]

23 hours ago, CaptainBeardsEyesBeard said:

I attempted the WinAuth function as below 

_Example_5_MSSQL_WinAuth('server\server', 'db', 'Select * from QATable1')

and the console returns 

! ---> @error=4  @extended=-2147352567 : _Example_MSSQL_SQLServerAuthorization()
>Exit code: 0

did you enable:

; You can use internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)

What you get in 

Quote

ADO.au3 v.2.1.20 BETA (1328) : ==> COM Error intercepted !
$oADO_Error.description is: ....
....
....

 

[mLipok]

also try to change False:

_ADO_Connection_OpenMSSQL($oConnection, $sServer, $sDatabase, Default, Default, 'YourAppName', @ComputerName & '_' & 'YourProgram_UID', False)

to True:

_ADO_Connection_OpenMSSQL($oConnection, $sServer, $sDatabase, Default, Default, 'YourAppName', @ComputerName & '_' & 'YourProgram_UID', True)

 

[Skeletor]

I think at the end of it all if you cannot connect, rather use SQLCMD [ if the server admin permits it ]

; INCASES WHERE YOUR QUERY IS COMPLEX
; AND REQUIRES MULTIPLE LINES
; RATHER CREATE AN SQL FILE
FileWrite("C:\path\to\QueryFile.sql", "SELECT * " & @CRLF)
FileWrite("C:\path\to\QueryFile.sql", "FROM Table" & @CRLF)

; CREATE A BATCH FILE THAT WILL USE SQLCMD WHICH EXECUTES THE QUERY FILE.
FileWrite("C:\path\to\Batch.bat", 'SQLCMD -S SERVER\INSTANCE -i "C:\path\to\QueryFile.sql" -d DataBaseName')

; RUN THE BATCH SCRIPT
RunWait(@ComSpec & " /c " & '"C:\path\to\Batch.bat"', "", @SW_HIDE)

 

Edited April 13, 2023 by Skeletor
More detailed script