Jump to content

Recommended Posts

Hi,
For a few days I'm trying connect to MSSQL Server. My test environment is easy: my computer on W10 with Autoit and second machine Windows 2016 Server with MS SQL 2016 Standard Edition.
When I use PowerShell, there is no problem. I can connect to this sql and do query.
When I use Autoit I get connection error.
 

1. I used ADO.au3 UDF  from mLipok and I get:

image.png.69d1f58093780b6ef854fc9a713d309f.png
  

2. I use _sql.au3 from Chris Lambert and I get

 

Quote

[Microsoft][ODBC Driver Manager] data source name not found and no default driver specified
err.windescription:    an exception has occurre

 

What I do wrong? 

What is the easiest way to connect to SQL?

image.png

Share this post


Link to post
Share on other sites
Global $sDriver      = 'SQL Server'
Global $sDatabase    = 'MyMSDB' ; change this string to YourDatabaseName
Global $sServer      = 'MySQLSRV' ; change this string to YourServerLocation
Global $sUID         = 'localsql' ; change this string to YourServerLocation
Global $sPWD         = 'mypass' ; change this string to YourServerLocation

Global $sConnectionString = "DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUID &';PWD=' & $sPWD & ';"

Generally I use domain user but I created  local sql user additional. The same result: Connection error 5

Share this post


Link to post
Share on other sites

 

Beware of the quotation marks

$sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUID &';PWD=' & $sPWD & ';'

 


Rule #1: Always do a backup         Rule #2: Always do a backup (backup of rule #1)

Share this post


Link to post
Share on other sites

Original wrong:

Global $sConnectionString = "DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUID &';PWD=' & $sPWD & ';"

Fixed:

Global $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUID &';PWD=' & $sPWD & ';'

 

Share this post


Link to post
Share on other sites

I think you have to show your code, not only the connection string.
This works for me to open a connection:
 

Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'
Local $oConnection = _ADO_Connection_Create()
_ADO_Connection_OpenConString($oConnection, $sConnectionString)

With $sDriver ="SQL Server"


Rule #1: Always do a backup         Rule #2: Always do a backup (backup of rule #1)

Share this post


Link to post
Share on other sites

The simplest testing code:

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

$sServer = 'server1'
$sDatabase = 'master'
$sUID = 'admin'
$sPWD = 'pwd'

$DSN = 'DRIVER={SQL Server};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUID &';PWD=' & $sPWD & ';'
$oConn = ObjCreate ("ADODB.Connection")
$oConn.ConnectionTimeout = 3 ; default is 15 s (must be supported by data provider DSN)
$oConn.Open($DSN)
If @error Then Exit
$oConn.Close
MsgBox(64, 'OK', 'Finished')

Func MyErrFunc()
    MsgBox(48, 'COM Error', $objErr.description)
    SetError(1)
EndFunc

 

Share this post


Link to post
Share on other sites

@Zedna it works. I get OK Finished. When I set wrong password I get error. Later I'll test with domain user.
Next question is how it use to sql query i.e.:

Quote

SELECT * FROM Userlist

 

Share this post


Link to post
Share on other sites

Here is more general example showing working with ADO directly without any includes:

; https://www.w3schools.com/asp/ado_intro.asp

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

$sServer = 'server1'
$sDatabase = 'master'
$sUID = 'admin'
$sPWD = 'pwd'

$DSN = 'DRIVER={SQL Server};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUID &';PWD=' & $sPWD & ';'
$oConn = ObjCreate ("ADODB.Connection")
$oConn.ConnectionTimeout = 3 ; default is 15 s (must be supported by data provider DSN)
$oConn.Open($DSN)
If @error Then Exit

; simple select returns 1 value (1 column and 1 row: count() max() TOP 1)
$value = $oConn.Execute('select max(col1) from admin.table').Fields(0).Value
ConsoleWrite($value & @CRLF)

; simple SQL command INSERT/UPDATE/DELETE without return value
$oConn.Execute('update admin.table set col1 = value where key = id')

; general SELECT returns more columns/rows
$oRS = ObjCreate ("ADODB.Recordset")
$oRS.CursorType = 2
$oRS.LockType = 3

$oRS.Open ('select * from admin.table', $oConn)
If @error Then Exit

If $oRS.RecordCount Then ; -1
    While Not $oRS.EOF
        $value = $oRS.Fields(0).Value & ' | ' & $oRS.Fields(1).Value ; read only first and second column
        ConsoleWrite($value & @CRLF)
        $oRS.MoveNext
    WEnd
EndIf

; execute stored procedure, doesn't support input params and result set
Local $rowcount
$oConn.Execute('admin.sp_my_procedure', $rowcount, 132) ; adCmdStoredProc=4 + adExecuteNoRecords=0x80 (128)

$oRS.Close
$oConn.Close
MsgBox(64, 'OK', 'Finished')

Func MyErrFunc()
    MsgBox(48, 'COM Error', $objErr.description)
    SetError(1)
EndFunc

 

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...