测试SQL Server连接 [英] Test SQL Server Connection

问题描述

我构建了一个基于Excel的工具，该工具使用ODBC连接和查询表从SQL Server中提取数据(2014年).此工具必须与Mac Office 2016兼容(因此ODBC和querytables).

我正在努力测试用户是否可以连接到SQL Server.使用ODBC连接时，如果连接失败，则会打开一个SQL Server错误登录对话框，然后打开一个登录对话框，然后我的错误处理才能生效.

我发现使用的ADODB (与Mac不兼容)的每个解决方案都是此答案讨论了TCP连接，但是我找不到与Mac兼容的任何有关TCP和VBA的信息. >

有人知道使用VBA测试与Office for Mac 2016兼容的SQL Server连接的方法吗?

有关如何使用ODBC和querytables连接到SQL Server的示例.

 Sub main()
    On Error GoTo err1
    Dim connstring As String
    Dim sqlstring As String
    Dim dArr As Variant
    Dim qt As QueryTable

    connstring = "ODBC;DRIVER={SQL Server};SERVER=SERVERNAME;DATABASE=master;Trusted_Connection=yes"
    sqlstring = "SELECT 1"

    Set qt = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=ThisWorkbook.Sheets(1).Range("A1"), Sql:=sqlstring)

    With qt
      .BackgroundQuery = False
      .RowNumbers = False
      .Refresh
    End With

    dArr = qt.ResultRange.Value
    qt.Delete

    ThisWorkbook.Sheets(1).Cells.Clear
    Exit Sub
err1:
Stop
End Sub
 

解决方案

Windows上存在相同的问题.这是两个可能的答案.

1)不确定它是否与MAC兼容:

connstring = fnGetConnString( {connection string inputs})
if connstring = '' then 
    msgbox( "Not a valid connection or sever unavailable: {connection string inputs}"
    exit sub ''#halt all
end if

除其他事项外，该功能需要接受{TestID}作为输入，以确保其具有正确的文件.

有关sqlcmd的帮助，请参见: https://docs.microsoft.com/zh-cn/sql/tools/sqlcmd-utility?view=sql-server-ver15

有关来自VBA的OS命令，请参见: 使用excel VBA在命令提示符下执行命令

I built an Excel-based tool that uses ODBC connections and querytables to pull data from a SQL Server (2014). This tool has to be compatible with Mac Office 2016 (hence the ODBC and querytables).

I'm struggling with testing whether the user can connect to the SQL Server. With the ODBC connection, if it fails to connect, a SQL Server error login dialog opens and then a login dialog opens before my error handling can take effect.

Every solution to this issue that I found uses either ADODB (which isn't compatible with Mac) or is a solution to Access VBA. This answer talks about TCP connections, but I was unable to find anything about TCP and VBA that looked compatible with Mac.

Does anyone know of a way to test a SQL Server connection that would be compatible with Office for Mac 2016 using VBA?

An example of how I'm using ODBC and querytables to connect to SQL Server.

Sub main()
    On Error GoTo err1
    Dim connstring As String
    Dim sqlstring As String
    Dim dArr As Variant
    Dim qt As QueryTable

    connstring = "ODBC;DRIVER={SQL Server};SERVER=SERVERNAME;DATABASE=master;Trusted_Connection=yes"
    sqlstring = "SELECT 1"

    Set qt = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=ThisWorkbook.Sheets(1).Range("A1"), Sql:=sqlstring)

    With qt
      .BackgroundQuery = False
      .RowNumbers = False
      .Refresh
    End With

    dArr = qt.ResultRange.Value
    qt.Delete

    ThisWorkbook.Sheets(1).Cells.Clear
    Exit Sub
err1:
Stop
End Sub

解决方案

Same issue exists on Windows. Here are 2 possible answers.

1) Not sure it is compatible with MAC: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_other-mso_2007/connection-error-when-network-down/35d5be85-f850-4953-a393-6fafa687961f?messageId=0c063768-69c7-43ce-a9ed-9139f8cfd830

2) in principle, this should work I have not done it. Requires sqlcmd.exe = OS level sql client where you can query at command line. At OS prompt, get a simple "select 1" to work using sqlcmd.exe that either succeeds and creates a sqlSuccess.{TestID}.tmp file or fails and does nothing. Then build a VBA function, say fnGetConnString() that runs the commands and verifies that the sqlSuccess.{TestID}.tmp file exists and deletes it. The function could halt your program if it fails or return the connstring you desire and run as (pseudo code):

connstring = fnGetConnString( {connection string inputs})
if connstring = '' then 
    msgbox( "Not a valid connection or sever unavailable: {connection string inputs}"
    exit sub ''#halt all
end if

Among other things, the function needs to take {TestID} an input to guarantee that it has the right file.

for sqlcmd help, see: https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15

for OS command from VBA see: Execute a command in command prompt using excel VBA

这篇关于测试SQL Server连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！