Reporting Services and Temp tables
Hassan
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'.
#temp1 is one of the temp tables in my stored proc that I use.
Sproc runs fine when run in Management studio.. just gives me that error in
the report wizard.
Thanks
Bruce L-C [MVP]
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
"AnalystKumar" <Analys...@discussions.microsoft.com> wrote in message
news:A6590933-5CCC-41BA...@microsoft.com...
> Try using @table variable
jmann
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:
"An error has occurred during report processing.
Query execution failed for data set 'dataset'.
There is already an object named '##table' in the database."
Any ideas there?
Thanks!
Bruce L-C [MVP]
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
"jmann" <jm...@discussions.microsoft.com> wrote in message
news:543F03E8-4834-486C...@microsoft.com...
jmann
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.
Bruce L-C [MVP]
you shouldn't have any problem with temp tables.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jmann" <jm...@discussions.microsoft.com> wrote in message
news:4FCCBD72-5395-4682...@microsoft.com...
SUNDARAGURU S
Instead using the # temp table and ## temp table.. You can try with
@TABLE Variable.
Thanks
On Apr 29, 9:16 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@hotmail.com>
wrote:
> >> >> "AnalystKumar" <AnalystKu...@discussions.microsoft.com> wrote in
> >> >> message
> >> >>news:A6590933-5CCC-41BA...@microsoft.com...
> >> >> > Try using @table variable
>
> >> >> > "Hassan" wrote:
>
> >> >> >> 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'.
>
> >> >> >> #temp1 is one of the temp tables in my stored proc that I use.
>
> >> >> >> Sproc runs fine when run in Management studio.. just gives me that
> >> >> >> error
> >> >> >> in
> >> >> >> the report wizard.
>
> >> >> >> Thanks- Hide quoted text -
>
> - Show quoted text -
abhishekswarnkar
thanks
Bruce L-C [MVP] wrote:
I have mixed results with the wizard and stored procedures.
30-Jan-08
--
Bruce Loehle-Conger
Previous Posts In This Thread:
On Monday, January 28, 2008 11:22 PM
Hassan wrote:
Reporting Services and Temp tables
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
On Tuesday, January 29, 2008 4:47 PM
AnalystKuma wrote:
RE: Reporting Services and Temp tables
Try using @table variable
"Hassan" wrote:
On Wednesday, January 30, 2008 8:34 AM
Bruce L-C [MVP] wrote:
--
Bruce Loehle-Conger
On Tuesday, April 29, 2008 9:18 AM
jman wrote:
Bruce,I am trying something similar for my report.
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:
"An error has occurred during report processing.
Query execution failed for data set 'dataset'.
There is already an object named '##table' in the database."
Any ideas there?
Thanks!
"Bruce L-C [MVP]" wrote:
On Tuesday, April 29, 2008 11:14 AM
Bruce L-C [MVP] wrote:
You are using a global temporary table.
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
"jmann" <jm...@discussions.microsoft.com> wrote in message
news:543F03E8-4834-486C...@microsoft.com...
On Tuesday, April 29, 2008 11:42 AM
jman wrote:
Well, I can't explain it, but I initially tried a local temp table and had
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.
"Bruce L-C [MVP]" wrote:
On Tuesday, April 29, 2008 12:16 PM
Bruce L-C [MVP] wrote:
Not if they are using local temp tables, not global.
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
On Wednesday, May 21, 2008 1:09 AM
SUNDARAGURU S wrote:
HiInstead using the # temp table and ## temp table..
Hi
Instead using the # temp table and ## temp table.. You can try with
@TABLE Variable.
Thanks
wrote:
ce
ad
rt
SP
e
ly
t
ur
y
N
d
n
g
t
Submitted via EggHeadCafe - Software Developer Portal of Choice
Beer - Words To Live By
http://www.eggheadcafe.com/tutorials/aspnet/8c4ba353-b6b8-457b-9c25-7085092dc09c/beer--words-to-live-by.aspx
Bruce L-C [MVP]
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Abhishek Swarnkar" wrote in message
news:20102563033...@gmail.com...
Mahmoud Alam
> On Monday, January 28, 2008 11:22 PM Hassan wrote:
> 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
>> On Tuesday, January 29, 2008 4:47 PM AnalystKuma wrote:
>> Try using @table variable
>>
>> "Hassan" wrote:
>>> On Wednesday, January 30, 2008 8:34 AM Bruce L-C [MVP] wrote:
>>>> On Tuesday, April 29, 2008 9:18 AM jman wrote:
>>>> 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:
>>>>
>>>> "An error has occurred during report processing.
>>>> Query execution failed for data set 'dataset'.
>>>> There is already an object named '##table' in the database."
>>>>
>>>> Any ideas there?
>>>>
>>>> Thanks!
>>>>
>>>> "Bruce L-C [MVP]" wrote:
>>>>> On Tuesday, April 29, 2008 11:14 AM Bruce L-C [MVP] wrote:
>>>>> 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
>>>>>
>>>>> "jmann" <jm...@discussions.microsoft.com> wrote in message
>>>>> news:543F03E8-4834-486C...@microsoft.com...
>>>>>> On Tuesday, April 29, 2008 11:42 AM jman wrote:
>>>>>> 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.
>>>>>>
>>>>>> "Bruce L-C [MVP]" wrote:
>>>>>>> On Tuesday, April 29, 2008 12:16 PM Bruce L-C [MVP] wrote:
>>>>>>> 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
>>>>>>>> On Wednesday, May 21, 2008 1:09 AM SUNDARAGURU S wrote:
>>>>>>>> Hi
>>>>>>>> Instead using the # temp table and ## temp table.. You can try with
>>>>>>>> @TABLE Variable.
>>>>>>>>
>>>>>>>> Thanks
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> wrote:
>>>>>>>> ce
>>>>>>>> ad
>>>>>>>> rt
>>>>>>>> SP
>>>>>>>>
>>>>>>>> e
>>>>>>>>
>>>>>>>> ly
>>>>>>>> t
>>>>>>>> ur
>>>>>>>> y
>>>>>>>>
>>>>>>>>
>>>>>>>> N
>>>>>>>> d
>>>>>>>> n
>>>>>>>> g
>>>>>>>> t
Mahmoud Alam
> On Monday, January 28, 2008 11:22 PM Hassan wrote:
> 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
>> On Tuesday, January 29, 2008 4:47 PM AnalystKuma wrote:
>> Try using @table variable
>>
>> "Hassan" wrote:
>>> On Wednesday, January 30, 2008 8:34 AM Bruce L-C [MVP] wrote:
>>>> On Tuesday, April 29, 2008 9:18 AM jman wrote:
>>>> 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:
>>>>
>>>> "An error has occurred during report processing.
>>>> Query execution failed for data set 'dataset'.
>>>> There is already an object named '##table' in the database."
>>>>
>>>> Any ideas there?
>>>>
>>>> Thanks!
>>>>
>>>> "Bruce L-C [MVP]" wrote:
>>>>> On Tuesday, April 29, 2008 11:14 AM Bruce L-C [MVP] wrote:
>>>>> 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
>>>>>
>>>>> "jmann" <jm...@discussions.microsoft.com> wrote in message
>>>>> news:543F03E8-4834-486C...@microsoft.com...
>>>>>> On Tuesday, April 29, 2008 11:42 AM jman wrote:
>>>>>> 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.
>>>>>>
>>>>>> "Bruce L-C [MVP]" wrote:
>>>>>>> On Tuesday, April 29, 2008 12:16 PM Bruce L-C [MVP] wrote:
>>>>>>> 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
>>>>>>>> On Wednesday, May 21, 2008 1:09 AM SUNDARAGURU S wrote:
>>>>>>>> Hi
>>>>>>>> Instead using the # temp table and ## temp table.. You can try with
>>>>>>>> @TABLE Variable.
>>>>>>>>
>>>>>>>> Thanks
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> wrote:
>>>>>>>> ce
>>>>>>>> ad
>>>>>>>> rt
>>>>>>>> SP
>>>>>>>>
>>>>>>>> e
>>>>>>>>
>>>>>>>> ly
>>>>>>>> t
>>>>>>>> ur
>>>>>>>> y
>>>>>>>>
>>>>>>>>
>>>>>>>> N
>>>>>>>> d
>>>>>>>> n
>>>>>>>> g
>>>>>>>> t
>>>>>>>>> On Friday, February 05, 2010 6:30 AM Abhishek Swarnkar wrote:
>>>>>>>>> I was trying many solution before this,but when i used Set FMTONLY Off,it worked perfectly with RS
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> thanks