Debugging Stored procs in vs.net ide

I am debugging a stored proc inside the IDE which is great. However,
the stored proc dumps some data into temp tables. Is there a way to see
the contents of the temp tables while in debug mode?

Thanks
P.S. I posted it earlier in c# ng but no help, thus repost here.

Frans
Tue May 03 04:12:35 CDT 2005

Frank Rizzo wrote:
> I am debugging a stored proc inside the IDE which is great. However,
> the stored proc dumps some data into temp tables. Is there a way to see
> the contents of the temp tables while in debug mode?

Try to use ##temp tables instead of #temp tables so they're visible for
other connections as well. You can then debug the procs in query
analyzer which is much more efficient than in vs.net.

> Thanks
> P.S. I posted it earlier in c# ng but no help, thus repost here.

Heh, a question about debugging a stored procedure, not really a C#
topic, don't you think? ;)

FB

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

Frank
Tue May 03 12:09:19 CDT 2005

Frans Bouma [C# MVP] wrote:
> Frank Rizzo wrote:
>
>> I am debugging a stored proc inside the IDE which is great. However,
>> the stored proc dumps some data into temp tables. Is there a way to
>> see the contents of the temp tables while in debug mode?
>
>
> Try to use ##temp tables instead of #temp tables so they're visible
> for other connections as well. You can then debug the procs in query
> analyzer which is much more efficient than in vs.net.

Say what? You can debug in Query Analyzer? That's something new to me.
By debug I mean, step through the code.

Also, can you or can you not see the contents of the temp tables in
vs.net debugger?

>
>> Thanks
>> P.S. I posted it earlier in c# ng but no help, thus repost here.
>
>
> Heh, a question about debugging a stored procedure, not really a C#
> topic, don't you think? ;)

I live in c# world, so didn't think before posting :)

William
Tue May 03 13:29:09 CDT 2005

A #temp table is owned by the SP that creates it so it will be dropped
automatically once the SP ends. However, there are ways to "see" the data in
a #temp table--they're stored in TempDB with a UserID/owner prefix. If you
get into Query Analyzer and enter this SQL (for your own test table) you can
see this in action.

SELECT TOP 10 Author, Year_Born INTO #TEMP FROM authors

Copies 10 rows into #temp and creates a table named
"dbo.#temp______________________000001B" (or somesuch). The problem is that
it does not seem that any of the tools included in the Visual Studio or SQL
Server suites (and I tried four of them) could open this table. I might try
to write a test application to do so though. You should be able to see the
data (I've done in the past) one way or another. No, the Visual Studio IDE
in 2003 and 2005 won't let you see it.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Frank Rizzo" <none@none.com> wrote in message
news:eDYXTC3TFHA.3840@tk2msftngp13.phx.gbl...
>I am debugging a stored proc inside the IDE which is great. However, the
>stored proc dumps some data into temp tables. Is there a way to see the
>contents of the temp tables while in debug mode?
>
> Thanks
> P.S. I posted it earlier in c# ng but no help, thus repost here.

Frank
Tue May 03 15:58:38 CDT 2005

Ah, sucks. DB Artisan's debugger automatically keeps live temp tables
gened by the stored in separate tabs - so convinient. But the damn
thing is so expensive.

William (Bill) Vaughn wrote:
> A #temp table is owned by the SP that creates it so it will be dropped
> automatically once the SP ends. However, there are ways to "see" the data in
> a #temp table--they're stored in TempDB with a UserID/owner prefix. If you
> get into Query Analyzer and enter this SQL (for your own test table) you can
> see this in action.
>
> SELECT TOP 10 Author, Year_Born INTO #TEMP FROM authors
>
> Copies 10 rows into #temp and creates a table named
> "dbo.#temp______________________000001B" (or somesuch). The problem is that
> it does not seem that any of the tools included in the Visual Studio or SQL
> Server suites (and I tried four of them) could open this table. I might try
> to write a test application to do so though. You should be able to see the
> data (I've done in the past) one way or another. No, the Visual Studio IDE
> in 2003 and 2005 won't let you see it.
>
> hth
>

Frans
Wed May 04 03:30:29 CDT 2005

Frank Rizzo wrote:
> Frans Bouma [C# MVP] wrote:
>> Frank Rizzo wrote:
>>> I am debugging a stored proc inside the IDE which is great. However,
>>> the stored proc dumps some data into temp tables. Is there a way to
>>> see the contents of the temp tables while in debug mode?
>>
>> Try to use ##temp tables instead of #temp tables so they're
>> visible for other connections as well. You can then debug the procs in
>> query analyzer which is much more efficient than in vs.net.
>
> Say what? You can debug in Query Analyzer? That's something new to me.
> By debug I mean, step through the code.

Yes, you can, you can even set breakpoints :)

Just right-click the procedure in the object browser and select
'debug'. You then fill in initial parameters and start at the beginning
of the proc. You can then set a breakpoint, step through the code etc.

When you use ##temp tables, IMHO you can see them from another
connection. This means that you can step through the proc, and when the
temptable is created ,you open another query analyzer and check the table.

> Also, can you or can you not see the contents of the temp tables in
> vs.net debugger?

#temp tables are connection specific, if I'm not mistaken ##temp tables
are accessable by all connections.

FB

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------