Debugging Stored Procedure

[stevensteven]

Is it possible to debug Stored procedures. If so, can you also do it from SQL Query Analyzer.

 

[mwolf00]

In query analyzer, I place SELECT statements in my code if I'm trying to make sure that SP is working as intended.

For instance, if I'm loading a temp table with some data and manipulating it, I could check the manipulation by adding

SELECT * FROM #temp

and then removign it when developing is completed.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[checkai]

I use that, also PRINT statements if i want to show the value of a variable...

dlc

 

[TysonLPrice]

From SQL Analyzer open the object browser, open your database, click on stored procedures, right click on the one you want, and finally click debug. Fill in and parameters you may have defined and click execute. It will open up a debugging session where you can step through the code. You will have to play with it to get comfortable with it.

 

[stevensteven]

Thanks TysonLPrice. But I have having problems debugging on my computer connected to the server through Query Analyzer.

Is there any reason why SQL Analyzer only allows me to debug on the host computer?

 

[SQLSister]

I personally never use the debugger as it does not seem to me to work very well. Here's my take on debugging.

Debugging Complex Stored Procedures. faq183-4314

 

[TysonLPrice]

Thanks TysonLPrice. But I have having problems debugging on my computer connected to the server through Query Analyzer.

Is there any reason why SQL Analyzer only allows me to debug on the host computer?

I'm not sure what you mean by "having problems". Sometimes I need to go to settings, administrative tools, services, right click on MSSQLServer, properties, and logon as administrator. I don't know if it's an option for you or if that is the problem you are having.

As far as:

I personally never use the debugger as it does not seem to me to work very well. Here's my take on debugging.

I'm not sure what you mean by it not working well. It steps through one line of code at a time and displays the values declared as they change.

I have switched to copying the SP into query analyzer and using print and the other techniques in the FAQ you posted though. Mainly because you can change the code right there versus the debugger where you can't.

 

[rcampbellwhite]

Guys, by far the easiest method of debugging stored procedures is to spend the time getting the debugger working for you. Yes, it's sometimes frustrating to get it up and running, but with a bit of patience you will be richly rewarded because *nothing* beats being able to step through your proc, setting breakpoints, and checking variable values on the fly.

If debugging isn't working on your server, here's a good MSDN article to start you off.

You can also find a lot of info about this topic by searching for "Debugging stored procedures" in the newsgroup microsoft.public.dotnet.framework.adonet which is accessible through Google Groups.

I found this afternoon that there were two problems that were preventing my server from letting me do interactive debugging:

Firstly, using dcomcnfg (start...run...dcomcnfg), select "SQL Debugger Registry2" in the applications tab. Click properties, select the Identity tab, and then select "The Interactive User" as the account to run this application. Click ok, and click ok again to save this setting. Restart the SQL Server service.

At the very least this will provide you with a more meaningful error message if the debugger still won't start. Check the articles above for keywords from your error message and follow the advice. It will work eventually!

The second and crucial problem that I found was that my server had been renamed at some point. This is a known problem with the debugger and is covered in MSDN knowledge base article 317241.

To quickly check if your server has been renamed, type SELECT @@SERVERNAME into Query Analyzer. If you don't get the server name you expect then you may have a renamed server on your hands. See the article above for further instructions.

I hope this helps.

I also hope that Microsoft make SQL debugging an altogether easier experience in Yukon!

Rob C-W

 

[SQLSister]

Rob that's good information. Do you mind if I add it it to the FAQ on debugging?

But TysonLPrice said:
"I have switched to copying the SP into query analyzer and using print and the other techniques in the FAQ you posted though. Mainly because you can change the code right there versus the debugger where you can't."

And that is exactly the reason I hate to use the debugger. Every other debugger I've ever used has let me make changes while I step through it. Makes me so crazy, that I can't stand to use it.

 

[rcampbellwhite]

Ultimately the best technique for debugging a stored proc will involve a combination of any number of methods.

Copying the code and using Print statements or Select statements to interrogate your code is as effective and valid a method as using the debugger as long as it gets your proc working! Having said that, I think it's fair to conclude that the debugger is by far the quickest method of finding exactly where and why your code is branching where it is.

Rob C-W

Ps: No problem with adding this info to the FAQ

 

[SQLSister]

Added, Rob. I just quoted your post, but let me know if there is something else you want in it and I will be glad to oblige. The idea is to give people as many tools as possible to help themselves. I don't care for the debugger, but if someone like you who uses it wants to add a section to the FAQ on using it, I'm all in favor. My profile has mu email if you's rather send me text to include that way.