SQL Server Connectivity

[PoojaKrishna]

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 August 9, 2021 by PoojaKrishna

[PoojaKrishna]

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.

[Zedna]

Use:

Database=master;

[robertocm]

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

expandcollapse popup

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 August 11, 2021 by robertocm

[PoojaKrishna]

On 8/10/2021 at 2:28 PM, Zedna said:

Use:

Database=master;

Thank you! It worked!

[PoojaKrishna]

@robertocm,

Thank you! Your solution also fixed the problem.