ADODB.Connection error

[Nas]

Hello Guys,

 

I am running the Ado example script, if I run this line, it runs without errors :

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

But when I try the windows auth one , it failed everytime :

Func _Example_MSSQL_WindowsAuthorization()
    Local $sDatabase = 'AdventureWorks2016' 
    Local $sServer = 'SQLSRV' 
    _Example_5_MSSQL_WinAuth($sServer, $sDatabase, "Select * from Person.Address")
EndFunc

here is the error :

 

ADO.au3 v.2.1.16 BETA (1204) : ==> COM Error intercepted !
$oADO_Error.description is:     Provider cannot be found. It may not be properly installed.
$oADO_Error.windescription:     Exception occurred.

$oADO_Error.number is:     80020009
$oADO_Error.lastdllerror is:     0
$oADO_Error.scriptline is:     1204
$oADO_Error.source is:     ADODB.Connection
$oADO_Error.helpfile is:     C:\Windows\HELP\ADO270.CHM
$oADO_Error.helpcontext is:     1240655

By the way, I have 64x windows server 2016 with SQL Server , I already installed the odbc my sql connector, any thought on this will be greatly appreciate it.

Edited December 14, 2019 by Nas

[water]

You could run the script provided here to check all installed providers.

[Nas]

Result

[jchd]

28 minutes ago, Nas said:

I have 64x windows server 2016 with SQL Server , I already installed the odbc my sql connector

SQL Server != MySQL

Edit: never mind, I must have misundertood you.

Edited December 14, 2019 by jchd

[water]

Can you please provide the full scripts you are using?
 

[Nas]

@jchd Upon looking online, I found some people saying that I will need it to run that example script.

[jchd]

It only depends on the DMBS engine you're using.  ADO driver for Oracle, ADO driver for DB2, ADO driver for SQLite, ...  Each one is different and specialized to interface a given engine.

[Nas]

expandcollapse popup

#AutoIt3Wrapper_UseX64=Y

#include "ADO.au3"
#Tidy_Parameters=/sort_funcs /reel
#AutoIt3Wrapper_Run_AU3Check=Y
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7

#AutoIt3Wrapper_Run_Au3Stripper=Y
#Au3Stripper_Parameters=/RM

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>

_ADO_EVENTS_SetUp(True)

; SetUP internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

; You can use your own COMErrorHandler instead internal ADO.au3 UDF COMError Handler
;~ _ADO_ComErrorHandler_UserFunction(_ErrFunc)

; Uncomment one of the following examples
;
;~ _Example_MSAccess()
;~ _Example_MSExcel()
_Example_MSSQL_SQLServerAuthorization()
_Example_MSSQL_WindowsAuthorization()
;~ _Example_MSSQL_COMMAND_StoredProcedure()
;~ _Example_MySQL()
;~ _Example_PostgreSQL()
;~ _Example_Firebird


Func _Example_MSExcel()

    Local $sFileFullPath = Default ; Here put FileFullPath to your Excel File or use Default to open FileOpenDialog
    Local $sProvider = Default
    Local $sExtProperties = Default
    Local $HDR = Default
    Local $IMEX = Default

    Local $sConnectionString = _ADO_ConnectionString_Excel($sFileFullPath, $sProvider, $sExtProperties, $HDR, $IMEX)

    _Example_1_RecordsetToConsole($sConnectionString, "select * from [Sheet1$]")
    _Example_2_RecordsetDisplay($sConnectionString, "select * from [Sheet1$]")
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_MSExcel

Func _Example_MSSQL_SQLServerAuthorization()
    Local $sDriver = 'SQL Server'
    Local $sDatabase = 'AdventureWorks2016' ; change this string to YourDatabaseName
    Local $sServer = 'SQLSRV' ; change this string to YourServerLocation
    Local $sUser = 'sa' ; change this string to YourUserName
    Local $sPassword = 'P@ssword' ; change this string to YourPassword

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

    _Example_1_RecordsetToConsole($sConnectionString, "Select * from Person.Address")
    _Example_2_RecordsetDisplay($sConnectionString, "Select * from Person.Address")
    _Example_3_ConnectionProperties($sConnectionString)

    _Example_4_MSSQL_SQLAuth($sServer, $sDatabase, $sUser, $sPassword, "Select * from Person.Address")

EndFunc   ;==>_Example_MSSQL

Func _Example_MSSQL_WindowsAuthorization()
    Local $sDatabase = 'AdventureWorks2016' ; change this string to YourDatabaseName
    Local $sServer = 'SQLSRV' ; change this string to YourServerLocation

    _Example_5_MSSQL_WinAuth($sServer, $sDatabase, "Select * from Person.Address")

EndFunc   ;==>_Example_MSSQL

Func _Example_MSSQL_COMMAND_StoredProcedure()
    Local $oCOMERRHander = ObjEvent("AutoIt.Error", _ADO_COMErrorHandler)
    #forceref $oCOMERRHander

    Local $sDriver = 'SQL Server'
    Local $sDatabase = 'baza' ; 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 & ';'

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $oCommand = _ADO_Command_Create($oConnection, $ADO_adCmdStoredProc)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Create procedure (local temp procedure    )
    ; https://msdn.microsoft.com/pl-pl/library/ms188655(v=sql.110).aspx
    Local $sQUERY1 = _
            "CREATE PROCEDURE #testing1 @Param1 varchar(1), @Result1 varchar(1000) OUTPUT" & @CRLF & _
            "AS" & @CRLF & _
            "   SET @Param1 =1" & @CRLF & _
            "   SET @Result1 = 'Starting @@TRANCOUNT=' +  CAST(@@TRANCOUNT AS VARCHAR(10)) + CHAR(10)" & @CRLF & _
            "IF @Param1 = 1" & @CRLF & _
            "BEGIN" & @CRLF & _
            "   SET @Result1 = @Result1 + '@Param1 = ' + @Param1 + CHAR(10)" & @CRLF & _
            "END" & @CRLF & _
            ""
    _ADO_Execute($oConnection, $sQUERY1)

    _ADO_Command_CreateParameter($oCommand, '@Param1', 1, 1, $ADO_adChar, $ADO_adParamInput)
    If @error Then MsgBox($MB_ICONERROR, '@Param1', _
            '@error = ' & @error & @CRLF & '@extended = ' & @extended)

    _ADO_Command_CreateParameter($oCommand, '@Result1', 1000, Default, $ADO_adChar, $ADO_adParamOutput)
    If @error Then MsgBox($MB_ICONERROR, '@Result1', _
            '@error = ' & @error & @CRLF & '@extended = ' & @extended)

    Local $oParameters_coll = $oCommand.parameters
    If @error Then MsgBox($MB_ICONERROR, 'Parameters', '@error = ' & @error & @CRLF & '@extended = ' & @extended)

    ; Enumerate parameters to check if are properly added
    ; Local $iParam_count = $oParameters_coll.count
    For $oParameter In $oParameters_coll
        ConsoleWrite($oParameter.name & @CRLF)
    Next

    Local $oRecordset = _ADO_Command_Execute($oCommand, "#testing1")

    MsgBox($MB_ICONINFORMATION, '@Result1', $oCommand.Parameters.Item("@Result1").Value)

    Return $oRecordset

EndFunc   ;==>_Example_MSSQL_COMMAND_StoredProcedure

Func _Example_MySQL()
    ; Link to Windows MySQL ODBC drivers
    ; https://dev.mysql.com/downloads/connector/odbc/

    Local $sDriver = 'MySQL ODBC 5.3 ANSI Driver' ; 'MySQL ODBC 5.3 UNICODE Driver'
    Local $sServer = 'localhost' ; change this string to YourServerLocation
    Local $sDatabase = 'world' ; change this string to YourDatabaseName
    Local $sPort = '3306' ; change this string to If your Server use non standard PORT
    Local $sUser = 'AutoIt' ; change this string to YourUserName
    Local $sPassword = 'AutoIt' ; change this string to YourPassword

    ; Local $sConnectionString = 'Driver={' & $sDriver & '};SERVER=' & $sServer & ';PORT=' & $sPort & ';DATABASE=' & $sDatabase & ';User=' & $sUser & ';Passwd=' & $sPassword & ';'
    Local $sConnectionString = _ADO_ConnectionString_MySQL($sUser, $sPassword, $sDatabase, $sDriver, $sServer, $sPort)

;~  _Example_1_RecordsetToConsole($sConnectionString, "SELECT * FROM city")
;~  _Example_2_RecordsetDisplay($sConnectionString, "SELECT * FROM country WHERE `region` LIKE '%Europe%'")
    _Example_2_RecordsetDisplay($sConnectionString, "SELECT Name , CountryCode , District , Population FROM city WHERE name='Cary'")
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_MySQL

Func _Example_PostgreSQL()
    ; http://www.tutorialspoint.com/postgresql/index.htm

;~  Local $sDriver = 'PostgreSQL ODBC Driver(ANSI)'
    Local $sDriver = 'PostgreSQL ANSI'
    Local $sDatabase = 'postgres' ; change this string to YourDatabaseName
    Local $sServer = 'localhost' ; change this string to YourServerLocation
    Local $sPort = '5432' ; change this string to If your Server use non standard PORT
    Local $sUser = 'postgres' ; change this string to YourUserName
    Local $sPassword = 'AutoIt' ; change this string to YourPassword

    #cs
        Local $sDSN = 'PostgreSQL35W'
        Local $sConnectionString = 'DSN=' & $sDSN & ';DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'
    #ce
    Local $sConnectionString = 'Driver={' & $sDriver & '};DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    Local $aSchema_Catalogs = _ADO_Schema_GetAllCatalogs($oConnection)
    _ADO_Recordset_Display($aSchema_Catalogs, '$aSchema_Catalogs')

    Local $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, 'postgres')
    _ADO_Recordset_Display($oRecordset_Tables)
    If @error Then MsgBox($MB_ICONERROR, '_ADO_Recordset_Display OpenSchema_Tables', _
            '@error = ' & @error & @CRLF & '@extended = ' & @extended)

    _Example_1_RecordsetToConsole($sConnectionString, 'Select * from "SOME_TABLE"')
    _Example_2_RecordsetDisplay($sConnectionString, 'Select * from "SOME OTHER TABLE"')
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_PostgreSQL

Func _Example_Firebird()

    ; http://firebirdsql.org/pdfmanual/Firebird-2.5-QuickStart.pdf
    ; https://www.autoitscript.com/forum/topic/180850-adoau3-udf-beta-support-topic/?do=findComment&comment=1319831

    Local $sDSN = 'Firebird' ; Default
    Local $sDatabase = @ScriptDir & '\firebird\showDB_ado.fdb' ; db name
    Local $sServer = 'localhost' ; Server IP
    Local $sPort = '3050' ; Port
    Local $sUser = 'sysdba' ; DEFAULT Username
    Local $sPassword = 'masterkey' ; DEFAULT Password

    Local $sConnectionString = 'DSN=' & $sDSN & ';DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    ConsoleWrite("_Example_Firebird " & $sConnectionString & @CRLF)
;   _ADO_Execute("create database " & $sDatabase & " page_size 8192 " & $sUser & $sPassword)
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_Firebird

#Region Common / internal
Func _Example_1_RecordsetToConsole($sConnectionString, $sQUERY)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Executing some query
    Local $oRecordset = _ADO_Execute($oConnection, $sQUERY)

    ; Get recordset to array of arrays (Conent and ColumnNames)
    Local $aRecordsetAsArray = _ADO_Recordset_ToArray($oRecordset, False)

    ; Get inner array - only conent of Recordset
    Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRecordsetAsArray)

    ; Go through the array variable (Recorset Conent)
    Local $iColumn_count = UBound($aRecordsetContent, $UBOUND_COLUMNS)
    For $iRecord_idx = 0 To UBound($aRecordsetContent) - 1
        ConsoleWrite('==================================================================' & @CRLF)
        For $iColumn_idx = 0 To $iColumn_count - 1
            ConsoleWrite($aRecordsetContent[$iRecord_idx][$iColumn_idx] & @CRLF)
        Next
    Next

    ; Clean Up
    $oRecordset = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

EndFunc   ;==>_Example_1_RecordsetToConsole

Func _Example_2_RecordsetDisplay($sConnectionString, $sQUERY)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Executing some query directly to Array of Arrays (instead to $oRecordset)
    Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True)

    ; Clean Up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

    ; Display Array Content with column names as headers
    _ADO_Recordset_Display($aRecordset, 'Recordset content')

EndFunc   ;==>_Example_2_RecordsetDisplay

Func _Example_3_ConnectionProperties($sConnectionString)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Get all connection properties to Array
    Local $aProperties = _ADO_Connection_PropertiesToArray($oConnection)

    ; Clean Up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

    ; Show connection properties
    _ArrayDisplay($aProperties, "ADO connection - List of properties", "", 0, Default, "Name|Type|Value|Attributes")

EndFunc   ;==>_Example_3_ConnectionProperties

Func _Example_4_MSSQL_SQLAuth($sServer, $sDatabase, $sUser, $sPassword, $sQUERY)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenMSSQL($oConnection, $sServer, $sDatabase, $sUser, $sPassword, 'YourAppName', @ComputerName & '_' & 'YourProgram_UID')
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Executing some query directly to Array of Arrays (instead to $oRecordset)
    Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True)

    ; Clean Up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

    ; Display Array Content with column names as headers
    _ADO_Recordset_Display($aRecordset, 'Recordset content')

EndFunc   ;==>_Example_4_MSSQL_SQLAuth

Func _Example_5_MSSQL_WinAuth($sServer, $sDatabase, $sQUERY)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenMSSQL($oConnection, $sServer, $sDatabase, '', '', 'YourAppName', @ComputerName & '_' & 'YourProgram_UID', false)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Executing some query directly to Array of Arrays (instead to $oRecordset)
    Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True)

    ; Clean Up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

    ; Display Array Content with column names as headers
    _ADO_Recordset_Display($aRecordset, 'Recordset content')

EndFunc   ;==>_Example_4_MSSQL_SQLAuth
#EndRegion Common / internal

Func _ErrDescription($sDescription = Default)
    Local Static $sDescription_static = ''
    If $sDescription <> Default Then $sDescription_static = $sDescription
    Return $sDescription_static
EndFunc   ;==>_ErrDescription

Func _ErrFunc($oError)
    ConsoleWrite( _
            @ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & _
            "$oError.description is: " & @TAB & $oError.description & @CRLF & _
            "$oError.windescription: " & @TAB & $oError.windescription & @CRLF & _
            "$oError.number is: " & @TAB & Hex($oError.number, 8) & @CRLF & _
            "$oError.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            "$oError.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            "$oError.source is : " & @TAB & $oError.source & @CRLF & _
            "$oError.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            "$oError.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF _
            )
    _ErrDescription($oError.description) ; store description to use it outsided UDF in your own function
EndFunc   ;==>_ErrFunc

This is it.

I am only running these two that's it :

_Example_MSSQL_SQLServerAuthorization()
_Example_MSSQL_WindowsAuthorization()

don't bother by the rest.

[Nas]

3 minutes ago, jchd said:

It only depends on the DMBS engine you're using.  ADO driver for Oracle, ADO driver for DB2, ADO driver for SQLite, ...  Each one is different and specialized to interface a given engine.

What about Microsoft SQL Server?

[jchd]

I don't touch anything related to DBMSs & MS, sorry.

[Nas]

1 minute ago, jchd said:

I don't touch anything related to DBMSs & MS, sorry.

Merci quand meme, et bon fin de semaine alors.

[water]

Function _Example_MSSQL_SQLServerAuthorization needs to be modified. That's why you get the error message.

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 = 'AutoIt' ; 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")
    _Example_2_RecordsetDisplay($sConnectionString, "Select * from SOME_TABLE")
    _Example_3_ConnectionProperties($sConnectionString)

    _Example_4_MSSQL_SQLAuth($sServer, $sDatabase, $sUser, $sPassword, "Select * from SOME_TABLE")

EndFunc   ;==>_Example_MSSQL

 

[Nas]

I did and that one works fine for me, it's just this one that doesn't  :

_Example_MSSQL_WindowsAuthorization()

[Nas]

Func _Example_MSSQL_SQLServerAuthorization()
    Local $sDriver = 'SQL Server'
    Local $sDatabase = 'AdventureWorks2016' ; change this string to YourDatabaseName
    Local $sServer = 'SQLSRV' ; change this string to YourServerLocation
    Local $sUser = 'sa' ; change this string to YourUserName
    Local $sPassword = 'P@ssword' ; change this string to YourPassword

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

    _Example_1_RecordsetToConsole($sConnectionString, "Select * from Person.Address")
    _Example_2_RecordsetDisplay($sConnectionString, "Select * from Person.Address")
    _Example_3_ConnectionProperties($sConnectionString)

    _Example_4_MSSQL_SQLAuth($sServer, $sDatabase, $sUser, $sPassword, "Select * from Person.Address")

EndFunc   ;==>_Example_MSSQL

Func _Example_MSSQL_WindowsAuthorization()
    Local $sDatabase = 'AdventureWorks2016' ; change this string to YourDatabaseName
    Local $sServer = 'SQLSRV' ; change this string to YourServerLocation

    _Example_5_MSSQL_WinAuth($sServer, $sDatabase, "Select * from Person.Address")

EndFunc   ;==>_Example_MSSQL

 

[TheXman]

It's a simple question but no one has has asked it yet.  Can you log on locally to your SQL Server, using SQL Server Management Studio (SSMS) or any other local SQL server management tool, using Windows Authorization and access that database?  Windows Authorization may not be enabled/configured on the SQL Server.  So the best way to tell is to try it using SSMS.  If you can't do it with SSMS, then you won't be able to do it with your script.

Edited December 14, 2019 by TheXman

[Nas]

@TheXman  that was the first thing I checked and yes I am able to login with windows auth using SSMS.

[Nas]

@water, guys any feedback please ?

[water]

No idea how to solve this problem
But maybe the author of the ADO UDF, @mLipok finds some time to have a look?

[mLipok]

Sure. I will look to this today.

[mLipok]

Try  to not use X64 AutoIt. 

...or...

Install 64bit driver / provider.

Edited December 18, 2019 by mLipok