| Author |
Topic |
|
toddmo
Starting Member
5 Posts |
 Posted - 2010-01-09 : 13:14:40
|
| I'm in Sql Server Management Studio Microsoft SQL Server Management Studio 10.0.1600.22 ((SQL_PreRelease).080709-1414 )I am debugging a stored procedure,which creates a local temp table.All I want to do is see what's in that table.How do I do that?I know I can alter the code to do a select,but I'm looking for a solution that doesnot require altering the stored procedure.I just want to inspect. Thanks.Please, I ask you try any suggestions on yourself first and make sure it works,before posting your solution.Thank you! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
toddmo
Starting Member
5 Posts |
Posted - 2010-01-09 : 15:09:40
|
| Tara,MVP sounds pretty knowledgeable.So does SQL goddess.Did you happen to know of a way to do this?It seems like a pretty common task, right?Thanks. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-01-09 : 18:08:12
|
| Well I didn't respond with an answer since you only wanted tested solutions. I know you can query it directly in tempdb, but I don't recall how you find the object name.What I do in a situation like this is copy the stored procedure into a new stored procedure and then add my debug code there. I then run this new stored procedure in SSMS and don't need to worry about bothering application users.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
toddmo
Starting Member
5 Posts |
Posted - 2010-01-10 : 19:13:52
|
| It looks like it shows the full object name in the object explorer,under the tempdb tables / temp tables node.But I got an SSMS error "Failed to retrieve data for this request"when I try to "Select top 1000 rows" from any table in this folder.I get that error whether debugging or not. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2010-01-11 : 19:42:47
|
I know of no way to select the rows from a local temp table outside of the session in which the temp table was created.If you try the following in an SSMS query window:create table #tmp (col1 int);select object_id('tempdb..#tmp')you will get a non-null object id. Now if you open another query window (another session) and issue the same select command, you will get null. It is as though the temp table is not visible to other sessions even though you can see it in the list of tables in tempdb and in a query on sysobjects in tempdb. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
toddmo
Starting Member
5 Posts |
Posted - 2010-01-12 : 11:07:14
|
| Well,in summary,whether or not there is a clever workaround,I think it would be worth it to pass onto the Microsoft Sql Server teamthe suggestion tomake viewing session-only objects and changesa straightforward and intuitive task while debugging.This would include local temp tables but also uncommitted changes made by the debugging session, etc.They've come a long way since 2000 Enterprise Manager,so I know they wish us well. :) |
|
|
|
nickmich
Starting Member
1 Post |
Posted - 2011-09-12 : 06:24:55
|
| Hi!I see the topic is quite old but other might also find it.I found the solution. It is inhttps://github.com/FilipDeVos/sp_selectThe procedures are sp_select.sql and sp_selectpages.sql .I didn't write them but found them extremely useful while debugging unknown scripts with loads of temporary tables. |
|
|
|
|
Viewing contents of # temp tables while debugging
Tara Kizer