SSRS 2005 Data Driven Subscription and Temporary table

Few days back I was setting up data driven subscription for a SSRS 2005 report. The command / query which populates the parameters was bit complex. I normally create the script in the SSMS and once it works just used to copy paster it in the data driven subscription. This T-SQL batch also worked in fine. But when I copied it into command / query window in the data driven subscription and hit the validate button, it generated an error

The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery) , Invalid object name ‘#tmp’

Though the error was thrown at the temporary table, I did not see any issue with it when I ran the same query in SSMS. I modified the query to use derived table instead of #tmp and it worked fine. Did some browsing and found that the command / query window does not allow all T-SQL even DELETE statement (Link).

However it seems that this is fixed in SSRS 2008, at-least the temporary table.

Advertisements

About Suresh Kumar D

Hardcore SQL Developer and aspiring Database Architect.
This entry was posted in SSRS. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s