Click here to Skip to main content
16,016,669 members
Articles / Programming Languages / SQL

Debug your stored procedure

Rate me:
Please Sign up or sign in to vote.
4.42/5 (27 votes)
31 May 2016CPOL4 min read 93.6K   22   10
This article will help you to know how to 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

Image 1

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

            Image 2

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

            Image 3

  • 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

            Image 4

 

  • 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

          Image 5

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)

            Image 6

  •  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

           Image 7

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

           Image 8

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

Image 9

Following exception occurred when you tried to debug Remote stored procedure

Image 10

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
India India
Hi there, I am Prasad. Author, Blogger, contributor and passionate about Microsoft .NET technologies. I like to write an articles/blogs on different .NET aspects and like to help Developers, to resolve their issues and boost them on Microsoft Technologies.


Certifications: Microsoft Certified professional (MCP), Microsoft Certified technology specialist (MCTS), Agile-Scrum Master.


Awards: Microsoft Re-connect MVP (GSC Member), Most valuable member at dotnetspider, Most popular curator, Most active curator, featured curator at Microsoft Curah, Editor at dotnetspider.


Microsoft MVP 2014 [ASP.NET/IIS]
Click here for more .NET Tips
-After all Knowledge is an endless entity

Comments and Discussions

 
Questiondebug a stored procedure in SQL Azure Pin
Member 1338623813-Mar-19 7:34
Member 1338623813-Mar-19 7:34 
QuestionVisual Studio 2017 Pin
Quarterback166-Nov-18 18:09
Quarterback166-Nov-18 18:09 
Hi Prasadd,

Do you know if this still works with Visual Studio 2017? I have enterprise version and I don't get the Application Debugging and Allow SQL/CLR Debugging options when I right click the Server in Server Explorer.

Cheers
Steve
AnswerRe: Visual Studio 2017 Pin
sirius-black23-Nov-18 6:46
sirius-black23-Nov-18 6:46 
GeneralRe: Visual Studio 2017 Pin
Quarterback163-Jan-19 14:54
Quarterback163-Jan-19 14:54 
PraiseThank you so much Pin
Member 119682825-Jun-16 19:43
Member 119682825-Jun-16 19:43 
GeneralGood one Pin
Gaurav Aroraa4-Jun-16 21:01
professionalGaurav Aroraa4-Jun-16 21:01 
GeneralRe: Good one Pin
koolprasad20035-Jun-16 17:57
professionalkoolprasad20035-Jun-16 17:57 
QuestionVery Helpful...5 Pin
RhishikeshLathe4-Jun-16 20:29
professionalRhishikeshLathe4-Jun-16 20:29 
AnswerRe: Very Helpful...5 Pin
koolprasad20035-Jun-16 17:57
professionalkoolprasad20035-Jun-16 17:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.