Execute Stored Proc Using ADODB

[NeedForExcel]

Hi,

I use a Stored Proc which runs in SSMS properly.

It needs a Parameter that is actually a string of XML Tags which I generate in excel. This generates perfectly as well. I can say this as I copy the generated XML and paste it in the parameter and the Stored Proc Runs perfect.

The Problem is, when I use ADODB to Execute this stored Proc from Excel VBA, the stored Proc does not seem to work in SSMS.

Here is the Code -

Please Login or Register  to view this content.

Note: The SourceQuery variable has all the required things to run the SQL Stored Proc, like the EXECUTE Statement etc.

[Kyle123]

Why use a recordset at all? You aren't returning anything, just use the execute method of the connection object

[NeedForExcel]

Yes I got that and I added these below lines before the With SQLRecordSet statement.

Please Login or Register  to view this content.

But the funny thing is, when I comment the With End With SQLRecordSet lines, the data stops updating in SSMS.

Not sure why..

Now the Code looks like this -

Please Login or Register  to view this content.

[Kyle123]

So remove the recordset?

Please Login or Register  to view this content.

So the below should be sufficient:

Please Login or Register  to view this content.

[NeedForExcel]

Runs perfectly..

Thank you Kyle..

Rep Added, Thread marked as solved..

I also have another issue, I guess Ill have a new post for that..

[Kyle123]

Glad it helped