0

I am using ActiveX Data Objects 6.1 library in Excel VBA to access a SQL Server 2012 database. I can connect and run queries fine, but once connected there appears to be no way to determine which server the connection object is connected to, as the connectionString property does not return the same string that was used to open the connection:

Public Sub connectDB()
        Dim conn As New Connection
        Dim strServer As String, strDatabase As String, strUser As String, strPassword As String
        strServer = "****": strDatabase = "****": strUser = "****": strPassword = "****"

        conn.ConnectionString = "Driver={SQL Server};Server=" & strServer & ";Database=" & strDatabase & ";UID=" & strUser & ";PWD=" & strPassword
        Debug.Print conn.ConnectionString
        conn.Open
        Debug.Print conn.ConnectionString
End Sub

Outputs:

Driver={SQL Server};Server=****;Database=****;UID=****;PWD=****

Provider=MSDASQL.1;

In other words, once the connection is open the connectionString property is reset to something unhelpful. Connection.DefaultDatabase returns the database, but there appears to be no way to determine the server from the connection object.

If I have code which is passed a connection object at runtime it would be nice to be able to see which server it's connected to without having to execute sys.dm_exec_connections (which seems like a waste of resources, and you need fairly high permissions to run it). Is there a way?

aucuparia
  • 2,021
  • 20
  • 27
  • 1. Why do you need to read the server name after you connected? 2. Why don't you use global variables to save the server, so you can read it everytime you need it – EngJon Aug 11 '15 at 11:45
  • Yes, there are ways round it. But if my code gets a `connection` object from other code that I don't otherwise need to modify, it would be neater to just get the connection string or server from the connection. – aucuparia Aug 11 '15 at 12:30
  • There is a property that disables the stripping of the data from your connection string. However, if you plan to read from other code, the property has to be set to `true` in that code, too. – EngJon Aug 11 '15 at 12:54
  • Can you post details of the property in an answer? I couldn't find it (though in the process of looking I *did* find the Server Name property so thanks for that!) – aucuparia Aug 11 '15 at 14:34

1 Answers1

2

The server name (along with a lot of other interesting information including DBMS version, supported features, special characters, etc) is one of the Connection object's dynamic properties, accessed through the Properties collection:

connection.Properties("Server Name").Value

returns the server name. For the Microsoft OLE DB Provider for ODBC Drivers (and possibly others), the full connection string is also in the "Extended Properties" property. You can list the properties by doing:

Dim prop As Property
For Each prop In conn.Properties
    Debug.Print prop.Name, prop.Value
Next prop

Exactly which properties you get for any given connection depends on the provider you are using - see MSDN for lists.

aucuparia
  • 2,021
  • 20
  • 27
  • Is it always possible to read the server name through this property? The property I meant in the comment above was "Persist Security Info". It can be found inside the `conn.Properties` as well. Seems like it only is meant for the connectionString, so the property "Server Name" won't be affected. – EngJon Aug 11 '15 at 14:39