SQL Server - Reporting Services and Temp tables

Asked By Hassan

28-Jan-08 11:22 PM

I was going over through the report wizard and in the query string specified
the stored procedure that i want to run such as exec dbo.sproc1
and it gives me an error

There is an error in the query. Invalid object name '#temp1'.


Sproc runs fine when run in Management studio.. just gives me that error in
the report wizard.

Thanks

29-Jan-08 04:47 PM

Try using @table variable

30-Jan-08 08:34 AM

I have mixed results with the wizard and stored procedures. Having said
that, if you use temp tables here is a handy list on how to write your
stored procedure to use temp tables in a RS compatible way:
1. Click on the refresh fields button (to the right of the ...) if your
field list is not showing up.

2. Do not use set nocount on

3. Do not explicitly drop the temp tables. Let the temp tables just fall out
of scope. SQL Server will properly dispose of them when they are no longer
needed. I think people explicitly drop them due to habit. Most likely at one
time it was the proper thing to do. It is not necessary and if you drop them
then stored procedures will not work with RS.

4. Have your last statement be a select

If none of these work then add Set FMTONLY Off  (the below is from Simon
Sabin a SQL Server MVP). Here is his explanation: "The issue with RS is that
the rowset of the SP is defined by calling the SP with SET FMTONLY ON
because Temp tables don't get created if you select from the temp table the
metadata from the rowset can't be returned. This can be worked around by
turning FMTONLY OFF in the SP."

I have found this to only be an issue when you create a temp table in your
stored procedure that is then filled with data from another stored
procedure.



--

Bruce Loehle-Conger

MVP SQL Server Reporting Services

29-Apr-08 09:18 AM

Bruce,

I am trying something similar for my report.  Only, I am using one
procedure.  When I run the procedure in the dataset, it runs properly and
generates the field list.  However, when I preview the report, I get the
following error:

Query execution failed for data set 'dataset'.
There is already an object named '##table' in the database."

Any ideas there?

Thanks!

29-Apr-08 11:14 AM

You are using a global temporary table. Your stored procedure must be
creating it without first checking on whether it already exists. Global temp
tables are removed once all the users using it have disconnected. I have
never worried about the development environment but I would guess that all
the users (your self) have not disconnected so when it tries to preview the
report the global temp table is still there.

Do you really need to be using a global temp table. My feeling is that
global temp tables should be a last resort.


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

29-Apr-08 11:42 AM

Well, I can't explain it, but I initially tried a local temp table and had
issues.  I changed my code back to a local temp table and now the report is
running just fine.  Thanks!

One other question.  Do you know if there is an issue running multiple SP w/
temp tables on the same report?

Thanks again.

29-Apr-08 12:16 PM

Not if they are using local temp tables, not global. If you follow my advice
you should not have any problem with temp tables.


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

21-May-08 01:09 AM

Hi
Instead using the # temp table and ## temp table.. You can try with
@TABLE Variable.

Thanks



ce
ad
rt
SP

e

ly
t
ur
y


N
d
n
g
t

05-Feb-10 06:30 AM

I was trying many solution before this,but when i used Set FMTONLY Off,it worked perfectly with RS





thanks

29-Mar-11 04:24 PM

thank you , you really helped me . :)

29-Mar-11 04:24 PM

Thank you , You really helped me :)