Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to debug T-SQL Expand / Collapse
Author
Message
Posted Saturday, December 29, 2007 1:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 1, 2012 3:30 PM
Points: 292, Visits: 1,028
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

Post #437310
Posted Saturday, December 29, 2007 8:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:16 PM
Points: 37,102, Visits: 31,655
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #437334
Posted Saturday, December 29, 2007 11:58 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:36 AM
Points: 587, Visits: 2,530
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
Post #437344
Posted Monday, December 31, 2007 12:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 1, 2012 3:30 PM
Points: 292, Visits: 1,028
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

Post #437447
Posted Monday, December 31, 2007 1:28 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:08 AM
Points: 4,432, Visits: 4,171
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
Post #437453
Posted Monday, December 31, 2007 3:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
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
Post #437502
Posted Wednesday, January 2, 2008 1:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 1, 2012 3:30 PM
Points: 292, Visits: 1,028
Thanks, guys. You've all been a great help.


Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Post #437853
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse