Post reply

How To Debug a lengthy Stored Proc in Sql Server 2000 ,that contains 5000 lines of code

  • November 5, 2008 at 8:55 pm

    #894651

    Thank you very much for your suggestion.

  • November 5, 2008 at 9:44 pm

    #894670

    anoop.sakthidharan (10/27/2008)

    anyway i don't want to use a error log , and track that log file, would like to know any other alternative way to debug a stored proc. make it more clear, can we debug that through the debugger window, through query analyzer. i found one debug option while right clicking the stored proc through the object browser , the dubug window comes but all the option like step into,step over are disabled may i know this reason and how to active the debugger.

    why dont you use Print and Return statements for debugging, if you dont want any log and third party tool

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • November 5, 2008 at 10:04 pm

    #894679

    that is ok. i agree with you apart from that i have to use debugger, when i am tring this dubugger window comes but then it wend into a hanging state , at last the result pan will displays the result produced by the stored proc, i am not able to use stepinto step over

    stuff over here.

  • November 6, 2008 at 7:43 am

    #894871

    Missing form my post:

    I had put "your first step" between the " " characters and this had the effect of ripping out anything between these characters.

    So, [font="Courier New"]EXECUTE @r = "your stored procedure".[/font]

    As for hanging when executing in the query analyzer window, then you can start at say, around line 500 and place a RETURN @li_Return_Code.

    This means that instead of you having to want until the stored procedures, you have to manually repeat the operation, removing the statement at line 500 and moving it to line 1000, etc. To help against forgetting stray traces in your code, I suggest you use the following:

    [font="Courier New"]PRINT "**** Debug ln 500"; RETURN @li_Return_Code[/font]

    Of course, the line numbers never remain fixed when you insert code in between previous lines of codes, but it provides a good clue as to where the statement is, so you can remove it directly from the stored procedure editor window, instead of having to copy and paste the entire stored proc code in Notepad and then use Notepad's search feature to locate the debug statement.

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply