Using Temp Tables in SPROC output
-
I was trying to use a store procedure that it's result set came from a temp table and when I was creating the report I received a message #output table doesn't exist. Is this a known bug and is there a work around? Service Pack?
Thanks,
Kevin
-
The question is a little confusing, do you have a stored proc that is using a temp table, or are you trying to pull data from a seperate temp table from within a proc?
-
Better to give an example
create procedure usp_rpt_list
as
BEGIN
create table #output
(id integer,
id_desc varchar(10),
active integer
)
insert into #output (id, id_desc)
select id, id_desc from tableA
...Logic to set active column
SELECT id, id_desc, active from #output
END
-
I don't see any inherent problems in your proc, I would get rid of the BEGIN and END unless you are using them in some logic not posted. Double check your code to make sure that you are not dropping the temp table before your select statement.
-
what application calls the stored procedure?
-
There are issues with using temp tables in reporting services. It has to do with the way the meta data is returned. It should work if you manually add the field list into the dataset.
--------------------
Colt 45 - the original point and click interface
-
I have had a similar problem, more than once, when using a stored procedure as the source for a Crystal report. The stored procedure executed fine and returned the expected results but Crystal wouldn't recognize the data. Originally, the procedure returned the contents of a table variable. When Crystal wouldn't recognize the data, I changed it to use a temp table which didn't work either. Ultimately, I was forced to make the SP truncate and repopulate a regular table. The VB interface has to call the SP and then execute the Crystal report. Fortunately, it is an obscure report used only by one power user. You can imagine the potential problems if multiple users ran the report at the same time.
-
Bear in mind that the # temp tables (table variables) are only open or active during the duration of the session. Once the stored procedure is finished executing, temp tables are automatically deleted from the system. This is not applicable when you are using recodsets in your vb or asp scripts since recordsets requires the tables to be opened.
-
Kevin,
The code you posted compiled and executed just fine for me. If I try to run the estimated execution plan I get the "Invalid object name '#output'." error.
In this case, I create the table and populate it, then run the eep.
cl
Signature is NULL
-
The reason for the error when running the estimated execution plan is because the procedure is not actually run, therefore the temp table is not created.
As I posted previously the error occurs because reporting services can't determine the correct structure from the meta data.
If the fields are manually entered in, everything should work.
--------------------
Colt 45 - the original point and click interface -
Phill,
What do you mean, "fields are manually entered in"?
cl
Signature is NULL
-
When you define a dataset in Reporting Services you can manually enter the fields in the Dataset Properties dialog.
--------------------
Colt 45 - the original point and click interface -
Thanks for the input
-
One last point, maybe... if you create the report using the wizard you can't get past the "Cannot find temp table" bit. If you create the report without the wizard, however, it spits when you first reference the stored proc, but you get to the design window. If you then click the execute button, it goes off, runs the proc and retrieves all the fields just fine & you're away!
Hope this helps,
Matt
-
Dear phillcart
thanks for this information. But Could you please give me exact information how to manualy enter fields.. .
I need to use temp tables in stored procedures (for very large queries)but I can implement it in Reporting Services report.
THanks
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply