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.
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.
