Solution to some ADODB.Connection problems for SQL Server

[Tippex]

I had a problem getting a connection to "SQL Server" on 64 bit PCs (running XP) even though 32 bit PCs connected easily.

Even compiling for 32bit was (initially) insufficient and to get it working at all on a 64bit PC, I ended up forcing its use of TCP/IP protocol by specifying a port number (that is apparently picked up as a default by 32 bit PCs once "SQL Server" has been added with it configured in "Control Panel --> Administrative Tools --> Databases(ODBC) --> Add --> SQL Server --> Client Configuration"

This worked for me on a 64bit PC, but only when compiled in 32bit:

$sqlCon = ObjCreate("ADODB.Connection")

$sqlCon.Open("DRIVER={SQL Server};SERVER=name.of.server,port_number;DATABASE=name_of_database;UID=logon_ID;PWD=password;")

If @error Then
 MsgBox(0, "Problem", "SQL Connection Failed")
Else
 MsgBox(0, "Success", "SQL Connection OK")
EndIf

Also, I found that the "SQL Server" needed to be added to the Databases(ODBC) as shown above (and tested OK) before the AutoIT script worked but (strangely) it could be later removed from Databases(ODBC) and it still worked (perhaps it just needed to setup defaults).

Does anyone know how I can get the above working when compiled in 64bit too?

On a X64 bit PC, the above script it will fail to work with:

#AutoIt3Wrapper_UseX64=y

but works OK with

#AutoIt3Wrapper_UseX64=n

Edited October 15, 2013 by Tippex

[Jos]

Which version of the SQL driver do you have installed?

Have you tested this COM ODBC connection with VBscript or some other x64 software to establish it is really AutoIt3 giving the problem?

I have closed you bug report for now as I am not convinced this is an AutoIt3 issue.

Jos

[TXTechie]

It is a function of the bitness of the SQL Server ODBC component/driver that is installed. It looks like the 32-bit version of the SQL Server ODBC component/driver installed, and so, you must compile your script for a 32-bit executable. If the 64-bit version of the SQL Server ODBC component/driver was installed, then you would have to compile your script to a 64-bit executable - otherwise you would receive similar errors and it would not work if compiling to a 32-bit executable.

Edited October 16, 2013 by TXTechie

[mLipok]

I think the link below, although it does not apply to straight SQL, it has a lot to explain.

http://www.connectionstrings.com/using-jet-in-64-bit-environments/