locked
Debug Stored Procedures RRS feed

  • Question

  •  

    How to debug Stored Procedures in sql server 2005?

    In sql server 2000 sql debugger is available but in sql server 2005 which options are available?

     

    ** without using visual studion dotnet

     

    Friday, October 31, 2008 11:38 AM

Answers

  • There isn't one.  This was a feature that was removed in 2005, and has been reintroduced in 2008.  There were a number of feedback items for this on Connect which is likely why it was added back in 2008.

     

    Feedback: Bring back stored procedure debugger in SQL Server 2005

    Feedback: Bring Debug Stored Procedure functionality to Management ...

    Feedback: debugging inside SQL Server Management studio

     

    If you don't want to go the VS route, then your only real recourse is to add print statements that output information during execution.  This is the only other way I know for debugging to occur.

    • Marked as answer by Naomi N Sunday, January 22, 2012 9:30 PM
    Friday, October 31, 2008 12:34 PM
  •  Satish Kumar Thota wrote:

     

    How to debug Stored Procedures in sql server 2005?

     

    In addition to Jonathan's notes, you can do manual step through due to the extreme flexibility of T-SQL editing/executing environment in Management Studio. You just have to select (highlight) the segment of code you want to test and press execute. Notes:

     

    1. Better to use #temptables than @tablevariables to support step-by-step debugging

    2. If a sproc has parameters, take the logic to a new window and mass replace the parameters with literals for debugging

    3. You may have to use

     

    Code Snippet

    SELECT...

    INTO #tempAlpha

    FROM....

     

    to store query results for debugging examination; when finished you can just delete or comment out the INTO line

     

    Let us know if helpful.

    • Marked as answer by Naomi N Sunday, January 22, 2012 9:30 PM
    Friday, October 31, 2008 12:49 PM

All replies

  • There isn't one.  This was a feature that was removed in 2005, and has been reintroduced in 2008.  There were a number of feedback items for this on Connect which is likely why it was added back in 2008.

     

    Feedback: Bring back stored procedure debugger in SQL Server 2005

    Feedback: Bring Debug Stored Procedure functionality to Management ...

    Feedback: debugging inside SQL Server Management studio

     

    If you don't want to go the VS route, then your only real recourse is to add print statements that output information during execution.  This is the only other way I know for debugging to occur.

    • Marked as answer by Naomi N Sunday, January 22, 2012 9:30 PM
    Friday, October 31, 2008 12:34 PM
  •  Satish Kumar Thota wrote:

     

    How to debug Stored Procedures in sql server 2005?

     

    In addition to Jonathan's notes, you can do manual step through due to the extreme flexibility of T-SQL editing/executing environment in Management Studio. You just have to select (highlight) the segment of code you want to test and press execute. Notes:

     

    1. Better to use #temptables than @tablevariables to support step-by-step debugging

    2. If a sproc has parameters, take the logic to a new window and mass replace the parameters with literals for debugging

    3. You may have to use

     

    Code Snippet

    SELECT...

    INTO #tempAlpha

    FROM....

     

    to store query results for debugging examination; when finished you can just delete or comment out the INTO line

     

    Let us know if helpful.

    • Marked as answer by Naomi N Sunday, January 22, 2012 9:30 PM
    Friday, October 31, 2008 12:49 PM