Friday, July 1, 2011

The Query Did Not Run

Why thank you, Excel, that's a very helpful error message. Except I was watching the query execute on SQL Server, and it DOES run. Perhaps there could be a different problem?

The actual, exact text of the error is

The query did not run, or the database table could not be opened.

The sheet in question was running a stored procedure, which had been working fine, albeit very slowly. A coworker tuned the proc and happened to use a temp table to accumulate the results for multiple queries instead of a UNION, producing better execution plans for each. However, despite being 4x faster, the proc stopped running from Excel, throwing the error message above.

At first I thought there might be a problem with the final DROP #table statement in the proc, so I removed that. No help. I adjusted various properties of the database connection from Excel. No change. I fiddled with the table setup in Excel. Nothing.

Then I remembered that I had some difficulties with pyodbc a while back that were resolved by setting NOCOUNT ON in the query. I can't recall the exact cause, but it's something about the driver seeing the first count returned as the query result, when of course it's not in a proc that populates a table with a few queries and then selects from it as the final step. Anyway, I set NOCOUNT ON at the beginning of the proc and OFF at the end and voila, it worked. So try that if you're having difficulties with Excel not executing your stored proc correctly.

16 comments:

Phil said...

Score! Thanks! This was just what I was looking for. I'm gonna pay you some props with a post over on my blog:
Execute Stored Procedure From Excel With Temp Table

Owen said...

4 hours trying to get a proc to run before I found your post.
Thanks - seriously

themikeaustin said...

OMG! Finally, after reading all those useless Bing/Google return entries, something that's correct and to the point.

Thanks!

Unknown said...

Also tried for hours before I found your post.
Thank you!!!!!

David Duncan said...

You are a champ. Thanks for posting this find, as it saved me from pulling the rest of my hair out!

Kaleesh said...

It really helps.. Thanks.

Mark said...

I love you.

Fergal Moran said...

Beautiful, thanks so much..

Saritha said...

Thanks much!

Anonymous said...

Thank you so much. you saved my day

Unknown said...

Qamber(SQA)
thanks alot this is exactly what I was looking for. Keep it up God Bless you.

Unknown said...

Thank you so much !!!

Congratulation by post !!!

agonist said...

Great tip! It solved the exact problem I was having. Thank you!

agonist said...

Great tip! It solved the exact problem I was having. Thank you!

agonist said...

Great tip! It solved the exact problem I was having. Thank you!

Unknown said...

Thank You..Saved my day.