CODEWITHSUNDEEP
vbawinapiodbc
sigil
sigil

Reputation: 9546

How to call SqlConfigDataSource() with a [server]\[instance] attribute?

Following the code in this post, I'm trying to use VBA to call the WinAPI function SQlConfigDataSource() for creating a new ODBC source. Here's my code:

Const ODBC_ADD_SYS_DSN = 4       'Add a system data source

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal _
hwndParent As Long, ByVal fRequest As Long, ByVal _
lpszDriver As String, ByVal lpszAttributes As String) As Long

Private Declare Function SQLInstallerError Lib "ODBCCP32.DLL" (ByVal iError As Integer, _
pfErrorCode As Long, ByVal lpszErrorMessage As String, ByVal cbErrorMsgMax As Integer, _
pcbErrorMsg As Integer) As Integer

Private Const SQL_SUCCESS = 0
Private Const SQL_SUCCESS_WITH_INFO = 1
Private Const SQL_NO_DATA = 100
Private Const SQL_ERROR = (-1)
Private Const SQL_MAX_MESSAGE_LENGTH = 512


Function Build_SystemDSN(dataSourceName As String, server_instance As String, database As String)

'SQLConfigDataSource parameters
Dim Driver As String
Dim Ret As Long
Dim Attributes As String

'SQLInstallerError parameters
Dim iret As Integer
Dim lpszErrMess As String
Dim iErr As Integer 'may be 1 - 8
Dim pfErrCode As Long

lpszErrMess = Space(SQL_MAX_MESSAGE_LENGTH)


Driver = "SQL Server"

'attributes are the connection information
Attributes = "server=" & server_instance & Chr(0)
Attributes = Attributes & "DSN=" & dataSourceName & Chr(0)
Attributes = Attributes & "Database=" & database & Chr(0)
'use this line if you want to use the users name and password
Attributes = Attributes & "Trusted_Connection=Yes" & Chr(0)
Ret = SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, Driver, Attributes)

'ret is equal to 1 on success and 0 if there is an error
If Ret <> 1 Then
  iErr=1
  iret = SQLInstallerError(iErr, pfErrCode, lpszErrMess, SQL_MAX_MESSAGE_LENGTH, 0)
  MsgBox "DSN Creation Failed: " & pfErrCode
End If


End Function

I'm calling the function as:

Sub testBuildDSN()

Dim oc As New ODBCCreator   
oc.Build_SystemDSN "XYSource", "SQLRM101\sqlrm101a", "XYoppid_abc_prod"

End Sub

But SQLConfigDataSource() is returning 0. I think it's because there is a backslash in my server/instance pair; per MSDN, this is not a permitted character in an attribute set. How can I set the server and instance using this function?

EDIT: I've added code to output the SQLInstallerError() data. When I look at error 1 (which I guess is the one from the call I just made?) then I get a pfErrorCode value of "19". I don't know what this means.

Upvotes: 0

Views: 2826

Answers (1)

arx
arx

Reputation: 16904

If SQLConfigDataSource is failing you need to call SQLInstallerError to find out why.

SQLInstallerError returned an error code of 19. ODBC error codes are in odbcinst.h and 19 is ODBC_ERROR_WRITING_SYSINFO_FAILED.

This is most likely because you don't have the necessary permissions to create a system-wide Data Source Name.

Try running the code as an Administrator.

Upvotes: 1

Related Questions