469,110 Members | 1,986 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,110 developers. It's quick & easy.

debugging stored procedures


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
Jul 23 '05 #1
4 1357
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)
Jul 23 '05 #2
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
Jul 23 '05 #3
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

Jul 23 '05 #4
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
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by srobinson | last post: by
2 posts views Thread by db2sysc | last post: by
2 posts views Thread by Tina | last post: by
2 posts views Thread by rodchar | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.