ADODB Connection.ConnectionString属性不返回服务器名称 [英] ADODB Connection.ConnectionString property doesn't return server name

问题描述

我正在使用Excel VBA中的ActiveX数据对象6.1库来访问SQL Server 2012数据库。我可以很好地连接并运行查询，但是一旦连接成功，就无法确定 connection 对象所连接的服务器，因为 connectionString 属性不会返回用于打开连接的字符串：

  Public Sub connectDB（ ）
 Dim conn作为新连接
 Dim strServer作为字符串，strDatabase作为字符串，strUser作为字符串，strPassword作为字符串
 strServer = ****：strDatabase = **** ：strUser = ****：strPassword = **** 
 
 conn.ConnectionString = Driver = {SQL Server}; Server =&服务器和服务器; Database =& str数据库和; UID =& strUser& ; PWD =& strPassword 
 Debug.Print conn.ConnectionString 
 conn.Open 
 Debug.Print conn.ConnectionString 
 End Sub 
  

输出：

  Driver = {SQL Server}; Server = ** **;数据库= ****; UID = ****; PWD = **** 
 
 Provider = MSDASQL.1; 
  

换句话说，一旦打开连接， connectionString 属性重置为无用的东西。 Connection.DefaultDatabase 返回数据库，但是似乎无法从 connection 对象确定服务器。 / p>

如果我有在运行时传递给连接对象的代码，那么能够不必执行就可以查看它所连接的服务器，这将是很不错的sys.dm_exec_connections （这似乎是对资源的浪费，并且您需要相当高的权限才能运行它）。有办法吗？

解决方案

服务器名称（以及许多其他有趣的信息，包括DBMS版本，支持的功能，特殊的字符等）是 Connection 对象的动态属性，可通过属性集合进行访问：

  connection.Properties（服务器名称）。值
  

返回服务器名称。对于ODBC驱动程序的Microsoft OLE DB提供程序（可能还有其他），完整的连接字符串也位于扩展属性属性中。您可以通过执行以下操作列出属性：

 将prop作为属性
将每个prop包含在conn.Properties $ b $中b Debug.Print属性名称，属性值
下一个属性
  

您确实拥有哪些属性获取任何给定的连接取决于您使用的提供程序-查看MSDN中的列表。

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?

解决方案

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.

这篇关于ADODB Connection.ConnectionString属性不返回服务器名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！