Debugging Stored Procedure in SQL Server 2008

Question

Is there any way to debug a stored procedure on SQL Server 2008    I have access to use SQL Server Management Studio 2008 and Visual Studio 2008  not sure whether either provides this functionality    Generally I use the SQL profiler to find the parameters passed to the stored proc  however would like to be able to step through the procedure to see where it is failing   Is this possible   What is the best way   in terms of quickly finding location of bugs

User · Answer

Yes  although it can be tricky to get debugging working  especially if trying to debug SQL on a remote SQL server from your own development machine  In the first instance I d recommend getting this working by debugging directly on the server first  if possible  Log to the SQL server using an account that has sysadmin rights  or ask your DBA to to do this  Then  for your own Windows account  create a  login  in SQL Server  if it isn t already there       Right-click the account   properties - ensure that the login is a member of the  sysadmin  role        also ensure that the account is  owner  of any databases that you want to debug scripts  e g  stored procs  for       Then  login directly onto the SQL server using your Windows account  Login to SQL server using Windows Authentication  using the account you ve just used to log into the server  Now  Debug  the query in SQL management studio  setting breakpoints as necessary  You can step into stored procs using F11       Here s a useful guide to debugging    http   blogs msdn com b billramo archive 2009 04 11 transact-sql-debugger-for-sql-server-2008-part-1 aspx   If you need to remotely debug  then once you ve got this part working  you can try setting up remote debugging    http   blogs msdn com b billramo archive 2009 04 11 transact-sql-debugger-for-sql-server-2008-part-2 aspx

User · Answer

MSDN has provided easy way to debug the stored procedure  Please check this link-  How to  Debug Stored Procedures

User · Answer

One requirement for remote debugging is that the windows account used to run SSMS be part of the sysadmin role  See this MSDN link  http   msdn microsoft com en-us library cc646024 28v sql 105 29 aspx

User · Answer

Well the answer was sitting right in front of me the whole time   In SQL Server Management Studio 2008 there is a Debug button in the toolbar  Set a break point in a query window to step through   I dismissed this functionality at the beginning because I didn t think of stepping INTO the stored procedure  which you can do with ease    SSMS basically does what FinnNK mentioned with the MSDN walkthrough but automatically   So easy  Thanks for your help FinnNK   Edit  I should add a step in there to find the stored procedure call with parameters I used SQL Profiler on my database

User · Answer

Yes you can  provided you have at least the professional version of visual studio   although it requires a little setting up once you ve done this it s not much different from debugging code  MSDN has a basic walkthrough

[sql-server] Debugging Stored Procedure in SQL Server 2008

Examples related to sql-server

Examples related to sql-server-2008

Examples related to debugging