Handling Temp tables in reporting services
Today in my 24Hrs of PASS session I highlighted the
issue of using temp tables with reporting services.
My solution is to use a design time variable, and the feature that all paths
are executed when FMTONLY is set ON
The code I used is along these lines.
@design = 0
@design = 0
--Put your main processing in
execute( 'DBCC sqlperf(logspace)')
row_number() over (order by dbname)row
Note that if using TSQL embedded in your report you have to
drop the temp table. Or you could check for its existence at the start http://sqlblogcasts.com/blogs/simons/archive/2005/11/09/Check-for-existence-of-temporary-table.aspx
If you are using a stored procedure you don't as the temp table
will be dropeed at the end of the stored procedure.
The other thing to note that I mentioned today was that
reporting services can handle temp tables but it does so by running the query.
If you have a long running query then you really don't want reporting services
to be doing this just to get the meta data.
There does seem to be a feature in that once RS has determined a
data set uses a temp table then it will carry on executing the code and not
using SET FMTONLY ON option to get the metadata, this is what got me in the