SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to debug T-SQL


How to debug T-SQL

Author
Message
goodguy
goodguy
SSC-Addicted
SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)

Group: General Forum Members
Points: 432 Visits: 1037
I still count myself as a Newbie even though I have moderately good skills in database design and administration. I am an average application developer in VB6 who is also expected to have some mastery over SQL Server 2000.

I am grateful to have learnt more advanced T-SQL on these very forums. However, I still haven't figured out how to "debug" T-SQL, e.g. stored procedures, functions, etc. Debugging in VB6 is so straightforward and cool, once you get the hang of it. I would like a step-by-step guide to how I can start debugging in SQL also, if any one can spare the effort.

Hand-in-hand with debugging, I would also like to know how to "trap" errors in T-SQL and issue SQL error messages in my VB6 applications.

Any help will be greatly appreciated.


Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86754 Visits: 41103
Both are huge, very broad subjects....

There are a large number of articles on debugging SQL... Google for "HOW TO DEBUG SQL" and you'll get some great articles including how to setup the debugging tool in SQL Server 2000. There's also a bit of information in Books Online especially concering the debugger. Look for "Debug" in the Index of Books Online.

Same thing goes for error handling... lots of articles in Google and a good "starter" in Books Online... look in the Index for "errors-SQL Server" and drill down to the "Transact-SQL" entry or just find the main entry for @@Error in the Index.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Phil Factor
Phil Factor
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2050 Visits: 2971
Yes, this is really too broad a subject for a forum, but here are a few thoughts....

I'm an old fogey who finds the many development and debugging aids for SQL Server wonderful but rather bewildering. They never quite give the information I want, at the right time, in the detail I want. Whereas, in certain circumstances, I'm a sucker for 'Visual-xxxxxx' IDEs, it is dangerous to rely on them for developing database applications. Gimme a nice plain Query Analyser!

Whenever I cut code, my uppermost thought is 'How can I test this to make sure it always does the right thing in every circumstance, How do I find out if and when it fails, or when it causes another component to fail?.'

When I'm developing database applications, I like to design them from the start to make them easy to debug. This often means putting in the modules that are required for the 'production' monitoring and maintenance of the system. It takes a lot more effort with very little to show for it, but I've never been tempted to leave all this for later on in the project and use the IDE approach with SQL Server. With a dynamic multi-user, multiprocess like this, the ability to fiddle about looking at variables is less important, when you're dealing with much broader factors such as indexing, locking, blocking, referential constraints, triggers, and so on. SQL Server has a number of ways of clever ways of alerting you to problems, if you want them.

I run all systems, even production ones, with a number of simple logs that will give me exactly the statistics I need on the running of the various parts of the system and flag up hotspots and likely performance issues. For example, I include the recording of the calling and exit of every stored procedure, along with the parameters, the spid that called it, time, user etc. This allows me to re-run serendipitous combinations of database procedures that cause problems.

I like to develop every routine in a test harness with checks for all sorts of errors, and the ability to rerun a series of events to investigate fully when things go wrong. Every stored procedure has a regression-test suite that allows me to rapidly alter a procedure and check that it still does what it is supposed to. I suppose I design stuff with the view, based on past experience, that things are going to go wrong, even in the most unexpected places, and it is very likely to be your own error that caused it!

When developing a routine, along with the obvious help that things like query execution plans give, I still use the old technique of 'Print-effing'. One can't effectively 'print-eff' to a console. I create a log in tempdb and write what ever I want to the log. By 'print-effing' I mean inserting records, at various points in the routine, into a log table to show things like the state of variables, table variables, or whatever. I have a ready-made template for creating the log, so it isn't a great trouble to do. You can then inspect the log to see what happens when you run your regression test on the routine. It never fails to astonish me how many bugs get trapped by using this sort of approach


Best wishes,

Phil Factor
Simple Talk
goodguy
goodguy
SSC-Addicted
SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)

Group: General Forum Members
Points: 432 Visits: 1037
Phew! What a big world I have dared step into! Thanks to both of you, Jeff and Phil, I'll remember your advice.


Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

MarkusB
MarkusB
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5847 Visits: 4208
As Jeff already wrote there are loads of articles around and with a little help from google you should be able to find something. Anyway here's an article I found recently which explains the debugging options: http://aspnet.4guysfromrolla.com/articles/051607-1.aspx

Markus Bohse
karthik M
karthik M
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2953 Visits: 2584
Visit the following URL's.

msdn2.microsoft.com/en-us/library/zefbf0t6(VS.80).aspx
msdn2.microsoft.com/en-us/library/zefbf0t6(VS.71).aspx
searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1197403,00.html

You will get some idea.

karthik
goodguy
goodguy
SSC-Addicted
SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)

Group: General Forum Members
Points: 432 Visits: 1037
Thanks, guys. You've all been a great help.


Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search