Database Issue Windows 7 & Windows XP

[Merrik]

I'm using the code below and it works fine on my Windows 7 system, but won't work on my XP system

I get "Error: the requested action with this object has failed".

On Windows 7 it works fine and updates the database.

Any thoughts are appreciated.

<autoit>

$sServer="MSSQLSERVER"

$sDatabase="MYDB"

$fAuthMode = 0

$sUsername = ""

$sPassword = ""

$sDriver = "{SQL Server}"

Local $sTemp = StringMid($sDriver, 2, StringLen($sDriver) - 2)

Local $sKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $sVal = RegRead($sKey, $sTemp)

If @error or $sVal = "" Then SetError(2, 0, 0)

$oConn = ObjCreate("ADODB.Connection")

If NOT IsObj($oConn) Then SetError(3, 0, 0)

If $fAuthMode Then $oConn.Open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";")

If NOT $fAuthMode Then $oConn.Open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase)

If @error Then SetError(1, 0, 0)

If IsObj($oConn) Then

$adoSQL = "INSERT INTO dbo.WinXP VALUES ('" & $sTime & "','" & $fTime & "')"

$oConn.Execute($adoSQL)

else

MsgBox(0, "AutoIT-SQL Result", "connection failed")

EndIf

</autoit>

Edited September 15, 2011 by Merrik

[water]

What line raises the error?

I would suggest to improve error handling a bit. if you get an error you simply set @error and then go on with the next statement. Change that to a MsgBox or whatever so you know that and where an error occures.

You need a COM error handler. Check the "COM Error Handler example" in the help file for ObjEvent.

[water]

This line

Local $sKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $sVal = RegRead($sKey, $sTemp)

will raise a syntax error.

That means that the script you posted can't be the script that runs on Windows 7.

Edited September 15, 2011 by water

[Merrik]

Well Water, it does work fine on Windows 7.

The database is on a remote domain.

If I use a database on my domain, it works fine on both Windows 7 and XP.

[Merrik]

C:\Documents and Settings\USERNAME\My Documents\SQL-Defrag-Test.au3 (154) : ==> The requested action with this object has failed.:

If NOT $fAuthMode Then $oConn.Open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase)

If NOT $fAuthMode Then $oConn.Open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase)^ ERROR

This only happens when crossing a domain with XP. It works on Windows 7

[water]

Ok, I think a bit of Dr. Cox or Valik attitude is needed here

I told you that the script you posted could never be run successfully because there is a syntax error. So you post a different script than you run on your computers, right?

I told you that your script is b......t when it comes to error handling. Your error handling is the equivalent to the VB "On Error resume next"

I told you to add COM error handling and showed you where to find an example.

So why don't you do what I suggest so that we get more useful information to track down the problem?

[Merrik]

Here is the whole script. I ran on Windows 7 X86 and a Windows 7 X64 and it works. Will not work on XP crossing a domain

<autoit>

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****

#AutoIt3Wrapper_UseX64=n

#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****

;==>_Open Connection

$sServer="Server\ins1"

$sDatabase="dbo.MYDB"

$fAuthMode = 0

$sUsername = ""

$sPassword = ""

$sDriver = "{SQL Server}"

Local $sTemp = StringMid($sDriver, 2, StringLen($sDriver) - 2)

Local $sKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $sVal = RegRead($sKey, $sTemp)

If @error or $sVal = "" Then SetError(2, 0, 0)

$oConn = ObjCreate("ADODB.Connection")

If NOT IsObj($oConn) Then SetError(3, 0, 0)

If $fAuthMode Then $oConn.Open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";")

If NOT $fAuthMode Then $oConn.Open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase)

If @error Then SetError(1, 0, 0)

;==>_Insert Text into Database

If IsObj($oConn) Then

MsgBox(0, "AutoIT-SQL Result", "We have a connection")

$adoSQL = "INSERT INTO dbo.Test VALUES ('SomeName', '07/01/2011 12:00:00 AM')"

;$adoSQL = "SELECT * FROM dbo.Sample_sites"

$oConn.Execute($adoSQL)

else

MsgBox(0, "AutoIT-SQL Result", "connection failled")

EndIf

;==>_Close Connection

If IsObj($oConn) Then

$oConn.Close

MsgBox(0, "AutoIT-SQL Result", "Discconected")

Else

MsgBox(0, "AutoIT-SQL Result", "Discconect failed")

EndIf

</autoit>

[water]

Please try this and post the error messages you get:

expandcollapse popup

#region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_UseX64=n
#endregion ;**** Directives created by AutoIt3Wrapper_GUI ****
 
Global $g_eventerror = 0    ; to be checked to know if com error occurs. Must be reset after handling.
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")    ; Initialize a COM error handler
 
;==>_Open Connection
$sServer = "Server\ins1"
$sDatabase = "dbo.MYDB"
$fAuthMode = 0
$sUsername = ""
$sPassword = ""
$sDriver = "{SQL Server}"
Local $sTemp = StringMid($sDriver, 2, StringLen($sDriver) - 2)
Local $sKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers"
Local $sVal = RegRead($sKey, $sTemp)
If @error Or $sVal = "" Then Exit MsgBox(16, "SQL Connect Skript", "1: Error ocurred. @error = " & @error & ", $sVal = " & $sVal)
$oConn = ObjCreate("ADODB.Connection")
If @error Or Not IsObj($oConn) Then Exit MsgBox(16, "SQL Connect Skript", "2: Error ocurred. @error = " & @error & ", IsObj($oConn) = " & IsObj($oConn))
If $fAuthMode Then
    $oConn.Open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";")
Else
    $oConn.Open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase)
EndIf
If @error Then Exit MsgBox(16, "SQL Connect Skript", "3: Error ocurred. @error = " & @error & ", $fAuthMode = " & $fAuthMode)
;==>_Insert Text into Database
If IsObj($oConn) Then
    MsgBox(0, "AutoIT-SQL Result", "We have a connection")
    $adoSQL = "INSERT INTO dbo.Test VALUES ('SomeName', '07/01/2011 12:00:00 AM')"
    ;$adoSQL = "SELECT * FROM dbo.Sample_sites"
    $oConn.Execute($adoSQL)
    If @error Then Exit MsgBox(16, "SQL Connect Skript", "4: Error ocurred. @error = " & @error)
Else
    MsgBox(0, "AutoIT-SQL Result", "connection failled")
EndIf
;==>_Close Connection
If IsObj($oConn) Then
    $oConn.Close
    MsgBox(0, "AutoIT-SQL Result", "Discconected")
Else
    MsgBox(0, "AutoIT-SQL Result", "Discconect failed")
EndIf
 
; This is my custom defined error handler
Func MyErrFunc()
 
    MsgBox(0, "AutoItCOM Test", "We intercepted a COM Error !" & @CRLF & @CRLF & _
            "err.description is: " & @TAB & $oMyError.description & @CRLF & _
            "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
            "err.number is: " & @TAB & Hex($oMyError.number, 8) & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
            "err.source is: " & @TAB & $oMyError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
 
    Local $err = $oMyError.number
    If $err = 0 Then $err = -1
 
    $g_eventerror = $err ; to check for after this function returns
EndFunc   ;==>MyErrFunc

[Merrik]

OK, we caught a COM error. Only on XP does this occur.

"

We intercepted a COM error !

err.description is: [Microsoft][ODBC SQL Server Driver][sql Server]Login failed dor user 'MYDOMAIN\USERNAME'.

err.windescription:

err.number is: 80020009

err.lastdllerror is: 0

err.scriptine is: -1

err.dource is: Microsoft OLE DB Provider for ODBC Drivers

err.helpfile is:

err.helpcontext is: 0

"

[water]

And then you get MessageBox "3: Error ocurred. @error = ..., $fAuthMode = ...", right?

In this case a SQL guru is needed because this is not my area of expertise, if any.

Edited September 16, 2011 by water