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

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

problem while creating index for temporary table...

Hi,

i have created index for a temporary table and this script should used by multiusers.So when second user connecting to it is giving index i mean object already exists.

So what i need is when the second user connected the script should create one more index on temporary table.Will sql server provide any random way of creating indexes if the index exists already with that name?

Thank You,

Seeing the actual script will be helpful.

What advantage do you think you get from creating multiple (identical) indices on the same table?

|||

Nope..

SQL Server is cleaver enough to handel this situation.

When you create a index or constraint on the Temp Table, eventhough the index name is duplicate it will allow.

But it only possible on temp tables (prefixed with single #).

To Test this,

Open Two window,

Execute the below window on the opened 2 window..

create table #test

(

id int

);

Insert Into #test values(1);

Insert Into #test values(2);

Create clustered index testindex on #test(id)

Now you wont get any error on any of the window. Rite?

To fetch the created index details, execute the below code on any one of the window..

select * from sysindexes where name like '%test%'

Now you can see the 2 rows with same indexname but refereing with different table. Yes. all the temp tables (#) will be suffixed with unique number to avoid the object already found error while multiple users connects.