SQL Connection failure

[Gertsch123]

Hi

I use the following code to connect to a SQL2000 DB on a remote server

global $DSN = "DRIVER={SQL Server};SERVER=MySvr;DATABASE=MyDB;UID=MyUser;PWD=MyPwd;"

$conn.Open($DSN)

but keep on getting the following error

C:\scripts\testdb.au3 (10) : ==> The requested action with this object has failed.:

$conn.Open($DSN)

$conn.Open($DSN)^ ERROR

->10:25:47 AutoIT3.exe ended.rc:1

>Exit code: 1 Time: 1.775

I'm sure the username, password and DB name I use is correct as well as the server name.

Any Ideas?

Thanks

Gert

[PsaltyDS]

Hi

I use the following code to connect to a SQL2000 DB on a remote server

global $DSN = "DRIVER={SQL Server};SERVER=MySvr;DATABASE=MyDB;UID=MyUser;PWD=MyPwd;"

$conn.Open($DSN)

but keep on getting the following error

C:\scripts\testdb.au3 (10) : ==> The requested action with this object has failed.:

$conn.Open($DSN)

$conn.Open($DSN)^ ERROR

->10:25:47 AutoIT3.exe ended.rc:1

>Exit code: 1 Time: 1.775

I'm sure the username, password and DB name I use is correct as well as the server name.

Any Ideas?

Thanks

Gert

Are you providing Windows credential or an SQL login? If it's an SQL login, add "Trusted_Connection=no;" to the string.

Also, is there more than one named instance of SQL on the server? If so, edit for: "SERVER=Mysvr\InstanceName;".

[Zedna]

Add COM event error handler.

Look here how to do it: http://www.autoitscript.com/forum/index.php?showtopic=81710

[Gertsch123]

Hi

Thanks Guys, I've made changes as sugested but sitll can't get it to work.

There is no other instances of SQL on the server so the SERVER\instance part does not applie though.

Here is the new code, I'm basicaly trying to get all the entries in the DB for a specific value

Code:

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

dim $Value = $cmdline[1]

dim $ValueArray[1]

global $conn = ObjCreate( "ADODB.Connection" )

global $DSN = "DRIVER={SQL Server};SERVER=192.168.0.252;DATABASE=BMS;UID=sa;PWD=sqlpassword;Trusted_Connection=no;PORT=3306;"

$conn.Open($DSN)

dim $rs1 = ObjCreate( "ADODB.RecordSet" )

$rs1.Open("Select * from clip where Filepath like '" & $Value & "%'",$conn)

With $rs1

While Not .eof

$SQLentry = $rs1.Fields("FilePath").value

_ArrayAdd($ValueArray,$SQLentry)

.movenext

WEnd

EndWith

$conn.close

_ArrayDisplay($ValueArray)

Func MyErrFunc()

$HexNumber=hex($oMyError.number,8)

Msgbox(0,"","We intercepted a COM Error !" & @CRLF & _

"Number is: " & $HexNumber & @CRLF & _

"Description is: " & $oMyError.description & @CRLF & _

"Windescription is: " & $oMyError.windescription )

$g_eventerror = 1; something to check for when this function returns

Endfunc

and the msgbox result:

We intercepted a COM error!

Number is:80020009

Discription is: Provider is not specified and there is no designated default provider.

Windescription is:

THanks for your suggestions.

Gert

PS: I'm using a 64bit Vista Machine - I read about a guy who had the same problem and forced the app to compile using an option specifically for x86 CPU instead of "any CPU". This sorted out the problem for him.

Is there any way to do the same in AutoIT?

Edited October 3, 2008 by Gertsch123

[Gertsch123]

Hi

Thanks Guys, I've made changes as sugested but sitll can't get it to work.

There is no other instances of SQL on the server so the SERVER\instance part does not applie though.

Here is the new code, I'm basicaly trying to get all the entries in the DB for a specific value

Code:

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

dim $Value = $cmdline[1]

dim $ValueArray[1]

global $conn = ObjCreate( "ADODB.Connection" )

global $DSN = "DRIVER={SQL Server};SERVER=192.168.0.252;DATABASE=BMS;UID=sa;PWD=sqlpassword;Trusted_Connection=no;PORT=3306;"

$conn.Open($DSN)

dim $rs1 = ObjCreate( "ADODB.RecordSet" )

$rs1.Open("Select * from clip where Filepath like '" & $Value & "%'",$conn)

With $rs1

While Not .eof

$SQLentry = $rs1.Fields("FilePath").value

_ArrayAdd($ValueArray,$SQLentry)

.movenext

WEnd

EndWith

$conn.close

_ArrayDisplay($ValueArray)

Func MyErrFunc()

$HexNumber=hex($oMyError.number,8)

Msgbox(0,"","We intercepted a COM Error !" & @CRLF & _

"Number is: " & $HexNumber & @CRLF & _

"Description is: " & $oMyError.description & @CRLF & _

"Windescription is: " & $oMyError.windescription )

$g_eventerror = 1; something to check for when this function returns

Endfunc

and the msgbox result:

We intercepted a COM error!

Number is:80020009

Discription is: Provider is not specified and there is no designated default provider.

Windescription is:

THanks for your suggestions.

Gert

PS: I'm using a 64bit Vista Machine - I read about a guy who had the same problem and forced the app to compile using an option specifically for x86 CPU instead of "any CPU". This sorted out the problem for him.

Is there any way to do the same in AutoIT?

I found the awnser, thanks

Just needed to add "provider=SQLOLEDB.1" to connection string.

Gert