Excel VBA调用SQL存储过程返回伪造连接错误

Question

我正在使用一个脚本来循环循环通过Excel表，将参数传递到SQL Server过程，该过程采用三个PARMS并根据PARMS生成记录。当我拨打电话时，它实际上确实正确地传递了参数，执行过程并写入记录，但是随后我遇到了一个运行时错误" 1004查询没有运行..."

我发现了要更改背景查询的状态的评论= false-检查。设置nocount -on -check ...仍会遇到错误。

SQL Server过程确实包含了我没有传递的ReturnValue Parm（1/0），因为当我尝试执行此操作时，它说明了"太多参数"。这可能是问题吗？不确定如何接收回报值。

这是我的VBA代码：

Sub CallProc()
    Dim ws As Worksheet
    Worksheets("AllTags").Activate
    Dim Parm1 As String
    Dim Parm2 As String
    Dim Parm3 As Integer
    Dim RowIndex As Long
    Dim ReturnValue As Integer
    RowIndex = 2
   Do While Sheets("AllTags").Cells(RowIndex, 2).Value <> ""
   If Sheets("AllTags").Cells(RowIndex, 3).Value > 0 Then
   ReturnValue = 0
   Parm1 = Sheets("AllTags").Cells(RowIndex, 1).Value
   Parm2 = Sheets("AllTags").Cells(RowIndex, 2).Value
   Parm3 = Sheets("AllTags").Cells(RowIndex, 3).Value
   With ActiveWorkbook.Connections("TagUpload").OLEDBConnection
   ' SET NOCOUNT ON;  ** IT DOESN'T LIKE THIS 
    .BackgroundQuery = False
    .CommandText = "EXECUTE DCSTransfer.dbo.InsertTags '" & Parm1 & "', '" &  
        Parm2 & "', " & Parm3
    .CommandType = xlCmdSql
    .Connection = Array( _
    "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security  
     Info=True;Initial Catalog=DCSTransfer;Data Source=bdata03;Use Proc" _
    , _
    "edure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation 
      ID=B-MIS-NWILSON1;Use Encryption for Data=False;Tag with co" _
    , "lumn collation when possible=False;")

    .RefreshOnFileOpen = False
    .SavePassword = False
    .SourceConnectionFile = ""
    .SourceDataFile = ""
    .ServerCredentialsMethod = xlCredentialsMethodIntegrated
     ActiveWorkbook.Connections("TagUpload").Refresh
    .BackgroundQuery = False
     On Error Resume Next  ' **** NEVER GETS THIS FAR ***
     End With
     End If
     RowIndex = RowIndex + 1
     Loop
     End
 End Sub

这是我在SQL Server中手动调用该过程并输入PARMS时生成的脚本：

USE [DCSTransfer]
 GO
DECLARE @return_value int
 EXEC   @return_value = [dbo].[InsertTags]
        @Badge = N'9046',
        @StartTag = N'80996',
        @Qty = 70
 SELECT 'Return Value' = @return_value
 GO

还有其他建议吗？

Answer 1

弄清楚了...在整整两天以上痛苦的一天之后！

更改了命令文本以匹配存储过程：

    .CommandText = "Declare @return_Value int EXEC @return_Value = DCSTransfer.dbo.InsertTags '" & Parm1 & "', '" & Parm2 & "', " & Parm3 & "SELECT 'Return Value' = @return_value"