Execute SQL Server stored procedure through VBA which does not return rows

Question

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

which does not return any rows.

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)

I get the error:

Runtime error 2147217900

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

but I do not know what action query is.

You can use cnn.Execute to run a SQL command or a stored procedure. It's not really clear where your error happens currently. — Rory
– Rory, Commented May 23, 2016 at 15:21
Update. I added the last line to code which causes error. Set rs = cnn.Execute(Sql) Can you write full line of VBA code please. — Przemyslaw Remin
– Przemyslaw Remin, Commented May 23, 2016 at 15:26
Also as an extra - add SET NOCOUNT ON at the top of your stored procedure. Excel can often throw an error if this is not included. — Tom
– Tom, Commented May 23, 2016 at 16:23

Przemyslaw Remin · Accepted Answer · 2017-12-13 09:42:14Z

2

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

answered Dec 13, 2017 at 9:42

Przemyslaw Remin

7,06841 gold badges133 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Execute SQL Server stored procedure through VBA which does not return rows

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related