While trying to configure a report subscription in SQL Server Reporting Services earlier today, I ran across a problem I've seen a few times, and a few members of my project team have unsuccessfully attacked. Having finally found a resolution to the problem, I figured I'd make an entry here so I could find it easily later.
In our subscription query, we need to make use of Temporary tables. The Wizard for generating the subscription itself doesn't naturally find this very friendly, throwing an error that's only slightly helpful:
"The dataset cannot be generated. An error occurred while connecting to a datasource, or the query is not valid for the datasource. (rsCannotPrepareQuery) Invalid object name '#MyTempTable'."
After quite a bit of diggin around, I found this post, which described a resolution to the problem. Basically, by adding "SET FMTONLY OFF" at the beginning of the query will alleviate the issue. As this wasn't a command I was familiar with, a quick tour of MSDN provided the explanation: "Returns only metadata to the client. Can be used to test the format of the response without actually running the query."
So by turning it off, it forces the Wizard to evaluate the query--not the non-existant metadata from a temporary table that doesn't exist yet.