Merrik Posted September 15, 2011 Share Posted September 15, 2011 (edited) 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 Link to comment Share on other sites More sharing options...
water Posted September 15, 2011 Share Posted September 15, 2011 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. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted September 15, 2011 Share Posted September 15, 2011 (edited) This lineLocal $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 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Merrik Posted September 15, 2011 Author Share Posted September 15, 2011 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. Link to comment Share on other sites More sharing options...
Merrik Posted September 15, 2011 Author Share Posted September 15, 2011 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 Link to comment Share on other sites More sharing options...
water Posted September 15, 2011 Share Posted September 15, 2011 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? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Merrik Posted September 16, 2011 Author Share Posted September 16, 2011 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> Link to comment Share on other sites More sharing options...
water Posted September 16, 2011 Share Posted September 16, 2011 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 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Merrik Posted September 16, 2011 Author Share Posted September 16, 2011 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 " Link to comment Share on other sites More sharing options...
water Posted September 16, 2011 Share Posted September 16, 2011 (edited) 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 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now