[RESOLVED] Debugging w/ SQL server

[wild_bill]

Is there a way to step through a stored procedure, in a similar way to how you do with .NET source? Small procs aren't bad, but sometimes troubleshooting/debugging is a nightmare on larger procs.

[techgnome]

Nope... not really .... we usually end up using SQL Profiler and watch the DB as the SP executes.... we can then see the calls, and the entire execution of the sproc.

-tg

[FunkyDexter]

I do the same as TG. Use the TSQL_SPs template for the best results. The only frustrating thing is you don't get to interrogate the values that are being assigned to variables during processing but you do get to see the parameter values that get passed into any sproc/function calls and you can at least follow the path it takes through the sproc.

[alex_read]

Erm, yes there is guys.

I am assuming here that you are on about 2005/2008 SQL Server. 2000 has this also, but it's performed within the Query Editor application.

In Visual Studio you can bring up the Data Connections I think it is window. You can add/find your SQL server instance there and expand and navigate the neccessary containers until you find your stored proc. When you right click a stored proc, you get an additional popup menu option of debug, or Edit which will provide this functionality. You get a debugger session in which you can specify test values, have full breakpoint and execution navigation (step into etc.) support (Edit: after re-read of the above - and the locals window to keep tabs on variable assignments) similar to the way you have when debugging .Net code.

If you haven't got a full copy of .Net available, I believe you can use the BIDS (business intelligence development environment) which ships with SQL server and still have this. Debugging, Integration Services and Reporting Services support/interaction was why this tool was shipped with SQL Server 2005/2008.

I'm sorry some of the names of the environments and/or menus & windows may be different to those mentioned above, but I haven't got a local install here and this is from memory. Please post if any of the above names require altering and I'll edit the post for others to see.

[alex_read]

SQL Profiler is a fantastic tool also, however I tend to find myself using this just really for when evaluating any fully produced SQL Statements which have been construced and completed (having the parameters inserted and user entered values all inserted etc.) from .Net and are being passed to SQL Server. It's a Godsend to be able to see the whole statement produced as it's fired to SQL Server to check this for anomolies - great little tool that is!

[wild_bill]

You are the bomb, can't wait to try this out tomorrow.