Post reply

Debugging Stored Procedures

  • March 25, 2003 at 5:29 am

    #79748

    hi all,

    I wanted to debug a Stored Procedure from using debug option in Query Analyzer. when i am trying to do this it not breaking .. its simple getting executed. i wanted it to break at the begining so that i can ececute line by line.

    Thanks in advance

    Pratap

  • March 26, 2003 at 12:54 pm

    #452215

    Hmmm. I didnt know you could do that type of thing with Query Analyzer. What version of Query Analyzer do you have, becasue I would like to get it. I currently have 8.00.194. And here I have been debugging the old-fashinned way, commenting out lines and using @@error and @@rowcount.

    Andrew J. Hahn


    Andrew J. Hahn

  • March 26, 2003 at 1:15 pm

    #452223

    Prat, when you start the debugger it automatically starts the procedure you wish to debug. To step through it , you have to use the F10, F11 keys. Once it satarts though it is pause mode, and you should be able to set a breakpoint by selecting a line and using the F9 key. Let me know if that helps.

    Scran take a look at :

    http://qa.sqlservercentral.com/columnists/bkelley/qa2k_2_1.asp

    http://qa.sqlservercentral.com/faq/viewfaqanswer.asp?faqid=366

    The debugger is available with the 2K QA.

    Tim C.

    //Will write code for food


    Tim C //Will code for food

  • March 26, 2003 at 10:08 pm

    #452282

    Andrew,

    In Query Analyzer 8.00.194 we a feature to debug a Stored Procedure.

    Tim,

    thanks for ur reply. my problem is it is not getting into pause mode. The SP is getting executed and and displaying the results. if it pauses at then i can step throgh it using F11,F10. Problem is How to get it into pause mode?

    Thanks in Advance

    Pratap

  • March 27, 2003 at 8:22 am

    #452349

    To debug, I do this :

    A) Right-click a stored proc in the QA object browser

    B) Click "Debug"

    C) Fill in the parameters with values and click "Execute"

    D) After waiting a second or two, the proc starts in pause mode

    E) Use F10, or F11 to step through the proc or F5 to run to end

    Can you tell me the exact steps you go through to debug? Are they different than what I have above? What you are saying goes against every QA I have used to debug. Almost sounds like your client tools install is corrupted or some setting is changed that causes this.

    Optionally I add global variables like @@ROWCOUNT or @@IDENTITY, etc... If these steps do not work for you, and it is running through to the end, then I am unsure of the cause. Will try to do some research.

    Tim C.

    //Will write code for food


    Tim C //Will code for food

  • March 27, 2003 at 10:02 am

    #452370

    Hi,

    I'm having this problem too. For example, if I'm on server 1, I can execute debug step by step only when the procedure is on server 1. If I try execute a remote procedure on other server, the debug only show the results; I can't execute on step by step mode.

    I know it's possible cause there is one computer here in the enterprise that execute debug on step by step mode on any server.

    When I discover the configuration of this computer, I will publish the resolution.

  • March 27, 2003 at 10:08 pm

    #452416

    Tim and deselles

    thanks for ur replys.

    tim,

    i am alo following the same steps as you have mentioned. and for me it is failing on step (D). it is not pausing there, instead it is executing the Procedure and showing the results.

    i am also tryng to find out the cause of the problem and if any of you have the solutions please help.

    Thanks in advance

    Pratap

  • March 28, 2003 at 12:14 pm

    #452482

    I get the resolution finally!!! How much do you pay to know?? Joke...

    Only install SQL Server Service Pack 3 on the computers that have installed the server of the SQL Server. The computer that have the client don't need install service pack.

    Daniel E. Selles

  • March 28, 2003 at 12:53 pm

    #452487

    deselles, hmmmm, I am not running the server on my machine, and I have SP3 on the server as well as my machine, and I can debug. Maybe you changed something else?

    Tim C.

    //Will write code for food


    Tim C //Will code for food

  • March 28, 2003 at 1:26 pm

    #452493

    Before installing the SP3 I had set the permitions on DCOM. But only after installing the SP3 I get step by step debug.

    Server: Windows 2000 Server

    Client: Windows XP

    DataBase: SQL Server 2000

    DCOM Permission Settings:

    SQL debugging uses Distributed COM (DCOM) to communicate between your client computer and the database server. You must configure DCOM to allow remote users to attach the debugger to a process on the database server.

    By default, the correct DCOM settings are in place when SQL Server is installed. However, because of security considerations for the computer running SQL Server, you may want to restrict debugging access. Use the following as a general procedure for setting up DCOM on the SQL Server computer.

    Important If you plan on running the debugger as any user other than the one running SQL Server, DCOM will need to be correctly configured first. Follow these steps to insure you have execution permission.

    On the server:

    1. Run DCOMCNFG.EXE.

    2. In the Distributed COM Configuration Properties window, select the Default Security tab. Under Default Access Permissions, click Edit Default.

    3. If group Everyone does not already have permissions, you may add it by clicking Add. Select the local machine name from the List Name From list. Select Everyone and make sure that Type of Access is set to Allow Access. Then click OK.

    OR

    If you prefer to limit debugging to specific users, you can add individual domain user accounts (e.g. domain\account) with administrator privileges. If you choose to add only domain accounts instead of Everyone, make sure to add the SYSTEM account as well.

    4. Switch to the Applications tab of the Distributed COM Configuration Properties dialog.

    5. Scroll through the Applications list and select SQLDBREG. Then click Properties.

    6. Select the Identity tab and make sure that The interactive user is selected as the user to run this application.

    7. Click OK to close the dialog box.

    8. Restart the SQL Server service.

    After applying those steps, your server's DCOM settings should look like this:

    DCOMCNFG

    |

    |__ Application Tab

    | |

    | |_____ SQLDBREG

    | |

    | |______ Identity Tab

    | |

    | |_______ The interactive user

    |

    |__ Default Security Tab

    |

    |_____ Default Access Permissions

    |

    |______ Edit Default Button

    |

    |_______ Everyone (or domain\account and System)

  • March 31, 2003 at 2:34 am

    #452544

    deselles,

    Thnaks. debug is working now. i have just installed SP3 on Client & Server and thats all it started working..

    Pratap

Viewing 11 posts - 1 through 10 (of 10 total)

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