Stored Procedure called via Excel data connection

Question

I have table in a Excel 2007 workbook. The table has an external SQL data connection to a stored procedure. Inside the stored procedure I'd like to clear and populate an intermediate table before querying data from it.

The stored procedure works if I don't have the INSERT statement in it (I insert the data outside Excel), but as soon I try to insert into my temporary table in the stored procedure, I get an error "The query did not run or the database could not be opened".

What's interesting too is that despite the error, the insert inside the stored procedure still gets executed - the data is there after the error. Anybody knows how I can do the insert without triggering the error?

Answer 1

I did some more digging and I came across this article that contained the answer: Running Advanced SQL Stored Procedures from Excel

Basically before my insert I have to put SET NOCOUNT ON and after the insert, I add SET NOCOUNT OFF.