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 |
|
suhasckanade
Starting Member
8 Posts |
 Posted - 2008-06-26 : 05:22:59
|
| 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 @strQueryexec sp_executesql @strQuery set @strQuery = 'SELECT * FROM ' + @strTableprint @strQueryexec sp_executesql @strQuery after running this script query analyser gives error...Server: Msg 208, Level 16, State 1, Line 1Invalid object name '#tmp1'.Pls suggest the possible causes of this problem or what we need to change in this procedure.Thanks in advance. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-26 : 06:22:45
|
| try with exec(@sql) |
 |
|
|
suhasckanade
Starting Member
8 Posts |
Posted - 2008-06-26 : 07:39:39
|
quote:
Originally posted by visakh16
try with exec(@sql)
still it gives same error...Server: Msg 208, Level 16, State 1, Line 1Invalid object name '#tmp1'. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-26 : 08:06:23
|
quote:
Originally posted by suhasckanade
quote:
Originally posted by visakh16
try with exec(@sql)
still it gives same error...Server: Msg 208, Level 16, State 1, Line 1Invalid object name '#tmp1'.
working for me anyways. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-06-26 : 09:13:07
|
the temp table #tmp1 that you created only exists within the scope of the first sp_executesql. It is drop and when the first sp_executesql terminates.use global temp table ##tmp1 instead
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
Temporary Table problem