December 29, 2007 at 1:17 am
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.
December 29, 2007 at 8:57 am
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
Change is inevitable... Change for the better is not.
December 29, 2007 at 11:58 am
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
December 31, 2007 at 12:25 am
December 31, 2007 at 1:28 am
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
[font="Verdana"]Markus Bohse[/font]
December 31, 2007 at 3:57 am
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
January 2, 2008 at 1:17 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply