Merrik Posted September 28, 2011 Share Posted September 28, 2011 Has anyone ever tried something like the example below. I'm having problems with it. expandcollapse popup$sServer="DBServer" $db="MYDB" $sUsername = "" $sPassword = "" $SQL_ERROR = "SQL Error" $SQL_OK = "SQL OK" Func _SQL_Connect($ADODBHandle, $server, $db, $username, $password, $SQLAuth = True) $SQLErr = "" If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection If Not IsObj($ADODBHandle) Then $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()" Return SetError($SQL_ERROR, 0, $SQL_ERROR) EndIf If $SQLAuth = True then $ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";") ;<==Connect with required credentials Else $ADODBHandle.Properties("Integrated Security").Value = "SSPI" $ADODBHandle.Properties("User ID") = $username $ADODBHandle.Properties("Password") = $password $ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db ) EndIf If IsObj( $ADODBHandle) Then MsgBox(0, "AutoIT-SQL Result", "We have a connection") $adoSQL = "INSERT INTO dbo.Table VALUES ('" & $comp & "','" & $sTime & "')" $ADODBHandle.Execute($adoSQL) If @error Then Exit MsgBox(16, "SQL Connect Script", "4: Error occurred. @error = " & @error) If Not @error Then Return SetError($SQL_OK, 0, $SQL_OK) Else $SQLErr = "Connection Error" Return SetError($SQL_ERROR, 0, $SQL_ERROR) EndIf EndFunc ;==>_SQL_Connect Link to comment Share on other sites More sharing options...
rcmaehl Posted September 28, 2011 Share Posted September 28, 2011 What problems are occurring? My UDFs are generally for me. If they aren't updated for a while, it means I'm not using them myself. As soon as I start using them again, they'll get updated.My Projects WhyNotWin11Cisco Finesse, Github, IRC UDF, WindowEx UDF Link to comment Share on other sites More sharing options...
Merrik Posted September 28, 2011 Author Share Posted September 28, 2011 I keep getting errors like undeclared variable "$SQL_LastConnection". I really know little about SQL connections and was hoping someone with a good SQL background could help out. I really need a SQL connection string that uses "integrated authentication". Link to comment Share on other sites More sharing options...
Merrik Posted September 29, 2011 Author Share Posted September 29, 2011 I found another example using integrated authentication and I hope someone can help me get one working. I need to an insert into the DB. Can someone help explain $oProviderGroupID, ;Data Source=datasource, Provider=SQLOLEDB. I'm guessing I need to change "ADODB.Recordset" to "ADODB.Command" Local $oRS Local $oConn Local $oProviderGroupID $oConn = ObjCreate ("ADODB.Connection") $oRS = ObjCreate ("ADODB.Recordset") $oConn.Open ("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=user;Data Source=datasource") $oProviderGroupID = ObjName ("ProviderGroupID") $oRS.Open("SELECT DMisProviderGroup.ProviderGroupID FROM DMisProviderGroup WHERE DMisProviderGroup.Active= 'Y' AND LEFT(DMisProviderGroup.ProviderGroupID,3)IN ('LC.','MD.','UA.') AND DMisProviderGroup.ProviderGroupID NOT IN ('LC.PSYCH','LC.PSYKLR','LC.PSYMAD','LC.PSYMID','LC.PSYNEE','LC.PSYOSH')AND SUBSTRING(DMisProviderGroup.ProviderGroupID,4,3) <> 'LAB' ", $oConn, 1, 3) WinWaitActive ("MIS") For $iIndex = 1 To $oRS.RecordCount SEND ($oProviderGroupID) SEND ("{ENTER}") $oRS.MoveNext Next $oConn.Close $oConn = 0 Link to comment Share on other sites More sharing options...
hannes08 Posted September 29, 2011 Share Posted September 29, 2011 Hello Merrik, if you're using the _sql UDF, you'll need to start something before you try to connect: #include <_sql.au3> Global $oADODB = _SQL_Startup() If $oADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg()) If _sql_Connect(-1, "server", "db", "", "") = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg()) _SQL_Close() Exit EndIf Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler] Link to comment Share on other sites More sharing options...
Merrik Posted September 29, 2011 Author Share Posted September 29, 2011 Hi hannes08, Where do I find _SQL UDF? I need to connect using integrated authentication. Link to comment Share on other sites More sharing options...
hannes08 Posted September 29, 2011 Share Posted September 29, 2011 (edited) Hi Merrik, _sql.udf my ChrisL: Edited September 29, 2011 by hannes08 Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler] Link to comment Share on other sites More sharing options...
Merrik Posted September 29, 2011 Author Share Posted September 29, 2011 OK I am using the _sql.au3 UDF and am still having problems. When I connect to a SQL Server in my domain using NT Authority\System, no problem, but I am having problems connecting to a SQL Server in another domain using NT Authority\System. I can run sqlwb.exe as NT Authority\System and connect to the DB in the other domain and update it, but just not with my Autoit script. I get this: "C:\Documents and Settings\LocalService\My Documents\_sql.au3 (220) : ==> The requested action with this object has failed.: $ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";") $ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";")^ ERROR ->15:21:52 AutoIT3.exe ended.rc:1 >Exit code: 1 Time: 2.350 " Then I decided to force Integrated Authentication and I comment out "; $ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";") ;<==Connect with required credentials ; Else" Then I get" >Running:(3.3.6.1):C:\Program Files\AutoIt3\autoit3.exe "C:\Documents and Settings\LocalService\My Documents\test_Today.au3" C:\Documents and Settings\LocalService\My Documents\_sql.au3 (222) : ==> The requested action with this object has failed.: $ADODBHandle.Properties("Integrated Security").Value = "SSPI" $ADODBHandle.Properties("Integrated Security")^ ERROR #include <_sql.au3> Global $oADODB = _SQL_Startup() $comp = "MyPC" If $oADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg()) ;If _sql_Connect(-1, "DBSERVER\ins1", "PCDB", "", "") = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg()) Else MsgBox(0,"","You connected!") If _SQL_Execute(-1,"INSERT INTO dbo.Computers (Computer_Name) VALUES ('"& $comp & "');") Then MsgBox(0,"","Inserted") EndIf _SQL_Close() Exit EndIf Any help is appreciated. Link to comment Share on other sites More sharing options...
Merrik Posted September 29, 2011 Author Share Posted September 29, 2011 Solution: All that was missing was the fully qualified name of my DB Server If _sql_Connect(-1, "DBSERVER.mydomain.us\ins1", "PCDB", "", "") = $SQL_ERROR Then 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