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 |
|
docS
Starting Member
4 Posts |
 Posted - 2008-06-30 : 15:28:21
|
| Our app creates a temp table by building a string. It works fine interactively but not when executed in TSQL. Can you help?declare @mystr nvarchar(4000), @p_fromclause varchar(100), etc, etcset @mystr = N'select identity(int,1,1), a.* into #mytab from '+@p_fromclause+' a'execute sp_executesql @mystr /* executes without error */print @mystr /* prints string correctly */SELECT * FROM #MYTAB /* Error:Invalid object name '#MYTAB' */Doc |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-30 : 15:30:34
|
| Is your collation Case sensitive? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-30 : 15:31:12
|
| That's the problem with dynamic SQL, it runs in a different session so any local objects such as variables or temp tables are not available in the outer scope. You could use a permanent or global table but you're going to run into concurrency issues if multiple users are trying to access the same object. Why do you need to use dynamic SQL in the first place though? There are lots of ways to avoid it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
docS
Starting Member
4 Posts |
Posted - 2008-06-30 : 16:05:11
|
quote:
Originally posted by tkizer
That's the problem with dynamic SQL, it runs in a different session so any local objects such as variables or temp tables are not available in the outer scope. You could use a permanent or global table but you're going to run into concurrency issues if multiple users are trying to access the same object. Why do you need to use dynamic SQL in the first place though? There are lots of ways to avoid it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
docS
Starting Member
4 Posts |
Posted - 2008-06-30 : 16:09:54
|
| The problem is that the application dynamically calls a stored procedures passing in table names (from_clause), column names, group_by_clause, order_by_clause. I cannot depend on a sort column to use SQL 2005's ROW_NUMBER OVER (). This code should work, it does interactively in TSQL, but fails as a string when trying to select * from #mytab with a Message 208. |
|
|
|
docS
Starting Member
4 Posts |
Posted - 2008-06-30 : 16:33:20
|
| Naw, I fixed it. Have to use the double pound ## is all.Doc |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2008-06-30 : 16:38:59
|
quote:
Originally posted by docS
Naw, I fixed it. Have to use the double pound ## is all.Doc
I hope you know what is that ## is all about FROM BOLquote:
Global temporary tables have two number signs (##) as the first characters of their names........................Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.
ThanksKarunakaran |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-30 : 16:40:33
|
quote:
Originally posted by docS
Naw, I fixed it. Have to use the double pound ## is all.Doc
Do you realize what issues you might run into if multiple users are using the table?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
|
|
Dynamic #TEMP table problem