Debugging Stored Procedures in SQL Server 2005

Were you ever in need of debugging a stored procedure in SQL Server 2005. Any .NET Developer is well versed with debugging .NET applications. The .pdb files will be used internally to serve the purpose. Pdb files will have the mapping information from Native to MSIL and ultimately to the .NET code. I will not get into the internals of how this happens as this article falls under the SQL Server category.

Ooo lala! debugging stored procedures in SQL Server 2005 is not quite complex as one would expect. In real time applications not every business rule will be coded in the BAL/BOL (Business Access Layer/ Business Object Layer). At times we may need to implement partial business rules in the stored procedure itself. This may be due to bad coding practice/ design of the applications that force the developers to do so since its already in production.

This article aims at providing an insight into how debugging stored procedures can be configured by a DBA/ Senior DB programmer that makes the job of DB programmers lot more easier.

When I say debugging stored procedures in SQL Server 2005 remember am not going to use SQL Server Management Studio. Believe it or not for debugging Sps am not going to use Management Studio!. Ha! Then how do I do it. I will make use of Visual Studio 2005 IDE. Is it sounding bad. Yes. SQL Server Management Studio is not equipped with the ability to debug the code. However, VS 2005 IDE does. Confused!, Ok, lets get into the details.

Follow the steps carefully as described underneath.

Pre-requisites


1. Find the .exe file under the directory, C:\Program Files\Microsoft SQL Server\90\Shared\1033\rdbgsetup.exe or similar path where the SQL Server was installed. The file rdbgsetup.exe stands for 'RemoteDeBuGsetup'. Look for the emphasis on letters. The filename reads rdbgsetup because, we are going to debug a stored procedures of a database available in some remote physical server. Either we need to sit at the server and debug them or we should be in a position to debug the stored procedure remotely. This action should be performed on the physical server where SQL Server 2005 was installed.

2. The user who debugs the stored procedure should be a member of SQL Server's fixed Server role, SysAdmin.

As a DBA, I may need to grant this privilege to the user who is in need of debugging a stored procedure. When I do that, I should trust the user so that he/she will not mess-up anything on the Server. Ok, I believe my users are trust worthy and I issue the following T-SQL command to assigning a fixer server role, SysAdmin.

The command to do so is as follows


EXEC master..sp_addsrvrolemember @loginame = N'<YourLoginName either SQL Server Authentication or Windows Authentication>', @rolename = N'sysadmin'

This can however be accomplished with SQL Server Management Studio's IDE. Perhaps, I prefer using T-SQL commands.

Note: The parameters to the stored procedure, sp_addsrvrolemember are of type, Nvarchar and it stands for (National Variable Character), which usually holds Unicode characters.

Now, we are all set for debugging stored procedures.

Process to Debug a stored procedure


1. Open the Microsoft Visual Studio 2005 IDE.

2. Select Server Explorer option from the View Menu as follows:



3. From the Data Connections node, right click and select, 'Add connection'. Fill in the details to connect to the intended SQL Server and the database using the login who has a fixed server role, SysAdmin. Click on Test connection. Once the connection succeeds, the overall screen should look like the following.



4. Expand the data connection just added, and navigate to the Stored Procedures node.

5. Expand the Stored Procedures node and select the intended SP to be debugged.

6. Right click and select open to view the source code of the selected stored procedure.

7. Place a break point on the intended line of code where debugging needs to be started its usually the way .NET Developers perform.

8. After performing the above steps the screen shot should look like the following.

9. After performing the above steps the screen shot should like the following:



10. Right click on the stored procedure from the 'Server Explorer' and select 'Step-Into Stored Procedure' as shown below.



11. This action brings up the following screen shot.



12. Enter the needful values and click Ok. The next shot will be the following.



13. From here on, its usual .NET debugging stuff. Use Step-Into and Step-Over and Step-out from the shortcuts menu or pressing F11,F10, Shift+F11

Wasn't that very simple. It made the life of DB developers much more comfortable. Had it not been available with SQL Server 2005 and VS 2005 IDE it would have been a nightmare to debug stored procedures remotely/locally.

Happy Development and concentrated debugging. 

by Deeraj