VBA Excel 中的 SQL Server 存储过程

SQL Server stored procedure in VBA Excel

提问人：Atila D. Grings 提问时间：8/10/2023 最后编辑：marc_sAtila D. Grings 更新时间：8/14/2023 访问量：71

问：

我在网上看了一下，发现了很多类似的帖子，但没有任何东西可以用来解决我的问题。

我正在尝试从 VBA （Excel 2019） 中的 SQL Server 访问存储过程，但我总是遇到相同的错误，一个或另一个：

关闭对象时不允许操作

对象不支持此属性或方法

有人可以看一下并给出建议吗？

Sub SQP_Procedure_Into_Excel()
    Dim conn As Object
    Dim cmd As Object
    Dim strConn As String
    Dim ws As Worksheet
    Dim rs As ADODB.Recordset
    
    strConn = "Provider=SQLOLEDB;Data Source=##########;Initial Catalog=##########;Integrated Security=SSPI;"
    
        Set conn = CreateObject("ADODB.Connection")
            conn.Open strConn
        
        Set cmd = CreateObject("ADODB.Command")
    
    With cmd
        .ActiveConnection = conn
        .CommandText = "[###].[############]"
        .CommandType = 4 ' adCmdStoredProc
    End With
    
    Set rs = cmd.Execute

    Dim startcol As Long
    startcol = 1
    With ActiveWorkbook
        Do While Not rs Is Nothing
    
            Dim col As Long
            For col = 0 To rs.Fields.Count - 1
                .Cells(1, startcol + col) = rs.Fields(col).Name
            Next col
            .Cells(2, startcol).CopyFromRecordset rs
    
            startcol = startcol + rs.Fields.Count + 1
            Set rs = rs.NextRecordset
        Loop
    End With
    
End Sub
sql-server excel vba 存储过程

评论

2赞 siggemannen 8/10/2023
我不认为nextrecordset是正确的方法。你可能想要rs。下一个
0赞 Atila D. Grings 8/10/2023
这甚至不是问题，因为代码不会在“CopyFromRecordSet”部分停止。我也尝试过其他方法，但没有任何效果
1赞 Tim Williams 8/10/2023
总是有助于准确地说出哪一行给了你什么错误......
0赞 Atila D. Grings 8/10/2023
这条线会根据我想要做的事情而变化。如果我将 CopyFromRecordset（实际上给了我错误）更改为另一个版本的循环，则错误会转到“无法关闭对象”指向 rs 上的错误。EOF
0赞 Tim Williams 8/11/2023
在添加后立即检查返回的记录集是否处于打开状态，然后再尝试从中读取。如果打开，则值应为 1。learn.microsoft.com/en-us/sql/ado/reference/ado-api/......如果未打开，则需要调查存储过程。Set rs = cmd.ExecuteDebug.Print rs.State

答：

0赞 Ike 8/10/2023 #1

您必须使用 As 和 are 对象来引用 as 'ActiveConnection：connActiveConnectionSetconn

With cmd
   Set .ActiveConnection = conn
   .CommandText = "[###].[############]"
   .CommandType = 4 ' adCmdStoredProc
End With

评论

0赞 Atila D. Grings 8/10/2023
嘿，艾克，没想过那个。我仍然有属性方法错误，指向“copyFromRecordedset rs”
0赞 Ike 8/10/2023
啊 - 我没有看到：你正在使用然后引用 -> 这不起作用。您必须有一个工作表作为基础，而不是工作簿With ActiveWorkbook.cellsWith
0赞 Atila D. Grings 8/10/2023
这不可能是问题所在。我用工作表试过了，但仍然有同样的错误，我认为这是 excel 版本没有“copyfromrecordedset”选项。2019 在这里
0赞 Galaxiom 8/11/2023
关于 Ike 对使用 Set 的指示，ADODB 中的一个异常现象是，尽管 ActiveConnection 属性是 Object，但它可以是 Let 或 Set。我认为在旧版本的Office中，Set实际上导致了错误。
0赞 CDP1802 8/10/2023 #2

无需迭代CopyFromRecordset

Option Explicit

Sub macro1()

    Const startcol = 1

    Dim conn As ADODB.Connection, cmd As ADODB.Command, rs As ADODB.Recordset
    Dim strConn As String, col As Long
    
    strConn = "Provider=SQLOLEDB;Data Source=##########;Initial Catalog=##########;Integrated Security=SSPI;"
    
    Set conn = New ADODB.Connection
    conn.Open strConn
        
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = conn
        .CommandText = "[###].[############]"
        .CommandType = 4 ' adCmdStoredProc
        Set rs = .Execute
    End With

    With ActiveSheet
        For col = 0 To rs.Fields.Count - 1
            .Cells(1, startcol + col) = rs.Fields(col).Name
        Next col
        .Cells(2, startcol).CopyFromRecordset rs
    End With
    
End Sub

评论

0赞 Atila D. Grings 8/10/2023
我收到一条“应用程序定义或对象定义错误”消息，突出显示“copyfromRecordSet”行
0赞 CDP1802 8/10/2023
您是创建记录集还是更新？
0赞 Atila D. Grings 8/10/2023
它将数据从数据库带到一个干净的 spredsheet 中
0赞 CDP1802 8/10/2023
如果 Microsoft SQL Server 使用 .你有管理员权限吗？.CommandText = "[sys].[sp_who]
0赞 Atila D. Grings 8/10/2023
我现在确实可以访问服务器，只是缺乏有关如何在它们上工作的知识。我们已经有一个团队来创建脚本、视图/过程，所以我正在尝试一种方法将这些脚本合并到 Excel 中，以使事情变得更容易。

上一个：使用 Excel 从 Linux 电脑上的 SQL Server 检索数据

下一个：在 VBA Access 中将 null 日期时间值传递给 SQL Server 存储过程