Problem while using stored procedures with temporary tables in dataset

I am trying to generate a report using SQL Server Reporting Service. The dataset is passed the results from a stored procedure. The stored proc contains a temporary table. On exceuting of proc, it fetches the result but when I try to save dataset I get following error message

Invalid object name '#AdditionalParams'. (.Net SqlClient Data Provider)

And no colums are returned in the data set created.

Any help on this would be appreciated.

Thanks in advance

If possible, try using a table variable instead, or create a physical table first.

http://www.odetocode.com/Articles/365.aspx

Here are some workarounds for temp tables.

http://www.sql-server-performance.com/rd_temp_tables.asp

If you have to, try using set fmtonly off in stored procedure.

http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

cheers,

Andrew

|||

I exceuted the proc as stored procedure. And latter I added a new field to same dataset, as it didnt had any field because it had thrown error. I refreshed the datset and I got all the dataset fields although initally it showed error and it worked.

But their is essentially problem the way datset are handled in reporting service.

Thanks