SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Dynamic SQL with Temp Table (HOW?)
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

rpc86
Posting Yak Master

200 Posts

Posted - 08/24/2004 :  08:19:46  Show Profile
Hi guys,

How can I solve this problem? I need to write a Dynamic SQL with Temp Table but when I try to execute the command, it gives me an "Invalid Object". Please help me.


Declare @SQL char(2000)
Set @SQL='Select top 10 * into #report from myTable'
exec (@sql)

Select * from #report

(10 row(s) affected)

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#report'.





Thanks.

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 08/24/2004 :  08:27:25  Show Profile  Visit spirit1's Homepage
you need to use global temp table -> ##temp

Declare @SQL char(2000)
Set @SQL='Select top 10 * into ##report from myTable'
exec (@sql)

Select * from ##report


Go with the flow & have fun! Else fight the flow :)
Go to Top of Page

rpc86
Posting Yak Master

200 Posts

Posted - 08/24/2004 :  08:30:00  Show Profile
Thanks,

I have tried this before I posted my question, but it gave me the same error message.
Go to Top of Page

rpc86
Posting Yak Master

200 Posts

Posted - 08/24/2004 :  08:31:25  Show Profile
Sorry,

I tried only single # but when I tried ## it worked.

Many Thanks !!!
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 08/24/2004 :  21:23:55  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
uh, you're not going to have multiple users using this same procedure at the same time are you?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000