July 31, 2008 at 9:32 am
Hi,
I'm trying to debug a stored proc that creates temp tables. In 2000 Query Analyzer, I could remark out the drop statement & compile, and then, query the temp tables created by running the stored proc in the same window.
Now, it seems that the temp tables are dropped as soon as the sp is finished even without the drop statement. Is there a way to make my temp tables persistent within the session after running my sp?
Thanks
July 31, 2008 at 9:56 am
instead of #temptablename, use ##temptablename. this changes it from a local temp table to a global temp table. you should be able to see it then
July 31, 2008 at 10:01 am
I thought about doing that, but I was hoping maybe there was a switch in Management Studio that set it back to the way it worked in 2000 Query Analyzer.
So if there's not an option for that I will set it to global, but I really don't want to have to do that every time I have to debug a stored proc.
Thanks.
July 31, 2008 at 10:02 am
Temp tables are dropped when the connection closes or when the stored procedure that created them ends. You can create global tables, but that's a little messy and if more than one instance of the proc can run at the same time, gives you big problems.
Maybe explain a bit more what you're trying to do? There may be an alternative...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2008 at 10:09 am
it's uber-easy for debugging purposes:
1. CTRL+H (replace) find #TempTable, replace with ##TempTable
2. debug
3. repeat step 1 with find and replace reversed
you can also comment out from:
CREATE PROCEDURE
to:
AS
and debug the code in place without having to ALTER the proc until it's completely debugged.
but as gila mentioned, there's probably a better way to accomplish what you're after. this is just one.
July 31, 2008 at 10:13 am
Hey Gail,
What you are saying is exactly what is happening. When my stored proc ends, it deletes the temp tables even when I remark out the DROP statement.
What I am trying to do is run the stored proc that creates and loads the temp tables and then be able to run select statements and so forth to see what's being populated in each table. I'm going to have to add more data to what the sp is currently returning, and in order to do that, I really need to see what's in there now.
As Lenny suggested, I could just comment out the CREATE AS or do a quick replace on the temp tables and make them global. But I could have sworn that when I would comment out the DROP statements and ran a stored proc in 2000 Query Analyzer, that my temp tables would still exist for querying. Is that not correct?
Anyway, I appreciate all of your suggestions. And if I have to resort to temporarily setting them global I can do that.
July 31, 2008 at 10:18 am
actually, i meant do BOTH the replace and the commenting out for your debugging.
and yes, you'll have to explicity DROP the global temp tables because they'll survive the session.
July 31, 2008 at 10:23 am
lee.vance (7/31/2008)
Hey Gail,What you are saying is exactly what is happening. When my stored proc ends, it deletes the temp tables even when I remark out the DROP statement.
Because the temp table is now out of scope, and automatically dropped.
As Lenny suggested, I could just comment out the CREATE AS or do a quick replace on the temp tables and make them global. But I could have sworn that when I would comment out the DROP statements and ran a stored proc in 2000 Query Analyzer, that my temp tables would still exist for querying. Is that not correct?
As far as I know, the scoping rules haven't changed between 2000 and 2005.
Generally, I do what Lenny suggested. Create the tables outside of any stored proc
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2008 at 12:27 pm
I inherited several stored procedures that do step-wise processing using temp tables. In other words, there are multiple queries that insert/update/delete data in one or more temp tables within the stored procedure (not my design, but I'm constrained to using them).
To debug these stored procs, I add select statements (from the temp tables) in between the other processing queries so I get a snap-shot at each 'step' of what the data looks like.
When I then run these stored procedures, I have each snap-shot in the output window and I can then analyze how the data is changing from step to step by simply scrolling thru the output.
If it was easy, everybody would be doing it!;)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply