temporary table issue - SQL Server Forums

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.

All Forums

Development Tools

Reporting Services Development

temporary table issue

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2006-03-02 : 16:06:04

I am trying to generate a report using temporary tables.


Create Table #tempTbl
( CID int,
  DID int
)
insert into #tempTbl
select * from tabl1

insert into #tempTbl
select * from tabl2

insert into #tempTbl
select * from tabl3

select * from #tempTbl

The query executes right in Query Analyzer but when used in SQL Reporting Services, gives an error:


There is an error in the query. ERROR [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#tempTbl'.

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared

Any clue?

Thank you in advance.
PKS.

jhermiz

3564 Posts

Posted - 2006-03-02 : 21:43:04

you need to drop the temp table and recreate it...
first do this:

DROP TABLE #tempTbl

then the rest of your code

Then make sure you CHANGE your code to add a drop table #tempTbl at the end of your code.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources