Debugging Stored Procedure in SQL Server 2008

Question

Is there any way to debug a stored procedure on SQL Server 2008?

I have access to use SQL Server Management Studio 2008 and Visual Studio 2008 (not sure whether either provides this functionality).

Generally I use the SQL profiler to find the parameters passed to the stored proc, however would like to be able to step through the procedure to see where it is failing.

Is this possible?

What is the best way? (in terms of quickly finding location of bugs)

Answer 1

• Yes, although it can be tricky to get debugging working, especially if trying to debug SQL on a remote SQL server from your own development machine.
• In the first instance I'd recommend getting this working by debugging directly on the server first, if possible.
• Log to the SQL server using an account that has sysadmin rights, or ask your DBA to to do this.
• Then, for your own Windows account, create a 'login' in SQL Server, if it isn't already there:

• Right-click the account > properties - ensure that the login is a member of the 'sysadmin' role:

• (also ensure that the account is 'owner' of any databases that you want to debug scripts (e.g. stored procs) for:

• Then, login directly onto the SQL server using your Windows account.
• Login to SQL server using Windows Authentication (using the account you've just used to log into the server)
• Now 'Debug' the query in SQL management studio, setting breakpoints as necessary. You can step into stored procs using F11:

• Here's a useful guide to debugging:

http://blogs.msdn.com/b/billramo/archive/2009/04/11/transact-sql-debugger-for-sql-server-2008-part-1.aspx

• If you need to remotely debug, then once you've got this part working, you can try setting up remote debugging:

http://blogs.msdn.com/b/billramo/archive/2009/04/11/transact-sql-debugger-for-sql-server-2008-part-2.aspx

Answer 2

Yes you can (provided you have at least the professional version of visual studio), although it requires a little setting up once you've done this it's not much different from debugging code. MSDN has a basic walkthrough.

Answer 3

One requirement for remote debugging is that the windows account used to run SSMS be part of the sysadmin role. See this MSDN link: http://msdn.microsoft.com/en-us/library/cc646024%28v=sql.105%29.aspx

Answer 4

Well the answer was sitting right in front of me the whole time.

In SQL Server Management Studio 2008 there is a Debug button in the toolbar. Set a break point in a query window to step through.

I dismissed this functionality at the beginning because I didn't think of stepping INTO the stored procedure, which you can do with ease.

SSMS basically does what FinnNK mentioned with the MSDN walkthrough but automatically.

So easy! Thanks for your help FinnNK.

Edit: I should add a step in there to find the stored procedure call with parameters I used SQL Profiler on my database.

Answer 5

MSDN has provided easy way to debug the stored procedure. Please check this link-
How to: Debug Stored Procedures