Error Using # Temporary Tables in Query for Creating Reports

I am encountering error while creating a report using SQL
with # temporary tables. Here's a sample code using the
Adventureworks database:
CREATE TABLE #PRODUCTDETAILS(
[WEIGHT] [FLOAT] NULL ,
[PRODUCTNUMBER] [NVARCHAR] (25) COLLATE
SQL_LATIN1_GENERAL_CP1_CI_AS NULL ,
[STANDARDPRICE] [MONEY] NULL
) ON [PRIMARY]
INSERT INTO #PRODUCTDETAILS
SELECT WEIGHT,
PRODUCTNUMBER,
STANDARDPRICE
FROM PRODUCT A,
PRODUCTVENDOR B
WHERE A.PRODUCTID = B.PRODUCTID
SELECT * FROM #PRODUCTDETAILS
I tried to create a new report (using the wizard) with the
following SQL in the query section. When I click on the
next button, I get an error "There is an error in the
query. Invalid object name '#productdetails'". I also
tried to compile the below statements into a stored
procedure, but I get the same error when I try to generate
the report using the stored procedure.
However I found out that if I create the report manually
(without the wizard) and type all the field names as per
the output of the query, the query runs successfully in the
Data tab and I am able to generate the report.
Well, considering the fact that I have about 800 reports
to be based on stored procedures using # temporary tables,
it would be a collosal task to manually create so many
reports. I am looking for an automated way of doing this
task.
Please help!Report Designer has a graphical query designer (VDT) and text based query
designer (GQD - Generic Query Designer). When you are in Data view you can
switch between the two by clicking the third button from the left on the
query designer toolbar. Temp tables can be used provided you use the GQD.
The GQD has a couple of variations: one for stored procedures and on for
table direct. You can activate these by changing the command type to stored
procedure or table direct on the Data Set dialog.
Report Wizard only uses the VDT which means it will not work with temp
tables.
Here are a couple of suggestions for reusing reports and data sources:
Have you checked into using shared data sources? These allow you to define a
data source once and reuse it among all the reports in a project.
If your reports have a similar structure approximate template behavior by
creating the specific report you want and then placing it in
C:\ProgramFiles\Microsoft SQL Server\80\Tools\Report
Designer\ProjectItems\ReportProject directory. These reports "templates"
will show up in Report Designer's Add New Item dialog along with the Report
Wizard, Report, and Data Source templates when you add a new report to a
project.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amar Karande" <akarande@.yahoo.com> wrote in message
news:2497501c45f84$90d4e650$a401280a@.phx.gbl...
> I am encountering error while creating a report using SQL
> with # temporary tables. Here's a sample code using the
> Adventureworks database:
> CREATE TABLE #PRODUCTDETAILS(
> [WEIGHT] [FLOAT] NULL ,
> [PRODUCTNUMBER] [NVARCHAR] (25) COLLATE
> SQL_LATIN1_GENERAL_CP1_CI_AS NULL ,
> [STANDARDPRICE] [MONEY] NULL
> ) ON [PRIMARY]
> INSERT INTO #PRODUCTDETAILS
> SELECT WEIGHT,
> PRODUCTNUMBER,
> STANDARDPRICE
> FROM PRODUCT A,
> PRODUCTVENDOR B
> WHERE A.PRODUCTID = B.PRODUCTID
> SELECT * FROM #PRODUCTDETAILS
>
> I tried to create a new report (using the wizard) with the
> following SQL in the query section. When I click on the
> next button, I get an error "There is an error in the
> query. Invalid object name '#productdetails'". I also
> tried to compile the below statements into a stored
> procedure, but I get the same error when I try to generate
> the report using the stored procedure.
> However I found out that if I create the report manually
> (without the wizard) and type all the field names as per
> the output of the query, the query runs successfully in the
> Data tab and I am able to generate the report.
> Well, considering the fact that I have about 800 reports
> to be based on stored procedures using # temporary tables,
> it would be a collosal task to manually create so many
> reports. I am looking for an automated way of doing this
> task.
> Please help!|||Amar,
I have been trying to find where I wrote down the solution to this. I hit
this same problem and from memory, was able to fix it by ensuring that the
fields returned in the query were not prefixed with the table name. e.g.
EmployyID rather than #employee.EmployeeID.
I will continue to see if I can find where I wrote it down. Let me know if
this fixes it.
Brian Boyce
"Amar Karande" <akarande@.yahoo.com> wrote in message
news:2497501c45f84$90d4e650$a401280a@.phx.gbl...
> I am encountering error while creating a report using SQL
> with # temporary tables. Here's a sample code using the
> Adventureworks database:
> CREATE TABLE #PRODUCTDETAILS(
> [WEIGHT] [FLOAT] NULL ,
> [PRODUCTNUMBER] [NVARCHAR] (25) COLLATE
> SQL_LATIN1_GENERAL_CP1_CI_AS NULL ,
> [STANDARDPRICE] [MONEY] NULL
> ) ON [PRIMARY]
> INSERT INTO #PRODUCTDETAILS
> SELECT WEIGHT,
> PRODUCTNUMBER,
> STANDARDPRICE
> FROM PRODUCT A,
> PRODUCTVENDOR B
> WHERE A.PRODUCTID = B.PRODUCTID
> SELECT * FROM #PRODUCTDETAILS
>
> I tried to create a new report (using the wizard) with the
> following SQL in the query section. When I click on the
> next button, I get an error "There is an error in the
> query. Invalid object name '#productdetails'". I also
> tried to compile the below statements into a stored
> procedure, but I get the same error when I try to generate
> the report using the stored procedure.
> However I found out that if I create the report manually
> (without the wizard) and type all the field names as per
> the output of the query, the query runs successfully in the
> Data tab and I am able to generate the report.
> Well, considering the fact that I have about 800 reports
> to be based on stored procedures using # temporary tables,
> it would be a collosal task to manually create so many
> reports. I am looking for an automated way of doing this
> task.
> Please help!|||Bruce,
I am using Generic Query Designer and it does not work
there either, unless you explicitly define the fields in
the Dataset properties. It seems like reporting services
engine is not able to retrieve fields from the temporary
table. Guess why? Because it does not exist.
As per your second suggestion, I think that might not work
too well in my case, because the report layouts (columns)
are very different.
The million dollar question for me is, would I have to
manually create reports from stored procedures, i.e.
define fields manually in the dataset? If thats the case,
it will take me forever to migrate to Reporting Services.
I am sure there should be a way around it. Atleast I hope
so :(
Thanks,
Amar
>--Original Message--
>Report Designer has a graphical query designer (VDT) and
text based query
>designer (GQD - Generic Query Designer). When you are in
Data view you can
>switch between the two by clicking the third button from
the left on the
>query designer toolbar. Temp tables can be used provided
you use the GQD.
>The GQD has a couple of variations: one for stored
procedures and on for
>table direct. You can activate these by changing the
command type to stored
>procedure or table direct on the Data Set dialog.
>Report Wizard only uses the VDT which means it will not
work with temp
>tables.
>Here are a couple of suggestions for reusing reports and
data sources:
>Have you checked into using shared data sources? These
allow you to define a
>data source once and reuse it among all the reports in a
project.
>If your reports have a similar structure approximate
template behavior by
>creating the specific report you want and then placing it
in
>C:\ProgramFiles\Microsoft SQL Server\80\Tools\Report
>Designer\ProjectItems\ReportProject directory. These
reports "templates"
>will show up in Report Designer's Add New Item dialog
along with the Report
>Wizard, Report, and Data Source templates when you add a
new report to a
>project.
>--
>Bruce Johnson [MSFT]
>Microsoft SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>"Amar Karande" <akarande@.yahoo.com> wrote in message
>news:2497501c45f84$90d4e650$a401280a@.phx.gbl...
>> I am encountering error while creating a report using
SQL
>> with # temporary tables. Here's a sample code using the
>> Adventureworks database:
>> CREATE TABLE #PRODUCTDETAILS(
>> [WEIGHT] [FLOAT] NULL ,
>> [PRODUCTNUMBER] [NVARCHAR] (25) COLLATE
>> SQL_LATIN1_GENERAL_CP1_CI_AS NULL ,
>> [STANDARDPRICE] [MONEY] NULL
>> ) ON [PRIMARY]
>> INSERT INTO #PRODUCTDETAILS
>> SELECT WEIGHT,
>> PRODUCTNUMBER,
>> STANDARDPRICE
>> FROM PRODUCT A,
>> PRODUCTVENDOR B
>> WHERE A.PRODUCTID = B.PRODUCTID
>> SELECT * FROM #PRODUCTDETAILS
>>
>> I tried to create a new report (using the wizard) with
the
>> following SQL in the query section. When I click on the
>> next button, I get an error "There is an error in the
>> query. Invalid object name '#productdetails'". I also
>> tried to compile the below statements into a stored
>> procedure, but I get the same error when I try to
generate
>> the report using the stored procedure.
>> However I found out that if I create the report manually
>> (without the wizard) and type all the field names as per
>> the output of the query, the query runs successfully in
the
>> Data tab and I am able to generate the report.
>> Well, considering the fact that I have about 800 reports
>> to be based on stored procedures using # temporary
tables,
>> it would be a collosal task to manually create so many
>> reports. I am looking for an automated way of doing this
>> task.
>> Please help!
>
>.
>|||Brian,
My query is set up to do a select * from #Temp table.
Should I do something else?
Thanks,
Amar
>--Original Message--
>Amar,
>I have been trying to find where I wrote down the
solution to this. I hit
>this same problem and from memory, was able to fix it by
ensuring that the
>fields returned in the query were not prefixed with the
table name. e.g.
>EmployyID rather than #employee.EmployeeID.
>I will continue to see if I can find where I wrote it
down. Let me know if
>this fixes it.
>Brian Boyce
>"Amar Karande" <akarande@.yahoo.com> wrote in message
>news:2497501c45f84$90d4e650$a401280a@.phx.gbl...
>> I am encountering error while creating a report using
SQL
>> with # temporary tables. Here's a sample code using the
>> Adventureworks database:
>> CREATE TABLE #PRODUCTDETAILS(
>> [WEIGHT] [FLOAT] NULL ,
>> [PRODUCTNUMBER] [NVARCHAR] (25) COLLATE
>> SQL_LATIN1_GENERAL_CP1_CI_AS NULL ,
>> [STANDARDPRICE] [MONEY] NULL
>> ) ON [PRIMARY]
>> INSERT INTO #PRODUCTDETAILS
>> SELECT WEIGHT,
>> PRODUCTNUMBER,
>> STANDARDPRICE
>> FROM PRODUCT A,
>> PRODUCTVENDOR B
>> WHERE A.PRODUCTID = B.PRODUCTID
>> SELECT * FROM #PRODUCTDETAILS
>>
>> I tried to create a new report (using the wizard) with
the
>> following SQL in the query section. When I click on the
>> next button, I get an error "There is an error in the
>> query. Invalid object name '#productdetails'". I also
>> tried to compile the below statements into a stored
>> procedure, but I get the same error when I try to
generate
>> the report using the stored procedure.
>> However I found out that if I create the report manually
>> (without the wizard) and type all the field names as per
>> the output of the query, the query runs successfully in
the
>> Data tab and I am able to generate the report.
>> Well, considering the fact that I have about 800 reports
>> to be based on stored procedures using # temporary
tables,
>> it would be a collosal task to manually create so many
>> reports. I am looking for an automated way of doing this
>> task.
>> Please help!
>
>.
>|||Temporary table used in stored procedures from SQL Server work just fine. I
do this all the time. I have had trouble going against Sybase but works
great for SQL Server. Make sure you have set the command type to
StoredProcedure. There there is a little used button that looks like the
refresh button for IE. If you hoover over it, it will say refresh fields.
Click on it and your field list will update to the correct fields returned.
I did this with the code you posted. You can be in either query designers.
No need to add fields by hand. I only do this if I have a calculated field I
want to add (a handy little trick to remember).
Bruce L-C
"Amar Karande" <akarande@.yahoo.com> wrote in message
news:249dd01c45f8e$ae354d20$a501280a@.phx.gbl...
> Bruce,
> I am using Generic Query Designer and it does not work
> there either, unless you explicitly define the fields in
> the Dataset properties. It seems like reporting services
> engine is not able to retrieve fields from the temporary
> table. Guess why? Because it does not exist.
> As per your second suggestion, I think that might not work
> too well in my case, because the report layouts (columns)
> are very different.
> The million dollar question for me is, would I have to
> manually create reports from stored procedures, i.e.
> define fields manually in the dataset? If thats the case,
> it will take me forever to migrate to Reporting Services.
> I am sure there should be a way around it. Atleast I hope
> so :(
> Thanks,
> Amar
> >--Original Message--
> >Report Designer has a graphical query designer (VDT) and
> text based query
> >designer (GQD - Generic Query Designer). When you are in
> Data view you can
> >switch between the two by clicking the third button from
> the left on the
> >query designer toolbar. Temp tables can be used provided
> you use the GQD.
> >
> >The GQD has a couple of variations: one for stored
> procedures and on for
> >table direct. You can activate these by changing the
> command type to stored
> >procedure or table direct on the Data Set dialog.
> >
> >Report Wizard only uses the VDT which means it will not
> work with temp
> >tables.
> >
> >Here are a couple of suggestions for reusing reports and
> data sources:
> >
> >Have you checked into using shared data sources? These
> allow you to define a
> >data source once and reuse it among all the reports in a
> project.
> >
> >If your reports have a similar structure approximate
> template behavior by
> >creating the specific report you want and then placing it
> in
> >C:\ProgramFiles\Microsoft SQL Server\80\Tools\Report
> >Designer\ProjectItems\ReportProject directory. These
> reports "templates"
> >will show up in Report Designer's Add New Item dialog
> along with the Report
> >Wizard, Report, and Data Source templates when you add a
> new report to a
> >project.
> >
> >--
> >Bruce Johnson [MSFT]
> >Microsoft SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >
> >
> >"Amar Karande" <akarande@.yahoo.com> wrote in message
> >news:2497501c45f84$90d4e650$a401280a@.phx.gbl...
> >> I am encountering error while creating a report using
> SQL
> >> with # temporary tables. Here's a sample code using the
> >> Adventureworks database:
> >>
> >> CREATE TABLE #PRODUCTDETAILS(
> >> [WEIGHT] [FLOAT] NULL ,
> >> [PRODUCTNUMBER] [NVARCHAR] (25) COLLATE
> >> SQL_LATIN1_GENERAL_CP1_CI_AS NULL ,
> >> [STANDARDPRICE] [MONEY] NULL
> >> ) ON [PRIMARY]
> >>
> >> INSERT INTO #PRODUCTDETAILS
> >> SELECT WEIGHT,
> >> PRODUCTNUMBER,
> >> STANDARDPRICE
> >> FROM PRODUCT A,
> >> PRODUCTVENDOR B
> >> WHERE A.PRODUCTID = B.PRODUCTID
> >>
> >> SELECT * FROM #PRODUCTDETAILS
> >>
> >>
> >> I tried to create a new report (using the wizard) with
> the
> >> following SQL in the query section. When I click on the
> >> next button, I get an error "There is an error in the
> >> query. Invalid object name '#productdetails'". I also
> >> tried to compile the below statements into a stored
> >> procedure, but I get the same error when I try to
> generate
> >> the report using the stored procedure.
> >>
> >> However I found out that if I create the report manually
> >> (without the wizard) and type all the field names as per
> >> the output of the query, the query runs successfully in
> the
> >> Data tab and I am able to generate the report.
> >>
> >> Well, considering the fact that I have about 800 reports
> >> to be based on stored procedures using # temporary
> tables,
> >> it would be a collosal task to manually create so many
> >> reports. I am looking for an automated way of doing this
> >> task.
> >>
> >> Please help!
> >
> >
> >.
> >|||It sure works:) I hope it worked through the wizard. That
would have saved me a lottttt of time. Some of my reports
have 40 columns :(
>--Original Message--
>Temporary table used in stored procedures from SQL Server
work just fine. I
>do this all the time. I have had trouble going against
Sybase but works
>great for SQL Server. Make sure you have set the command
type to
>StoredProcedure. There there is a little used button that
looks like the
>refresh button for IE. If you hoover over it, it will say
refresh fields.
>Click on it and your field list will update to the
correct fields returned.
>I did this with the code you posted. You can be in either
query designers.
>No need to add fields by hand. I only do this if I have a
calculated field I
>want to add (a handy little trick to remember).
>Bruce L-C
>"Amar Karande" <akarande@.yahoo.com> wrote in message
>news:249dd01c45f8e$ae354d20$a501280a@.phx.gbl...
>> Bruce,
>> I am using Generic Query Designer and it does not work
>> there either, unless you explicitly define the fields in
>> the Dataset properties. It seems like reporting services
>> engine is not able to retrieve fields from the temporary
>> table. Guess why? Because it does not exist.
>> As per your second suggestion, I think that might not
work
>> too well in my case, because the report layouts
(columns)
>> are very different.
>> The million dollar question for me is, would I have to
>> manually create reports from stored procedures, i.e.
>> define fields manually in the dataset? If thats the
case,
>> it will take me forever to migrate to Reporting
Services.
>> I am sure there should be a way around it. Atleast I
hope
>> so :(
>> Thanks,
>> Amar
>> >--Original Message--
>> >Report Designer has a graphical query designer (VDT)
and
>> text based query
>> >designer (GQD - Generic Query Designer). When you are
in
>> Data view you can
>> >switch between the two by clicking the third button
from
>> the left on the
>> >query designer toolbar. Temp tables can be used
provided
>> you use the GQD.
>> >
>> >The GQD has a couple of variations: one for stored
>> procedures and on for
>> >table direct. You can activate these by changing the
>> command type to stored
>> >procedure or table direct on the Data Set dialog.
>> >
>> >Report Wizard only uses the VDT which means it will not
>> work with temp
>> >tables.
>> >
>> >Here are a couple of suggestions for reusing reports
and
>> data sources:
>> >
>> >Have you checked into using shared data sources? These
>> allow you to define a
>> >data source once and reuse it among all the reports in
a
>> project.
>> >
>> >If your reports have a similar structure approximate
>> template behavior by
>> >creating the specific report you want and then placing
it
>> in
>> >C:\ProgramFiles\Microsoft SQL Server\80\Tools\Report
>> >Designer\ProjectItems\ReportProject directory. These
>> reports "templates"
>> >will show up in Report Designer's Add New Item dialog
>> along with the Report
>> >Wizard, Report, and Data Source templates when you add
a
>> new report to a
>> >project.
>> >
>> >--
>> >Bruce Johnson [MSFT]
>> >Microsoft SQL Server Reporting Services
>> >
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >
>> >
>> >"Amar Karande" <akarande@.yahoo.com> wrote in message
>> >news:2497501c45f84$90d4e650$a401280a@.phx.gbl...
>> >> I am encountering error while creating a report using
>> SQL
>> >> with # temporary tables. Here's a sample code using
the
>> >> Adventureworks database:
>> >>
>> >> CREATE TABLE #PRODUCTDETAILS(
>> >> [WEIGHT] [FLOAT] NULL ,
>> >> [PRODUCTNUMBER] [NVARCHAR] (25) COLLATE
>> >> SQL_LATIN1_GENERAL_CP1_CI_AS NULL ,
>> >> [STANDARDPRICE] [MONEY] NULL
>> >> ) ON [PRIMARY]
>> >>
>> >> INSERT INTO #PRODUCTDETAILS
>> >> SELECT WEIGHT,
>> >> PRODUCTNUMBER,
>> >> STANDARDPRICE
>> >> FROM PRODUCT A,
>> >> PRODUCTVENDOR B
>> >> WHERE A.PRODUCTID = B.PRODUCTID
>> >>
>> >> SELECT * FROM #PRODUCTDETAILS
>> >>
>> >>
>> >> I tried to create a new report (using the wizard)
with
>> the
>> >> following SQL in the query section. When I click on
the
>> >> next button, I get an error "There is an error in the
>> >> query. Invalid object name '#productdetails'". I also
>> >> tried to compile the below statements into a stored
>> >> procedure, but I get the same error when I try to
>> generate
>> >> the report using the stored procedure.
>> >>
>> >> However I found out that if I create the report
manually
>> >> (without the wizard) and type all the field names as
per
>> >> the output of the query, the query runs successfully
in
>> the
>> >> Data tab and I am able to generate the report.
>> >>
>> >> Well, considering the fact that I have about 800
reports
>> >> to be based on stored procedures using # temporary
>> tables,
>> >> it would be a collosal task to manually create so
many
>> >> reports. I am looking for an automated way of doing
this
>> >> task.
>> >>
>> >> Please help!
>> >
>> >
>> >.
>> >
>
>.
>