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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-10-27 : 07:56:11
|
| VIJAY writes "I have to create temp table by using EXEC statement,But when i executed the follwing step it was not creating temporary table in TEmpDBDECLARE @CreateTable VARCHAR(8000)SELECT @CreateTable ='CREATE TABLE #SOA_TEMP1 (REQUEST_ID uniqueidentifier)'EXEC (@CreateTable)WHY?Thanks,Vijay" |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-10-27 : 08:21:16
|
| Dynamic SQL executes within its own scope, and once the statement completes the temporary objects created by it go out of scope as well and disappear.Try creating your table as a #Temporary table first, instead of a @TableVariable. Your dynamic SQL will be able to reference and manipulate temporary tables created outside of it. |
 |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-10-27 : 08:47:19
|
| He is creating a temp table and not a table var. 'CREATE TABLE #SOA_TEMP1 (REQUEST_ID uniqueidentifier)' Perhaps the temp table also goes out of scope? Try it as a GLOBAL temp table (##SOA_TEMP1) and see if that works. Just remember to explicitly drop it when you are done. I dont agree with creating temp tables with Dynamic SQL. There is usually a way around a problem without using Dynamic SQL.Help us help YOU!Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx*need more coffee*SELECT * FROM Users WHERE CLUE > 0(0 row(s) affected) |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-28 : 05:26:04
|
| What is the need of creating a table dynamically?MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Unable to create temporary table in dynamic SQL