Any way to debug using SQL SP

Hi all,
is there any way to debug stored procedure line by line just like we are
debugging VB.Net on VS?
Thanks
Rena.
VS.NET does have a SQL Debugger in it.
From the VS.NET Help file:
==========================
In Visual Studio, you can debug a stored procedure from Server Explorer or a
source window.
To debug a stored procedure from Server Explorer
1.. Establish a connection to a database using Server Explorer. For more
information, see Server Explorer Window.
2.. Expand the <database name> node.
3.. Expand the Stored Procedures node.
4.. Right-click the stored procedure you want to debug and choose Step
Into Stored Procedure from the shortcut menu.
To debug a stored procedure in the text editor
a.. In a source window, right-click the stored procedure and choose Step
Into Stored Procedure from the shortcut menu.
For more information, see Enabling SQL Debugging on SQL 2000 Desktop
Engines.
Visual Studio also enables you to debug extended stored procedures. However,
you cannot debug SQL statements that are executed outside of a stored
procedure.
Rick Sawtell
MCT, MCSD, MCDBA
|||Rena wrote:
> Hi all,
> is there any way to debug stored procedure line by line just like
> we are debugging VB.Net on VS?
> Thanks
> Rena.
You can debug right from Query Analyzer.
David G.
|||Query Analyzer --> Object Browser --> Right click the SP -
-> Debug
regds,
Shrikant Patil
MCDBA

>--Original Message--
>Hi all,
> is there any way to debug stored procedure line by
line just like we are
>debugging VB.Net on VS?
>Thanks
>Rena.
>
>.
>
|||Thx all for the helps~~~
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:OR$tKoDkEHA.1800@.TK2MSFTNGP15.phx.gbl...
> VS.NET does have a SQL Debugger in it.
> From the VS.NET Help file:
> ==========================
> In Visual Studio, you can debug a stored procedure from Server Explorer or
a
> source window.
> To debug a stored procedure from Server Explorer
> 1.. Establish a connection to a database using Server Explorer. For more
> information, see Server Explorer Window.
> 2.. Expand the <database name> node.
> 3.. Expand the Stored Procedures node.
> 4.. Right-click the stored procedure you want to debug and choose Step
> Into Stored Procedure from the shortcut menu.
> To debug a stored procedure in the text editor
> a.. In a source window, right-click the stored procedure and choose Step
> Into Stored Procedure from the shortcut menu.
> For more information, see Enabling SQL Debugging on SQL 2000 Desktop
> Engines.
> Visual Studio also enables you to debug extended stored procedures.
However,
> you cannot debug SQL statements that are executed outside of a stored
> procedure.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||Can you set a Break Point and only stop at that point, rather than
continually stepping?
If you are trying to trap a bug in the procedure, it is very useful to have
a breakpoint.
We can set one, but it never stops there, except when we are stepping
through line by line.
Cheers
Harry
"Rick Sawtell" wrote:

> VS.NET does have a SQL Debugger in it.
> From the VS.NET Help file:
> ==========================
> In Visual Studio, you can debug a stored procedure from Server Explorer or a
> source window.
> To debug a stored procedure from Server Explorer
> 1.. Establish a connection to a database using Server Explorer. For more
> information, see Server Explorer Window.
> 2.. Expand the <database name> node.
> 3.. Expand the Stored Procedures node.
> 4.. Right-click the stored procedure you want to debug and choose Step
> Into Stored Procedure from the shortcut menu.
> To debug a stored procedure in the text editor
> a.. In a source window, right-click the stored procedure and choose Step
> Into Stored Procedure from the shortcut menu.
> For more information, see Enabling SQL Debugging on SQL 2000 Desktop
> Engines.
> Visual Studio also enables you to debug extended stored procedures. However,
> you cannot debug SQL statements that are executed outside of a stored
> procedure.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||"Shrikant Patil" wrote:

> Query Analyzer --> Object Browser --> Right click the SP -
> -> Debug
>
> regds,
> Shrikant Patil
> MCDBA
>
> line just like we are
>
|||> "Shrikant Patil" wrote:
> Query Analyzer --> Object Browser --> Right click the SP -
> -> Debug
Hi,
I am a newbie to QA debugger. I noticed that this debugger can only be used
with stored procedures. I have created a following stored proc which in turn
calles an user defined function. Basically my intention is to debug the user
defined function.
create proc dbo.rp_test
(@.ordinal smallint)
AS
BEGIN
SELECT * FROM dbo.MyUserDefFunction( 'Carbon')
END
GO
Eventhough I could place break point at the select * statement, the
execution never stops at the break point.
Can anyone tell me what am I missing here? Or is there anyother better way
to debug MyUserDefFunction()?
Thanks for any help,
Kosu

Any way to debug using SQL SP

Hi all,
is there any way to debug stored procedure line by line just like we are
debugging VB.Net on VS'
Thanks
Rena.VS.NET does have a SQL Debugger in it.
From the VS.NET Help file:
==========================
In Visual Studio, you can debug a stored procedure from Server Explorer or a
source window.
To debug a stored procedure from Server Explorer
1.. Establish a connection to a database using Server Explorer. For more
information, see Server Explorer Window.
2.. Expand the <database name> node.
3.. Expand the Stored Procedures node.
4.. Right-click the stored procedure you want to debug and choose Step
Into Stored Procedure from the shortcut menu.
To debug a stored procedure in the text editor
a.. In a source window, right-click the stored procedure and choose Step
Into Stored Procedure from the shortcut menu.
For more information, see Enabling SQL Debugging on SQL 2000 Desktop
Engines.
Visual Studio also enables you to debug extended stored procedures. However,
you cannot debug SQL statements that are executed outside of a stored
procedure.
Rick Sawtell
MCT, MCSD, MCDBA|||Rena wrote:
> Hi all,
> is there any way to debug stored procedure line by line just like
> we are debugging VB.Net on VS'
> Thanks
> Rena.
You can debug right from Query Analyzer.
David G.|||Query Analyzer --> Object Browser --> Right click the SP -
-> Debug
regds,
Shrikant Patil
MCDBA

>--Original Message--
>Hi all,
> is there any way to debug stored procedure line by
line just like we are
>debugging VB.Net on VS'
>Thanks
>Rena.
>
>.
>|||Thx all for the helps~~~
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:OR$tKoDkEHA.1800@.TK2MSFTNGP15.phx.gbl...
> VS.NET does have a SQL Debugger in it.
> From the VS.NET Help file:
> ==========================
> In Visual Studio, you can debug a stored procedure from Server Explorer or
a
> source window.
> To debug a stored procedure from Server Explorer
> 1.. Establish a connection to a database using Server Explorer. For more
> information, see Server Explorer Window.
> 2.. Expand the <database name> node.
> 3.. Expand the Stored Procedures node.
> 4.. Right-click the stored procedure you want to debug and choose Step
> Into Stored Procedure from the shortcut menu.
> To debug a stored procedure in the text editor
> a.. In a source window, right-click the stored procedure and choose Step
> Into Stored Procedure from the shortcut menu.
> For more information, see Enabling SQL Debugging on SQL 2000 Desktop
> Engines.
> Visual Studio also enables you to debug extended stored procedures.
However,
> you cannot debug SQL statements that are executed outside of a stored
> procedure.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Can you set a Break Point and only stop at that point, rather than
continually stepping?
If you are trying to trap a bug in the procedure, it is very useful to have
a breakpoint.
We can set one, but it never stops there, except when we are stepping
through line by line.
Cheers
Harry
"Rick Sawtell" wrote:

> VS.NET does have a SQL Debugger in it.
> From the VS.NET Help file:
> ==========================
> In Visual Studio, you can debug a stored procedure from Server Explorer or
a
> source window.
> To debug a stored procedure from Server Explorer
> 1.. Establish a connection to a database using Server Explorer. For more
> information, see Server Explorer Window.
> 2.. Expand the <database name> node.
> 3.. Expand the Stored Procedures node.
> 4.. Right-click the stored procedure you want to debug and choose Step
> Into Stored Procedure from the shortcut menu.
> To debug a stored procedure in the text editor
> a.. In a source window, right-click the stored procedure and choose Step
> Into Stored Procedure from the shortcut menu.
> For more information, see Enabling SQL Debugging on SQL 2000 Desktop
> Engines.
> Visual Studio also enables you to debug extended stored procedures. Howeve
r,
> you cannot debug SQL statements that are executed outside of a stored
> procedure.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Shrikant Patil" wrote:

> Query Analyzer --> Object Browser --> Right click the SP -
> -> Debug
>
> regds,
> Shrikant Patil
> MCDBA
>
> line just like we are
>|||> "Shrikant Patil" wrote:
> Query Analyzer --> Object Browser --> Right click the SP -
> -> Debug
Hi,
I am a newbie to QA debugger. I noticed that this debugger can only be used
with stored procedures. I have created a following stored proc which in turn
calles an user defined function. Basically my intention is to debug the user
defined function.
create proc dbo.rp_test
(@.ordinal smallint)
AS
BEGIN
SELECT * FROM dbo.MyUserDefFunction( 'Carbon')
END
GO
Eventhough I could place break point at the select * statement, the
execution never stops at the break point.
Can anyone tell me what am I missing here? Or is there anyother better way
to debug MyUserDefFunction()?
Thanks for any help,
Kosu

Any way to debug using SQL SP

Hi all,
is there any way to debug stored procedure line by line just like we are
debugging VB.Net on VS'
Thanks
Rena.VS.NET does have a SQL Debugger in it.
From the VS.NET Help file:
==========================In Visual Studio, you can debug a stored procedure from Server Explorer or a
source window.
To debug a stored procedure from Server Explorer
1.. Establish a connection to a database using Server Explorer. For more
information, see Server Explorer Window.
2.. Expand the <database name> node.
3.. Expand the Stored Procedures node.
4.. Right-click the stored procedure you want to debug and choose Step
Into Stored Procedure from the shortcut menu.
To debug a stored procedure in the text editor
a.. In a source window, right-click the stored procedure and choose Step
Into Stored Procedure from the shortcut menu.
For more information, see Enabling SQL Debugging on SQL 2000 Desktop
Engines.
Visual Studio also enables you to debug extended stored procedures. However,
you cannot debug SQL statements that are executed outside of a stored
procedure.
Rick Sawtell
MCT, MCSD, MCDBA|||Rena wrote:
> Hi all,
> is there any way to debug stored procedure line by line just like
> we are debugging VB.Net on VS'
> Thanks
> Rena.
You can debug right from Query Analyzer.
--
David G.|||Query Analyzer --> Object Browser --> Right click the SP -
-> Debug
regds,
Shrikant Patil
MCDBA
>--Original Message--
>Hi all,
> is there any way to debug stored procedure line by
line just like we are
>debugging VB.Net on VS'
>Thanks
>Rena.
>
>.
>|||Thx all for the helps~~~ :)
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:OR$tKoDkEHA.1800@.TK2MSFTNGP15.phx.gbl...
> VS.NET does have a SQL Debugger in it.
> From the VS.NET Help file:
> ==========================> In Visual Studio, you can debug a stored procedure from Server Explorer or
a
> source window.
> To debug a stored procedure from Server Explorer
> 1.. Establish a connection to a database using Server Explorer. For more
> information, see Server Explorer Window.
> 2.. Expand the <database name> node.
> 3.. Expand the Stored Procedures node.
> 4.. Right-click the stored procedure you want to debug and choose Step
> Into Stored Procedure from the shortcut menu.
> To debug a stored procedure in the text editor
> a.. In a source window, right-click the stored procedure and choose Step
> Into Stored Procedure from the shortcut menu.
> For more information, see Enabling SQL Debugging on SQL 2000 Desktop
> Engines.
> Visual Studio also enables you to debug extended stored procedures.
However,
> you cannot debug SQL statements that are executed outside of a stored
> procedure.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Can you set a Break Point and only stop at that point, rather than
continually stepping?
If you are trying to trap a bug in the procedure, it is very useful to have
a breakpoint.
We can set one, but it never stops there, except when we are stepping
through line by line.
Cheers
Harry
"Rick Sawtell" wrote:
> VS.NET does have a SQL Debugger in it.
> From the VS.NET Help file:
> ==========================> In Visual Studio, you can debug a stored procedure from Server Explorer or a
> source window.
> To debug a stored procedure from Server Explorer
> 1.. Establish a connection to a database using Server Explorer. For more
> information, see Server Explorer Window.
> 2.. Expand the <database name> node.
> 3.. Expand the Stored Procedures node.
> 4.. Right-click the stored procedure you want to debug and choose Step
> Into Stored Procedure from the shortcut menu.
> To debug a stored procedure in the text editor
> a.. In a source window, right-click the stored procedure and choose Step
> Into Stored Procedure from the shortcut menu.
> For more information, see Enabling SQL Debugging on SQL 2000 Desktop
> Engines.
> Visual Studio also enables you to debug extended stored procedures. However,
> you cannot debug SQL statements that are executed outside of a stored
> procedure.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Shrikant Patil" wrote:
> Query Analyzer --> Object Browser --> Right click the SP -
> -> Debug
>
> regds,
> Shrikant Patil
> MCDBA
>
> >--Original Message--
> >Hi all,
> > is there any way to debug stored procedure line by
> line just like we are
> >debugging VB.Net on VS'
> >
> >Thanks
> >Rena.
> >
> >
> >.
> >
>|||> "Shrikant Patil" wrote:
> Query Analyzer --> Object Browser --> Right click the SP -
> -> Debug
Hi,
I am a newbie to QA debugger. I noticed that this debugger can only be used
with stored procedures. I have created a following stored proc which in turn
calles an user defined function. Basically my intention is to debug the user
defined function.
create proc dbo.rp_test
(@.ordinal smallint)
AS
BEGIN
SELECT * FROM dbo.MyUserDefFunction( 'Carbon')
END
GO
Eventhough I could place break point at the select * statement, the
execution never stops at the break point.
Can anyone tell me what am I missing here? Or is there anyother better way
to debug MyUserDefFunction()?
Thanks for any help,
Kosu

any way to debug stored procedures without buying visual studio?

Hi.

I am under the understanding that having the sql server 2005 db is not
enough (like 2000 was) to debug stored procedures.. that I need to
purchase (costly) visual studio 2005.

Can someone suggest a free or lower cost alternative?

sorry to be so cheap.. its the times I think.Jeff Kish (kishjjrjj@.charter.net) writes:

Quote:

Originally Posted by

I am under the understanding that having the sql server 2005 db is not
enough (like 2000 was) to debug stored procedures.. that I need to
purchase (costly) visual studio 2005.
>
Can someone suggest a free or lower cost alternative?
>
sorry to be so cheap.. its the times I think.

PRINT and SELECT is what I use, even in SQL 2000. I used to use the
T-SQL debugger occasionally, but I gave up on it. There were so many stars
that had to be aligned for it to work.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 2, 3:18 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Jeff Kish (kishjj...@.charter.net) writes:

Quote:

Originally Posted by

I am under the understanding that having the sql server 2005 db is not
enough (like 2000 was) to debug stored procedures.. that I need to
purchase (costly) visual studio 2005.

>

Quote:

Originally Posted by

Can someone suggest a free or lower cost alternative?

>

Quote:

Originally Posted by

sorry to be so cheap.. its the times I think.

>
PRINT and SELECT is what I use, even in SQL 2000. I used to use the
T-SQL debugger occasionally, but I gave up on it. There were so many stars
that had to be aligned for it to work.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

~
T-Sql debugger is some what okay, I used very long back.
I'm very comfortable with Print and Select commands to debug a stored
procedure.

~NR|||On Fri, 2 Feb 2007 10:18:24 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Jeff Kish (kishjjrjj@.charter.net) writes:

Quote:

Originally Posted by

>I am under the understanding that having the sql server 2005 db is not
>enough (like 2000 was) to debug stored procedures.. that I need to
>purchase (costly) visual studio 2005.
>>
>Can someone suggest a free or lower cost alternative?
>>
>sorry to be so cheap.. its the times I think.

>
>PRINT and SELECT is what I use, even in SQL 2000. I used to use the
>T-SQL debugger occasionally, but I gave up on it. There were so many stars
>that had to be aligned for it to work.

Thanks. I'll give it a try.
The debugger had been fairly nice to use when I had triggers and sp's
to debug.

I assume that it is a simple matter to invoke the procedure (I know
how to compile it) to get things to run?

Regards,
Jeff|||Jeff Kish (kishjjrjj@.charter.net) writes:

Quote:

Originally Posted by

The debugger had been fairly nice to use when I had triggers and sp's
to debug.
>
I assume that it is a simple matter to invoke the procedure (I know
how to compile it) to get things to run?

Not sure that I understand the question, just run it from Query Analyzer.

Triggers are a good point for the debugger. If you are not aware of that
there is a trigger, the debugger can make you aware, if you stick to F11.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Any Problems with Large Packages?

I've been suffering with a variety of symptoms lately, and I don't know why:

Can only debug once in BIDS. After that, it can never attach to DtsDebugHost.exe. DtsDebugHost.exe never receives any CPU time, and never grows past its initial memory allocation.

Save takes a long time with 100% CPU usage. This can be as simple as openning a SQL Server Destination component to correct the metadata due to an added column in the underlying table, then saving. It takes several minutes at 100% CPU.

I've had several Visual Studio crashes today (DW has reported these)

I've read here about people with 10MB or 17MB packages, so I feel badly complaining about my 3.2MB package! However, something is going on, and I'd like to know what it is, so I can debug again.

Thanks for any help.

Rebooting can sometimes work wonders

What SP level are you running? I highly recommend SP2.

As a "good" practice, I try to keep packages small, for a variety of reasons. Smaller packages are easier to debug, seem to be more stable in BIDS, and are easier to manage in a team environment with multiple developers. My packages normally are under 1mb, and most often are 300-700kb.

|||

I'm running SP2.

Once I get it out to QA, I may look into breaking it up into smaller packages. I'm sure the size is due to the 17 outputs from the XML Source all being processed in the same data flow task. At the cost of reading the XML more than once, and of creating subset schemas (so I don't get hundreds of column not used warnings), I can break some pieces off and execute them in parallel.There are performance reasons as well, as there are buffers made wide by large string and NTEXT fields.

In the meantime, I'm wondering about how to avoid that reboot issue...

|||

A couple of other suggestion:

Try setting Work Offline (under the SSIS menu) to true before saving, and see if that makes a difference (it shouldn't, but I've seen stranger things).

How complex are the transformations in the data flow? You might want to use one package to read the file once, and save all the outputs to RAW files. Then have other packages that pick up the RAW files to do the rest of the processing. RAW files are extremely fast, so they don't introduce as much overhead as you might expect. On the other hand, if the transformations are simple, you won't get a lot of benefit from that approach.

|||

Another wonderful behavior is that data viewers aren't working reliably. I've just given up on one pair: one each on the default output and on the error output of a derived column transformation. The final step in that execution path writes to a SQL Server Destination, and indeed, the rows get there. But they don't stop at either data viewer, and there's no path to the destination that does not go through the derived column transformation.