在 Excel VBA 代码中使用变量声明的 T-SQL 查询失败 [英] T-SQL query with variable declarations in Excel VBA Code fail

问题描述

我在 VBA 中使用声明"和设置"函数运行 SQL 查询时遇到问题.

I got a problem with running SQL query with "declare" and "set" functions in VBA.

Sheets("Arkusz1").Select
connstring = _
"ODBC;DRIVER=SQL Server;SERVER=my_database_server;UID=user;PWD=password;APP=Microsoft Office 2010;WSID=some_id;DATABASE=mydatabase"

With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Worksheets("Arkusz1").Range("A1"), Sql:=Array( _
"declare @dzisiaj date" & Chr(13), _
"set @dzisiaj = getdate()" & Chr(13), _
"select @dzisiaj as dzisiaj"))

    .BackgroundQuery = False
    .Refresh
End With

在 SQL Server 2012 中，该代码运行良好，但是...当我将其嵌入其中时，会出现运行时错误1004".VBA 代码也适用于其他查询.

In SQL Server 2012 that code works fine, but... when I embed it into it gives me a run-time error '1004'. Also VBA code works on other queries works well.

我的完整 SQL 查询有大约 90 行和 2 个变量声明(一个声明是来自另一个 30 行 SQL 查询的值)，因此必须包含变量声明:)

My full SQL query has about 90 lines with 2 variable declarations (one declaration is a value from another 30 line SQL query), so it's mandatory to include variable declarations :)

如何解决这个问题?

推荐答案

我想通了.关键是使用ADODB连接通过SQL Query导入数据.还需要在 Visual Basic 编辑器中的 Tools->References 中检查 Microsoft Active X Data Objects 2.0 库(快捷键:Excel 中的 Alt+F11).

I figured it out. The key is to use ADODB connection to import data via SQL Query. Also necessary is to check Microsoft Active X Data Objects 2.0 library in Tools->References in Visual Basic Editor (Shortcut: Alt+F11 in Excel).

所以，有一个我的 VBA 代码示例:

So, there is an example of my VBA code:

    Sub sql_query_import()

    ' Declarations
    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    ' Server connection settings
    Server_Name = "192.168.1.106\my_database" ' IP of server name
    Database_Name = "mydatabase" ' Database name
    User_ID = "myusername" ' User name
    Password = "mypassword" ' User password

    ' SQL Query
    SQLStr = "SET NOCOUNT ON " & Chr(13) ' it's mandatory if you don't want to get error 3704
    SQLStr = SQLStr & "declare @dzisiaj date " & Chr(13)
    SQLStr = SQLStr & "set @dzisiaj = getdate() " & Chr(13)
SQLStr = SQLStr & "select @dzisiaj as 'today'

    ' Connect to database
    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    ' Start connection
    rs.Open SQLStr, Cn, adOpenStatic

    ' Load data
    With rs
        For i = 1 To .Fields.Count
            Worksheets(1).Cells(1, i) = .Fields(i - 1).Name ' Include column name if not - delete it
        Next i
    End With
    Worksheets(1).Cells(2, 1).CopyFromRecordset rs ' Start loading data to Cell A2


    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
    End Sub

如果您不想收到错误 3704，则必须在 SQL 查询中使用SET NOCOUNT ON".另外，使用

Using in SQL Query "SET NOCOUNT ON" is necessary if you don't want to get error 3704. Also, using

SQLStr = "SET NOCOUNT ON " & Chr(13) ' it's mandatory if you don't want to get error 3704
SQLStr = SQLStr & "declare @dzisiaj date " & Chr(13)

是包含多行 SQL 查询的更有效方法 :)

is more efficient way to include multi-line SQL Queries :)

这篇关于在 Excel VBA 代码中使用变量声明的 T-SQL 查询失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！