Jump to content

SQL Server Connectivity


Go to solution Solved by Zedna,

Recommended Posts

Hi,

I am trying to connect to a remote SQL Server. But it return the error: [Microsoft][ODBC SQL Server Driver] Cannot open database "data base" requested by the login. The login failed. I could connect to the remote server and view the database and tables through MS SQL Server management studio using the server name and credentials. 

 

Please help.

$objErr = ObjEvent("AutoIt.Error","MyErrFunc")
$sServer = 'server_name'
$sDatabase = 'databse_name'
$sUID = 'user'
$sPWD = 'pwd'
$oConn = ObjCreate ("ADODB.Connection")
$DSN = 'DRIVER={SQL Server};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUID &';PWD=' & $sPWD & ';'
$oConn.ConnectionTimeout = 10 ; 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

#cs
With $oConn
    .Provider = "SQLOLEDB"
    .Properties("Initial Catalog") = $sDatabase
    .Properties("Data Source").Value = $sServer
    .Properties("Integrated Security").Value = "SSPI"
    .Properties("Persist Security Info").Value = "True"
    .Properties("User ID").Value = $sUID
    .Properties("Password").Value = $sPWD
    .Open
 EndWith
 If @error then Exit
#ce

 

Edited by PoojaKrishna
Link to post
Share on other sites

Removing the database name from the connection string made it work. 

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

Please specify the database name in the sql queries that you execute.

Local $sQuery = "INSERT INTO Database.dbo.[TABLENAME]([ColName1],[ColName2],[ColName3]) VALUES('TEST STRING',2,1)"

Thank you.

Link to post
Share on other sites

Here a good reference to read (i think)

https://accessexperts.com/blog/2011/03/24/sql-server-connections-strings-for-microsoft-access/

I use this to connect to SQL server in local network

Opt("MustDeclareVars", 1)
Opt("TrayIconDebug", 1)
OnAutoItExitRegister("OnAutoItExit")

Global $cn, $rst, $cmd, $sSQL, $SubSQL

;Help: COM Error Handling
;_ErrADODB From spudw2k
;https://www.autoitscript.com/forum/topic/105875-adodb-example/
Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB")

Global Const $iCursorType = 3 ;0 adOpenForwardOnly, 3 adOpenStatic
Global Const $iLockType = 3 ;1 adLockReadOnly, 3 adLockOptimistic
Global Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable
$cn = ObjCreate("ADODB.Connection") ; Create a connection object
$rst = ObjCreate("ADODB.Recordset") ; Create a recordset object

Global $sADOConnectionString = "Provider=SQLOLEDB;Data Source=99.9.9.99;Initial Catalog=MyDatabaseName;User Id=sa;Password=MyPassword;"

;https://www.w3schools.com/asp/prop_rs_cursorlocation.asp
;A Recordset object inherits this setting from the associated Connection object.
;This property is read-only on an open Recordset object, and read/write on a Connection object or on a closed Recordset object.
$cn.CursorLocation = 2 ;2 adUseServer, 3 adUseClient
$cn.CommandTimeout = 30

;https://stackoverflow.com/questions/31941487/open-adodb-connection-to-excel-file-in-read-only-mode
;try Mode = adModeRead instead
;By the way, do not put adModeRead in the connections string, but just before openning your connection, add this line: rsConn.Mode = adModeRead
;I tried your suggestion, however since in VBA we do not have direct access to the ADODB built-in constants, I set rsCon.Mode = 1
;as defined in the file adovbs.inc located in the folder "C:\Program Files\Common Files\System\ado"
;and although I watched the rsCon.Mode value being set to adModeRead while debugging, I still have the same problem and the application tries to access the file in Write/Edit mode.
;https://www.w3schools.com/asp/prop_rec_mode.asp
;$cn.Mode = 1 ;Read-only

$cn.Open($sADOConnectionString) ; Open the connection
;MsgBox(0,"",$cn.ConnectionString)

;...

$cn.Close ;Close the connection
$cn = 0 ;Release the connection object

Func _ErrADODB()
   Msgbox(0,"ADODB COM Error","We intercepted a COM Error !"      & @CRLF  & @CRLF & _
       "err.description is: "    & @TAB & $errADODB.description    & @CRLF & _
       "err.windescription:"     & @TAB & $errADODB.windescription & @CRLF & _
       "err.number is: "         & @TAB & hex($errADODB.number,8)  & @CRLF & _
       "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & @CRLF & _
       "err.scriptline is: "     & @TAB & $errADODB.scriptline     & @CRLF & _
       "err.source is: "         & @TAB & $errADODB.source         & @CRLF & _
       "err.helpfile is: "       & @TAB & $errADODB.helpfile       & @CRLF & _
       "err.helpcontext is: "    & @TAB & $errADODB.helpcontext, 5)

   Local $err = $errADODB.number
   If $err = 0 Then $err = -1

   ;$rst = 0
   ;$cmd = 0
   $cn.Close
   $cn = 0

   Exit
EndFunc

Func OnAutoItExit()
   ;$rst = 0 ; Release the recordset object
   $cmd = 0
   If IsObj($cn) Then
      If $cn.State > 0 Then $cn.Close ;adStateOpen Close the connection
      $cn = 0 ; Release the connection object
   EndIf
EndFunc

 

Edited by robertocm
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.

  • Similar Content

    • By rynow
      romaSQL
      This autoIt UDF is built on the concept of Laravel Query & doctrine.
      RomaSQL provides a new, comfortable and easy to use way for SQL-queries in autoIt.
      Most of the common SQL-queries are supported already and more are coming soon.
      All of your support is much appreciated.
      Connections
      For the connection the object ADODB is used. Therefore the connection string is based on ODBC.
      You can also use OLEDB connection strings or other database connections.
      In order for this to work your add-ons have to be installed in the function: __4ern_SQL_Connection.
      I’d be very glad if you shared your modifications with me.
      Currently supported connections
      -       MySQL (odbc)
      -       Microsoft SQL Server (odbc)
      -       SQLite (odbc)
      -       Microsoft Access (odbc)
      Command reference
      $SQL_connect; establishing connection $SQL_returnType; return a Array or Dictionary ('oDict') Object (Default = Array) $SQL_setDefaultTable; Default Tablename $SQL_setDefaultKey; Default Colmn Key (Default = id) $SQL_debug; if True, show SQL Statment in Console $SQL_get $SQL_update $SQL_delete $SQL_insertInto $SQL_take $SQL_limit $SQL_table $SQL_select $SQL_distinct $SQL_where $SQL_orWhere $SQL_whereBetween $SQL_whereNotBetween $SQL_whereIn $SQL_whereNotIn $SQL_whereNull $SQL_whereNotNull $SQL_having $SQL_orHaving $SQL_havingBetween $SQL_havingNotBetween $SQL_havingIn $SQL_havingNotIn $SQL_havingNull $SQL_havingNotNull $SQL_groupBy $SQL_orderBy  
      Examples
      establishing connection
      ;-----/ ; SQLite Connection ;-----/ $SQL_setDatabase('sqlite') $SQL_connect('C:\project.db') ;-----/ ; Access Connection ; Database, User, Password ;-----/ $SQL_setDatabase('access') $SQL_connect('C:\project.mdb') ;or as Admin $SQL_connect('C:\project.mdb', '4ern', 'root') ;-----/ ; SQLServer Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('sqlserver') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'SQL Server') ;-----/ ; MySQL Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('mysql') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'MySQL ODBC 5.2 UNICODE Driver')  
      simple SQL query
      $SQL_table('albums') $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif  
      Select
      $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song') ;or pass to an Array Local $aSelect = ['id', 'Name', 'Artist', 'Song'] $SQL_select($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif where
      $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song', 'Votes') $SQL_where('Artist', 'adele') $SQL_where('Votes', '>=' ,'9') $SQL_orWhere('Artist', '=' ,'Rag'n'Bone Man') ;or pass to an 2dArray Local $aSelect = [['Artist','adele'],['Votes', '>=' ,'9']] $SQL_where($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif  
      If you need more examples, then tell me exactly what you need.
      I hope you like my UDF and find some use for it.
      ---
      ->DONWLOAD romaSQL
       
       
×
×
  • Create New...