Debug your stored procedure

Introduction

We can debug our .NET application easily using a debugger from debug menu, .NET application has its own .PDB (Program Debug Database) file that used to store Debug location and increment linking of debug configuration. This all about .NET but can we debug our stored procedures? Answer is YES, we can debug our stored procedure. Let's find out How to do it.

Background

Stored procedures are popular enough due to their features like enhanced security, separation is possible in data functionality and application, improved performance (as fewer call made to database), basically it's a one time and one place processing theme. SP (stored procedure) is now more popular in DBA and Developer crowd, But after writing large SP's, how to debug them ? How to test them if they are running properly.

So here is the need to Debug stored procedure, Let's see what we need to debug it.

Bit by bit

1. First step is to create a Stored procedure (if you have stored procedure already created then you can skip this step), Here i am creating a simple Stored procedure, which select record from EMP table for specific ID, see below snap

2. After stored procedure created successfully, we are ready to debug it. Basically There are couple of ways to debug stored procedure

1. Using SQL Management studio (SSMS)
2. Using Visual Studio

Will see how to Debug it using SQL Management studio (I am using SQL 2008R2 Express)

• Open SQL 2008 R2
• Click on ViewMenu --> Object Explorer
• Click on Connect database engine  --> Enter username and password for SQL database --> Connect to database, see below snippet

• Expand Database --> Select Programmability folder --> Stored Procedures (You will see your procedure here)

• Open New Query window by right click on database and click on 'New Query'
• Write statement 'Exec ProcedureName' (in my case its procedure name is 'sp_empInfo')
• Put Debugger on that line using key F9

• Select Green arrow (debug) from SQL and your debugger gets start, Press F11 to step in to procedure
• You can see output in 'local' and 'callstack' window

Debug it using Visual Studio

Same way you can use Visual Studio to debug the stored procedure

• Go to Visual studio and connect database. follow below steps
•  Open Visual studio editor
•  Click on ViewMenu --> SQL Server object Explorer (Before VS 2012 it was Server explorer)

•  In SQL Server object Explorer Pane right click on Data Connections
•  Select Add connection
•  Enter credential (like SQL instance, SQL server user name, Password, database name)
• Right click on database and allow 'Application debugging' and 'Allow SQL/CLR debugging', see below snap

• Expand Database --> Select Programmability folder --> Stored Procedures
• Right click procedure --> Select 'Debug Procedure'

You can start Debug from now.

Exceptions

Access permission is the biggest issue while debug stored procedure,

Common Error Messages

following are the common error messages occurred during debug stored procedure

1. Unable to start the transact-SQL debugger, could not connect to the Database Engine instance...
2. Unable to start T-SQL debugging, Could not connect to computer...
3. Logon failure: Unknown username or bad password...
4. Failed to start debugger, The execute permission was...
5. Could not attach to SQL Server process on...

see some exceptions/errors snap below

Following exception occurred when you tried to debug Remote stored procedure

Common points to avoid error while debugging

To resolve above errors you can try out following workarounds

1. If you are debugging stored procedure on local SQL server then your local SQL user should be in 'sysadmin' role, to add user in sysadmin role, run below stored procedure (it is inbuilt), see below syntax
2. SQL

   sp_addsrvrolemember '<Login>', 'sysadmin'

3. Configure TCP port in Windows firewall to enable t-SQL Debugging, for details see MSDN link
4. Configure TCP port in Windows firewall to enable for remote t-SQL Debugging (when SQL server is different and want to Debug it from client machine) see MSDN link, For remote debugging in either case you can use Windows local or domain account authentication
5. Open firewall ports on both SQLserver machine as well as client, PORTS: TCP 139, TCP 445, UDP 137 and UDP 138 
6. SQL management Studio is should be lunched with “Run an administrator…”
7. Management studio(Client machine) and Server (SQL) should be in same domain, otherwise you need to setup account with same Username and password

What we learn !

We can debug stored procedure by using Visual studio or by using SSMS (Sql management studio), we can use local variables, call stack windows for that. You can provide parameters to stored procedure (if any) and get instant debugger on it. 

Hope it will be useful in daily .NET/SQL life. 

Thanking You

- Prasad