通过不返回行的VBA执行SQL Server存储过程 [英] Execute SQL Server stored procedure through VBA which does not return rows

问题描述

如何通过Excel VBA执行特殊的SQL Server存储过程?特殊，因为它不会像以下过程一样返回行:

How to execute special SQL Server stored procedure through Excel VBA? Special because it does not return rows like this procedure:

select * from tab1

如何执行准备报告的过程，就像这样:

How to execute the procedure which let's say prepares reports, like this one:

select * 
into tab2
from tab1

不返回任何行.

我正在使用此VBA代码:

I am using this VBA code:

Dim cnn As Object
Set cnn = CreateObject("ADODB.Connection")
Dim cmd As Object
Set cmd = CreateObject("ADODB.Command")
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")

Dim ConnectionString As String
cnn.ConnectionString = "driver={SQL Server};server=MYSERVERNAME;Database=MYDATABASE;Trusted_Connection=yes;"
cnn.Open

Sql = "EXEC [dbo].[Procedure_make_report] 'param1'"
Set rs = cnn.Execute(Sql)

我得到了错误:

运行时错误2147217900

Runtime error 2147217900

我在这里找到了一个线索: http://www.vbforums.com/showthread.php?541498-RESOLVED-runtime-error-2147217900-(80040e14)-invalid-syntax-error

I found a clue here: http://www.vbforums.com/showthread.php?541498-RESOLVED-runtime-error-2147217900-(80040e14)-incorrect-syntax-error

但是我不知道什么是动作查询.

推荐答案

基于罗里(Rory)和汤姆(Tom)的评论，我留下了可行的答案.

Based on comments of Rory and Tom, I leave the answer which works.

Dim cnn As Object
Set cnn = CreateObject("ADODB.Connection")

Dim cmd As Object
Set cmd = CreateObject("ADODB.Command")
    cmd.CommandType = 1      ' adCmdText
    cmd.CommandTimeout = 120 ' number of seconds to time out

Dim ConnectionString As String
cnn.ConnectionString = "driver={SQL Server};server=MYSERVERNAME;Database=MYDATABASE;Trusted_Connection=yes;"
cnn.Open

Dim SQL As String
SQL = "EXEC [dbo].[Procedure_make_report] 'param1'"

cnn.Open
    cmd.CommandText = SQL
    cmd.ActiveConnection = cnn
    cmd.Execute

If Not cnn Is Nothing Then
    cnn.Close
    Set cnn = Nothing
End If

这篇关于通过不返回行的VBA执行SQL Server存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！