TEERA 2.0

At Plexia, our system makes extensive use of SQL stored procedure; yet, I’ve never been in a situation that would require debugging right into the SQL code. Actually you should not have to do so. If the application is well designed, complex business logics should be abstracted out from database to its own layer. Only until recently that I ran into a piece of legacy code that calls to a 100+ LOC stored proc. Visual Studio has a feature that allows smooth stored proc debugging and I found it to be quite handy. So on this post, I will share with you how to debug SQL 2005 stored procedure from Visual Studio 2008.

Tools

I use Visual Studio 2008 on MS SQL 2005 server. I’m pretty sure that you can do this with VS 2005 as well but I havn’t tried it yet.

Setting up the environment

When debugging T-SQL on SQL 2005 server, you may run into a problem with hardware or software firewalls. You can simply select “unblock” on any pop-up warning dialogs to allow VS and SQL server to communicate.

You also have to make sure that VS has enough privilege to access SQL server. You can do this by using Windows ‘Administrator’ account and run Visual Studio as administrator, both on the SQL server machine. You can also give additional accounts to SQL server’s sysadmin pivilege by using sp_addsrvrolemember '<Domain>\<Account Name>', 'sysadmin' command.

If you use SQL 2005 NT authentication model, make sure to give the account permissions to run sp_enable_sql_debug stored procedure. You can do so by running this script:

CREATE USER '<username>' FOR LOGIN '<Domain>\<Account name>'
GRANT EXECUTE ON sp_enable_sql_debug TO '<username>'

After you run this script, a new user account will be created for '<Domain>\<Account name>' Windows user with permission to debug SQL.

Warning!

You should NEVER do this on production server. It’s best to do this on local machines with everything installed.

1. Create test database and stored procedure

First I start by create a simple database called ‘Test’ with only ‘Products’ table.

Then I create a simple stored procedure called ’sp_getAllProducts’ to query for product on this table. Note that I use declared variable @count only for debugging purpose.

At this point, if you have set up privilege for Visual Studio correctly, you will already be able to debug stored procedure! You can do this by simply open VS Server Explorer, create connection to the database, select the stored procedure, right-click, and choose ‘Step Into Stored Procedure’.

If this stored procedure has input parameters, you will be asked to provide them. Yet, it’ll be even better if you can step into this stored procedure right from the application code!

3. Creating WPF client application

Next, I fire up Visual Studio 2008 and create a very simple WPF project. Let’s call this project ‘ProductBrowser’. I create a window with a ListBox to display items from database.

In the Window.xaml.cs code-behind file, I create a simple method that connect to the stored procedure. The returning data records are bound to the ListBox. Notice that I set the breakpoint when SqlCommand executes stored procedure.

4. Enable SQL debugging from the project

Next, go to Server Explorer window, right-click the test database connection, and select ‘Application Debugging’. This will allow application to step into SQL database programmability.

Right-click on the client application project in Solution Explorer, go to ‘Debug’ tab and select ‘Enable SQL Server Debugging’

5. Run the application in Debug mode

That’s it! Now, you can try setting breakpoint in the stored procedure and run your application in Debug mode. You will be able to step right into the stored procedure code right from your application.

And that’s all you need to do, easy right?