Reporting off of stored procedures

Hi, I'm new to SQL Reporting Services. I want to create a report from a dataset where the source is a stored procedure. The sp does several things to generate the ultimate reporting rows; temp table(s) are created and updated, joined with other tables and views, etc. The final line of the sp is: select * from #temptable. I'm getting an error that the temp table is an invalid object. Also, this sp used to have input parameters which caused errors also, so I just hard coded values to get past that. Any ideas here? Thanks, Marta.

You might want to put the data you are ultimately querying in a permanent table, not a temp table. It may be that when you return the cursor, the temp table is no longer available and when the report is going through the cursor and trying to get records, the data is not available anymore.

|||

Hi,

The problem must be elsewhere. I am doing this without problem.

Did you set the report dataset to type StoredProcedure?

Did you try to call your sp from a query window?

Did you create the datasource as to be of type Microsoft SQL Server?

Also, I do not like the " Select * " try to specify the columns if you can.

As Jayplus said, a permanent table would be better (if the data will remain unchanged for a while).

Philippe