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.