Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.


Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.