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)

  begin

  set fmtonly off

  set @design=1

  end

 

create table #logspace

(dbname varchar(100)

 ,LogSize numeric(10,2)

,LogSpaceUsed numeric(4,2)

,Status int)

 

if @design = 0

  begin

  --Put your main processing in here

  insert into #logspace

  execute( 'DBCC sqlperf(logspace)')

  end

 

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

Comments

No Comments