Temp Tables in Reporting Service

Hello,

I am tring to create a report from a sp. In the sp I am selecting into a temp table.
example: select * from pubs into #tempTable

This is the error I recieve from report service:
There is an error in the query. Invalid object name '#tempTable'.

Any Ideas?

Thank You,

Matt

When the Report Designer tries to get metadata about the stored procedures resultset this will fail because it runs the stored procedure with SET FMTONLY ON. In a development environment you have choice of using a normal table to simulate the structure of the temporary table (this has to exist and cannot itself be created in the procedure) or manually add the fields of the dataset to the fields toolbar by right clicking on it and choosing Add. Becasue SET FMTONLY ON returns only metadata and does not actually execute the procedure, the temp table created in the procedure will not exist and it will fail to return the metadata.


HTH

Jasper Smith

quote:Originally posted by mheinrich

Hello,

I am tring to create a report from a sp. In the sp I am selecting into a temp table.
example: select * from pubs into #tempTable

This is the error I recieve from report service:
There is an error in the query. Invalid object name '#tempTable'.

Any Ideas?

Thank You,

Matt

Use a variable instead of a temp table.

If you run the stored procedure in the Data tab it will retrieve the fields and no longer give you the error.[<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />Jack Corbett, DBA<br />Nexfor Fraser Papers

You have to create #temp table before you can insert / access

try something like this
============================

CREATE PROCEDURE usp_partselector
@PartType as varchar(20)
AS
-- CREATE TEMP TABLE
create #temp (part varchar(10), qty int, cost real)
-- INSERT RECORDS
insert into #temp
select part, qty, cost from source table
Where partgroup = @PartType

-- GET RECORDS FROM TEMP TABLE
select * from #temp

GO
========================================