Temporary Table problem

Question

Post reply

Temporary Table problem

suhas.kanade

SSC-Addicted

Points: 449
More actions
June 26, 2008 at 3:11 am

#70332

We have to write a stored procedure in SQL 2000 in which we need to create dynamically 3-5 tables depending on parameters. But in that we are facing a problem while accessing temporary table. A small & sample of code in stored procedure is as follow..
declare @strTable varchar(50)
declare @strQuery nvarchar(100)
SET @strTable = '#tmp1'
set @strQuery = 'select * into ' + @strTable + ' from user_mgmt'
print @strQuery
exec sp_executesql @strQuery
set @strQuery = 'SELECT * FROM ' + @strTable
print @strQuery
exec sp_executesql @strQuery
after running this script query analyser gives error...
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#tmp1'.
Pls suggest the possible causes of this problem or what we need to change in this procedure.
Thanks in advance.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

karthik M One Orange Chip Points: 29139 More actions · Answer 1

I have executed the below query, It executed without error message.

declare @strTable varchar(50)

declare @strQuery nvarchar(100)

Select @strTable = '#tmp1'

select @strQuery = 'select * into ' + @strTable + ' from X'

print @strQuery

exec (@strQuery)

Change SET to SELECT. But it may not be the problem,let us see, change it and let me know.

karthik

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 2

Karthik,

Try to select from the temp table the code just made...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 3

suhas.kanade (6/26/2008)
We have to write a stored procedure in SQL 2000 in which we need to create dynamically 3-5 tables depending on parameters. But in that we are facing a problem while accessing temporary table. A small & sample of code in stored procedure is as follow..
declare @strTable varchar(50)
declare @strQuery nvarchar(100)
SET @strTable = '#tmp1'
set @strQuery = 'select * into ' + @strTable + ' from user_mgmt'
print @strQuery
exec sp_executesql @strQuery
set @strQuery = 'SELECT * FROM ' + @strTable
print @strQuery
exec sp_executesql @strQuery
after running this script query analyser gives error...
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#tmp1'.
Pls suggest the possible causes of this problem or what we need to change in this procedure.
Thanks in advance.

Suhas... the problem is that temp tables are very scope sensitive... as soon as the dynamic SQL that creates the temp table ends, the scope of the temp table ends. You need to include everything about the query(s) in a single dynamic EXEC.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

suhas.kanade SSC-Addicted Points: 449 More actions · Answer 4

Jeff,

Thanks for u'r suggestion.

I will check it out.

suhas.kanade SSC-Addicted Points: 449 More actions · Answer 5

HELLO GUYS,

I have resolved this problem. Actually I was creating local temp table. The scope of any local temp table is into the block in which it is created (function,stored proc. or DB connection). As I was executing temp table query in sp_executesql which is a stored procedure temp table gets automatically drop after execution of stored procedure.

I have changed the scope of local temp table to global by declaring temp table as '##tmp1' instead of '#tmp1'. So my final query becomes...

declare @strTable varchar(50)

declare @strQuery nvarchar(100)

SET @strTable = '##tmp1'

set @strQuery = 'select * into ' + @strTable + ' from user_mgmt'

print @strQuery

exec sp_executesql @strQuery

set @strQuery = 'SELECT * FROM ' + @strTable

print @strQuery

exec sp_executesql @strQuery

And it works fine.

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 6

Ummm... yeah... just don't try to run the same proc twice at the same time unless you detect if the table is present before you make it...

... and be pretty sure that you name the table something pretty unique so it doesn't interfere with other GTT's in other procs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

suhas.kanade SSC-Addicted Points: 449 More actions · Answer 7

yaah...

We are going to create these table as per user_name who is active in session & drop that table as soon as it's job over.

thanks for support.