Connect to SQL Server using AutoIT - Need Help - (Moved)

[vijayg]

Hello All,

I am a complete newbie to AutoIT. I am a SQL Server DBA . I want to connect to SQL Server using AutoIT. I am trying below but nothing works.

https://www.autoitscript.com/forum/topic/149123-sql-this-object-has-failed/

https://www.autoitscript.com/forum/topic/129185-sql-output-more-than-1-row/?tab=comments#comment-897052

Below is what I am trying and I am getting error. Kindly help.

Help needed on below issue and below points:

1. Do I need to import module , like I do for PowerShell (Import-Module SQLPS) ?
2. Best source for AutoIT Training. I started liking it.

My Code:

#Region ;===????===

   #include <Date.au3>
   #include <ButtonConstants.au3>
   #include <ComboConstants.au3>
   #include <EditConstants.au3>
   #include <GUIConstantsEx.au3>
   #include <StaticConstants.au3>
   #include <WindowsConstants.au3>
   #include <MsgBoxConstants.au3>
   #include <Crypt.au3>
   #include <File.au3>
   #include <String.au3>

#EndRegion ;===????===

;SQL Credentials
$name=@ComputerName
;MsgBox(0,"Computer Name is",$name)
$UserName='abc'
$ID='$name\$Username'
;Local $ServerAddress = "   INSERT YOUR SQL SERVER IP HERE    "
$ServerName='@Computername\AB'
$ServerPassword='abc123'
$DatabaseName="abcd"

 
; Specify an ini file to write to
$sIni = @ScriptDir &  "\Documents.ini"

; Connect to the database
$conn = ObjCreate( "ADODB.Connection" )
$DSN = "DRIVER={SQL Server};SERVER=$ServerName;DATABASE=$DatabaseName;UID=$ID;PWD=$ServerPassword;"
$conn.Open($DSN)

;Create record set and run query
$rs = ObjCreate("ADODB.RecordSet")
$rs.Open("select top 1 ID from table1", $conn)

If $RS.RecordCount Then
    While Not $RS.EOF
 Consolewrite("<file: >" & $rs.Fields("file").value & @CRLF)
        $RS.MoveNext
    WEnd
EndIf
    
$RS.Close
$conn.?????close

Error:

(46) : ==> The requested action with this object has failed.:

$conn.Open($DSN)

$conn^ ERROR

->12:28:44 AutoIt3.exe ended.rc:1

+>12:28:44 AutoIt3Wrapper Finished.

>Exit code: 1    Time: 20.37

 

 

 

 

 

[jchd]

$DSN = "DRIVER={SQL Server};SERVER=$ServerName;DATABASE=$DatabaseName;UID=$ID;PWD=$ServerPassword;"
; should be:
$DSN = "DRIVER={SQL Server};SERVER=" & $ServerName & ";DATABASE=" & $DatabaseName & ";UID=" & $ID & ";PWD=" & $ServerPassword & ";"

There is no interpolation of variables in a string, unless you use

Opt("ExpandVarStrings)", 1

Also, next time please use the <> icon to paste code.

Edited July 29, 2019 by jchd

[vijayg]

22 minutes ago, jchd said:

$DSN = "DRIVER={SQL Server};SERVER=$ServerName;DATABASE=$DatabaseName;UID=$ID;PWD=$ServerPassword;"
; should be:
$DSN = "DRIVER={SQL Server};SERVER=" & $ServerName & ";DATABASE=" & $DatabaseName & ";UID=" & $ID & ";PWD=" & $ServerPassword & ";"

There is no interpolation of variables in a string, unless you use

Opt("ExpandVarStrings)", 1

Also, next time please use the <> icon to paste code.

Thanks jchd for your response. That was a good learning for me.

I am getting below error , can you please help

 
$conn=ObjCreate( "ADODB.Connection" )

1.au3"(46,17) : error: syntax error (illegal character)
 

[vijayg]

2 minutes ago, vijayg said:

I am able to fix the above ask.

$conn=ObjCreate( "ADODB.Connection" )

1.au3"(46,17) : error: syntax error (illegal character) . This is fixed.

Please help on below.

Currenntly I am facing issue with below:

;Create record set and run query
$rs = ObjCreate("ADODB.RecordSet")
$rs.Open("select top 1 ID from table1", $conn)

-- I am getting error here : syntax error (illegal character)

If $RS.RecordCount Then
    While Not $RS.EOF
 Consolewrite("<file: >" & $rs.Fields("file").value & @CRLF)
        $RS.MoveNext
    WEnd
EndIf
    
$RS.Close
$conn.?????close

 

[Jos]

Moved to the appropriate forum, as the Developer General Discussion forum very clearly states:

Quote

General development and scripting discussions. If it's super geeky and you don't know where to put it - it's probably here.

---

Do not create AutoIt-related topics here, use the AutoIt General Help and Support or AutoIt Technical Discussion forums.

Moderation Team

[Jos]

Post the whole script that generates the error os else we have no idea what the issue is.

Jos 

[mLipok]

Try to use my ADO.au3 UDF

[vijayg]

This is my code:

<

$constrim="DRIVER={SQL Server};SERVER=@Computername\AB;DATABASE=ABC;uid=@ComputerName\User1;pwd=abc!@123;"
$adCN = ObjCreate ("ADODB.Connection") ; <== Create SQL connection
$adCN.Open($constrim) ; <== Connect with required credentials
MsgBox(0,"",$constrim )

if @error Then
    MsgBox(0, "ERROR", "Failed to connect to the database")
    Exit
Else
    MsgBox(0, "Success!", "Connection to database successful!")
EndIf

$sQuery = "select * from table1"

$result = $adCN.Execute($sQuery)
MsgBox(0, "", $result.Fields( "s_username" ).Value)
$adCN.Close ; ==> Close the database

>

 

Failure Message:

>"C:\Program Files (x86)\AutoIt3\SciTE\..\AutoIt3.exe" "C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.au3" /test /run /prod /ErrorStdOut /in "D:\Users\AutiIT\TestSQLLL.au3" /UserParams   
+>14:59:20 Starting AutoIt3Wrapper v.18.708.1148.0 SciTE v.4.1.0.0   Keyboard:00000409  OS:WIN_10/  CPU:X64 OS:X64  Environment(Language:0411)  CodePage:0  utf8.auto.check:4
+>         SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE   UserDir => C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper
- *** Compile in Test mode skipping Tidy; Au3Stripper; Resource updating and Versioning to speed up the process. ***
>Running AU3Check (3.3.14.5)  from:C:\Program Files (x86)\AutoIt3  input:D:\Users\Desktop\AutiIT\TestSQLLL.au3
+>14:59:20 AU3Check ended.rc:0
>Running:(3.3.14.5):C:\Program Files (x86)\AutoIt3\autoit3_x64.exe "D:\Users\Desktop\AutiIT\TestSQLLL.au3"   
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
"D:\Users\Desktop\AutiIT\TestSQLLL.au3" (3) : ==> The requested action with this object has failed.:
$adCN.Open($constrim)
$adCN^ ERROR
->14:59:37 AutoIt3.exe ended.rc:1
+>14:59:37 AutoIt3Wrapper Finished.
>Exit code: 1    Time: 19.98
 

[jchd]

You're making the same mistake again: unless the releving Opt() is issued, there is no interpolation of variables ($something) nor macros (@somethingelse). Else concatenate strings parts with &

Also (again), use the <> icon for posting code.

Finally, you better try to run your program from SciTE with F5 instead of compiling it each time while coding/debugging.

[Melba23]

Moved to the appropriate forum.

Moderation Team

[JLogan3o13]

@vijayg please stick to one thread. You are receiving suggestions; take them.

[Skysnake]

On 7/29/2019 at 9:49 PM, mLipok said:

Try to use my ADO.au3 UDF

Use this.