debugging stored procedures

[Little PussyCat]

Hello,

I am doing a lot of work with stored procedures at work now and am wondering
if there is a way that I can step through the code line by line and set
breakpoints on it like I do in VB/VBA to test variables/parameters.

Regards,

Jayne

[Hugo Kornelis]

On Tue, 08 Mar 2005 21:38:46 +0000, Little PussyCat wrote:

I am doing a lot of work with stored procedures at work now and am wondering
if there is a way that I can step through the code line by line and set
breakpoints on it like I do in VB/VBA to test variables/parameters.

Hi Jayne,

In Query Analyzer, hit F8 to bring up the object browser. Find the
stored procedure, right-click it and select "Debug".

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:

On Tue, 08 Mar 2005 21:38:46 +0000, Little PussyCat wrote:

I am doing a lot of work with stored procedures at work now and am
wondering if there is a way that I can step through the code line by
line and set breakpoints on it like I do in VB/VBA to test
variables/parameters.

In Query Analyzer, hit F8 to bring up the object browser. Find the
stored procedure, right-click it and select "Debug".

That's the theory.

In practice it appears that there is always something that prevents it
from working. To start with SQL Server must be running from a domain
account, and not local server. If you have Windows XP SP2 on the client,
you need at least 8.00.944 or the beta of SQL 2000 SP4, and you must
apply it on server and client. On top of that you must open port 135
in Windows firewall for the SQL box. (Do NOT open this port generally.)

And when all is done, you sysadm may get the idea that the Windows users
under which SQL Server runs is not permitted access to the workstations.
This how the last attempt ended in our shop.

I should add if you run SQL Server on your own machine, debugging
usually works.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Miss Livvy]

You can also step into T-SQL stored procs from the VB or .NET development
environment. But personally, I have not found stepping into stored procs to
be all that useful since I could not see a way to examine the result sets of
queries. I have found using SQL profiler to be extremely useful for seeing
what parameters the calling app is passing in and which statements are
getting executed within the procedure. However, I think you need SA rights
to run profiler. To examine the result sets of queries, I usually just run
the guts of the procedure in Query Analyzer and use select statements to
dump the intermediate results. .
"Little PussyCat" <SP******@NOSPAM.com> wrote in message
news:cc************@tiger.sphynx...

Hello,

I am doing a lot of work with stored procedures at work now and am wondering if there is a way that I can step through the code line by line and set
breakpoints on it like I do in VB/VBA to test variables/parameters.

Regards,

Jayne

[Erland Sommarskog]

Miss Livvy (Xe*******************@yahoo.com) writes:

You can also step into T-SQL stored procs from the VB or .NET
development environment. But personally, I have not found stepping into
stored procs to be all that useful since I could not see a way to
examine the result sets of queries.

Yeah, I agree. Occassionally if a I have procedure with lot of procedural
logic, single-stepping through it can be helpful. The same is true if
want to look at the values of some variables.

But often I find too much hassle to start the debugger, so I rather
modify the procedure with some debug SELECT:s in strategic places.

Also, if your procedure raises an error and you want to debug that
happens after the error, the debugger does not appear to be very
co-operative.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp