Reporting Services and Temp tables

[Hassan]

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

[AnalystKumar]

Post by Hassan
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

[Bruce L-C [MVP]]

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

Post by Hassan
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

[jmann]

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!

Post by Bruce L-C [MVP]
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
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

Post by Hassan
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

[Bruce L-C [MVP]]

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

Post by jmann
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
"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!

Post by Bruce L-C [MVP]
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
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

Post by Hassan
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

[jmann]

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.

Post by Bruce L-C [MVP]
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

Post by jmann
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
"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!

Post by Bruce L-C [MVP]
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
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

Post by Hassan
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

[Bruce L-C [MVP]]

Not if they are using local temp tables, not global. If you follow my advice
you shouldn't have any problem with temp tables.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

Post by jmann
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.

Post by Bruce L-C [MVP]
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

Post by jmann
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
"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!

Post by Bruce L-C [MVP]
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
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

Post by Hassan
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

[SUNDARAGURU S]

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

Thanks

Post by Bruce L-C [MVP]
Not if they are using local temp tables, not global. If you follow my advice
you shouldn't have any problem with temp tables.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

Post by jmann
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.

Post by Bruce L-C [MVP]
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

Post by jmann
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
"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!

Post by Bruce L-C [MVP]
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
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

Post by Hassan
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 -

[unknown]

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


thanks



Bruce L-C [MVP] wrote:

I have mixed results with the wizard and stored procedures.
30-Jan-08

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 o

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 selec

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-Conge

MVP SQL Server Reporting Service


"AnalystKumar" <***@discussions.microsoft.com> wrote in message news:A6590933-5CCC-41BA-BA12-***@microsoft.com...

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.sproc
and it gives me an erro

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 variabl

"Hassan" wrote:

On Wednesday, January 30, 2008 8:34 AM
Bruce L-C [MVP] wrote:

I have mixed results with the wizard and stored procedures.
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 o

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 selec

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-Conge

MVP SQL Server Reporting Service


"AnalystKumar" <***@discussions.microsoft.com> wrote in message news:A6590933-5CCC-41BA-BA12-***@microsoft.com...

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" <***@discussions.microsoft.com> wrote in message news:543F03E8-4834-486C-B99C-***@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]]

Glad it worked for you.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

Post by unknown
I was trying many solution before this,but when i used Set FMTONLY Off,it
worked perfectly with RS
thanks
I have mixed results with the wizard and stored procedures.
30-Jan-08
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
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
On Monday, January 28, 2008 11:22 PM
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
RE: Reporting Services and Temp tables
On Wednesday, January 30, 2008 8:34 AM
I have mixed results with the wizard and stored procedures.
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
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
On Tuesday, April 29, 2008 9:18 AM
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
"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!
On Tuesday, April 29, 2008 11:14 AM
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
On Tuesday, April 29, 2008 11:42 AM
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.
On Tuesday, April 29, 2008 12:16 PM
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
HiInstead using the # temp table and ## temp table..
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
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

[Mahmoud Alam]

thank you , you really helped me . :)

Post by Hassan
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

Post by Bruce L-C [MVP]
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
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

Post by jmann
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
"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!

Post by Bruce L-C [MVP]
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

Post by jmann
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.

Post by Bruce L-C [MVP]
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

Post by SUNDARAGURU S
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

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

[Mahmoud Alam]

Thank you , You really helped me :)

Post by Hassan
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

Post by Bruce L-C [MVP]
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
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

Post by jmann
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
"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!

Post by Bruce L-C [MVP]
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

Post by jmann
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.

Post by Bruce L-C [MVP]
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

Post by SUNDARAGURU S
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

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

Post by Mahmoud Alam
thank you , you really helped me . :)