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.

set fmtonly on

declare @design int

set @design = 0


if (1=2)


  set fmtonly off

  set @design=1



create table #logspace

(dbname varchar(100)

 ,LogSize numeric(10,2)

,LogSpaceUsed numeric(4,2)

,Status int)


if @design = 0


  --Put your main processing in here

  insert into #logspace

  execute( 'DBCC sqlperf(logspace)')



select row_number() over (order by dbname)row

,dbname, logsize, logspaceUsed

from #logspace


drop table #logspace


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 presentation today

Published 02 September 2009 15:27 by simonsabin


No Comments