1

I have a test environment with a Linux Ubuntu 16.04 'server', running MS SQL Server 17, where the company database is hosted. My client machine is a Windows 7 machine.

  • I have managed to import the database into the Linux server, and am able to create and successfully test an ODBC connection from the Windows client.
  • SQL Server Management Studio on the Windows PC is able to create/drop tables, create users, objects and so forth on the SQL Server instance on Linux.

I am connecting as follows in VB 6.0

Dim rec1 As ADODB.Recordset 
Set rec1 = New ADODB.Recordset 
rec1.Open "Select * from tblSysReg", gstrDefaultDB, adOpenDynamic, adLockOptimistic
  • gstrDefaultDB is the string containing the name of the ODBC registered and tested connection.
  • The connection test credentials are: system administrator (sa) and password.
  • The connection is set to use SQL Server Authentication

This is where the error is generated as follows:

  • automation error code -2147217843

However, when I run the software program from the Windows client, it is unable to 'read' from a database table (tblSysReg) and generates an automation error.

The successfull ODBC connection parameters are as follows:

  • Type: TCP/IP
  • Server: 10.0.0.3
  • Port: 1433

I am logging in as 'sa' with my SQL Server password for SQL Server on Linux.

I am battling to understand how the connection test is successful, bit the program using the connection cannot read from any tables in the SQL Server database on Linux?

Thank you in advance.

squillman
  • 13,363
  • 3
  • 41
  • 60
Johan Van Der Hoven
  • 21
  • 3
  • 1
    Can you post the connection string that you're using in your application, as well as the specific error you receive? – squillman Jan 09 '20 at 14:04
  • The application was developed using Visual Basic 6. The code used to access the database object is as follows: Dim rec1 As ADODB.Recordset Set rec1 = New ADODB.Recordset rec1.Open "Select * from tblSysReg", gstrDefaultDB, adOpenDynamic, adLockOptimistic This is where the error is generated as follows: automation error code -2147217843 – Johan Van Der Hoven Jan 09 '20 at 14:55
  • gstrDefaultDB is the string containing the name of the ODBC registered and tested connection. The connection test credentials are: system administrator (sa) and password. The connection is set to use SQL Server Authentication, – Johan Van Der Hoven Jan 09 '20 at 14:59
  • Microsoft SQL Server ODBC Driver Version 06.01.7601 Running connectivity tests... Attempting connection Connection established Verifying option settings Disconnecting from server TESTS COMPLETED SUCCESSFULLY! (Connection from Windows client to Linux hosting SQL Server achieved.) – Johan Van Der Hoven Jan 09 '20 at 15:13
  • You can edit your question to add those bits in. Makes it easier to read. – squillman Jan 09 '20 at 15:41
  • 1
    Thanks will do ... – Johan Van Der Hoven Jan 09 '20 at 15:52
  • Cool. I just did some minor formatting of the code block. Oh, and welcome to Stackoverflow! :) – squillman Jan 09 '20 at 16:01

1 Answers1

0

Based on the code in your comments, you are trying to pass the connection string to rec1.Open() instead of a connection object. You need to use a Connection object to open the connection to the database and then use that object when opening the Recordset.

Something like this

Dim conn as ADODB.Connection
Dim rec1 As ADODB.Recordset

Set conn = New ADODB.Connection
Set rec1 = New ADODB.Recordset

conn.Open gstrDefaultDB
rec1.Open "Select * from tblSysReg", conn, adOpenDynamic, adLockOptimistic
... [do more stuff] ...
rec1.Close
conn.Close

You can also use the Execute method on the Connection to return a recordset.

Dim conn as ADODB.Connection
Dim rec1 As ADODB.Recordset

Set conn = New ADODB.Connection

conn.Open gstrDefaultDB
Set rec1 = conn.Execute("Select * from tblSysReg")
... [do more stuff] ...
rec1.Close
conn.Close

Have a look at this page for a lot more examples:
https://support.microsoft.com/en-us/help/168336/how-to-open-ado-connection-and-recordset-objects

squillman
  • 13,363
  • 3
  • 41
  • 60