Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sumitmahajan81
Starting Member
4 Posts |
 Posted - 2006-12-06 : 14:57:12
|
| 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 messageInvalid 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 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-06 : 16:04:17
|
| Here's a possible workaround - change the stored procedure and make the table a regular table. The create the dataset for the report (allow the stored procedure to run and create the permanent table). Once the dataset is saved, drop the permanent table and go back and change the stored procedure to use a temporary table again. |
 |
|
|
ARC
Starting Member
10 Posts |
Posted - 2007-01-12 : 03:44:38
|
Hi, I had a simalar problem In my case I found that the temp table was being dropped at the end of my proc (Housekeeping to keep testing simple, that I'd stopped seeing and forgot to take out).  So Make sure that the table stays active until the report has finished with it, also If you are nesting your stored procs you need to create the table in the one that is actualy called by your report and pass the name to any internal ones, as local temp tables cannot be seen by calling code.egSample code beginscreate proc1 @Paramsascreate table #TempTable (col list)exec( 'proc2 ' + '''' #TempTable + '''')'sample code endshth |
|
|
|
|
|
|
Problem while using stored procedures with tempora