Yahoo Search


Search the web Search this site

Live Search

Google Search

Custom Search

Translate this Web Site to

Tuesday, May 01, 2007

Debugging Stored Procedures

I know that all of you might have tried debugging Stored Procedures (SPs). But I know there are also people who are searching how to do this so I thought I needs to put an article on how to do this.

Please note that to dubug SPs you need to use an account which is a member of sysadmin role.

1.) SQL Server 2005
When you install SQL Server 2005 it will install SQL Server Business Intelliegnce Development Studio. You can use this or Visual Studio to debug SPs.

Start any prefered application from the above two and go to View menu and click on Server Explorer (or press Ctrl + Alt + S) to display the Server Explorer.

Now create a connection to the database where the SP you want to debug is located (Right click on Data Connections and click Add Connection...).

When the connection is added to the list expand the list and browse to the Stored Procedures and right click on the SP which you want to debug and select Step Into Stored Procedure. Now if the SP is requiring any parameters a dialog box will be displayed to enter the values. After entering them click Ok to run into the SP.

After you were taken into the debug mode you can use the same keys to debug SPs as if you are debugging application code (Step Into - F11, Step Over - F10, Step Out - Shift + F11).
If you like to use the Debug toolbar activate it by Clicking on View -> Toolbars -> Debug.

If you want to know what the buttons does, just hover on top of the tool so a helpful tooltip will appear. If you need more information press on the help tool which is the right most tool with a yellow question mark.

Also remember that all the additional features are also available for you to use (as if the Immediate Window) while debugging.


2.) SQL Server 2000
In SQL Server 2000 you have to use the SQL Query Analyzer to debug SPs.
Start SQL Query Analyzer and click on Tools -> Object Browser -> Show/Hide (or F8) to display the Object Browser if it is not already shown.

Now expand the database where the required SP is located and right click on the SP which you needs to debug inside Stored Procedures node.

Click on the Debug... to start the debugging, if the SP requires any values for its parameters a window will pop up to enter the values. After entering the values click on Execute.

When debugging starts you can use the available controls or shortcut keys (Step Into - F11, Step Over - F10, Step Out - Shift + F11, Run to Cursor - Ctrl + F10) to debug through the SP.



If you want to know what the buttons does, just hover on top of the tool so a helpful tool tip will appear. If you need more information press on the help tool which is the right most tool with a yellow question mark.

2 comments:

Rick said...

"When debugging starts you can use the available controls or shortcut keys (Step Into - F11, Step Over - F10, Step Out - Shift + F11, Run to Cursor - Ctrl + F10) to debug through the SP."

And when these buttons are not available (grayed)?

Please tell what I do. ;-)

Ricardo.

Arjuna Chiththananda said...

Hi Rick,

First of all sorry for being late for the reply.

If those buttons are not available that means that there is a problem while starting the debugging session.
Check the following,

1. Did you login to the Query Analyzer using the SA account?

To debug Stored Procedure (SP)s you need permission to access 'sp_sdidebug' (SP) in SQL 'master' database. With default permissions only SA has permissions to access the master database. So try login as SA or try giving permissions to access the master database to the user you are login. (The latter is not much recommended since who ever having access to the master database can play with your database.)

2. Did you provide the required parameters when running the SP?

Sometimes your SP might be requiring parameters to run it. If needs then you can supply them using the popup screen you get. Also note that if it needs parameters of type DateTime (any Date related parameters) you have to properly give the format which SQL Server accepts. If you swap Day with Month SP will fail to debug.

In both cases above buttons will not be available to you.

Hope this helps,
If you have any more problems please write back.

Kind Regards,
Arjuna.