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
-