Debugging Stored Procedures

Question

Debugging Stored Procedures

pratapkrg

SSC Enthusiast

Points: 157
More actions
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

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

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

scranthdaddy Mr or Mrs. 500 Points: 552 More actions · Answer 1

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

tcartwright SSCertifiable Points: 5573 More actions · Answer 2

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://www.sqlservercentral.com/columnists/bkelley/qa2k_2_1.asp

http://www.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

pratapkrg SSC Enthusiast Points: 157 More actions · Answer 3

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

tcartwright SSCertifiable Points: 5573 More actions · Answer 4

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

deselles Grasshopper Points: 10 More actions · Answer 5

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.

pratapkrg SSC Enthusiast Points: 157 More actions · Answer 6

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

deselles Grasshopper Points: 10 More actions · Answer 7

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

tcartwright SSCertifiable Points: 5573 More actions · Answer 8

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

deselles Grasshopper Points: 10 More actions · Answer 9

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)

pratapkrg SSC Enthusiast Points: 157 More actions · Answer 10

deselles,

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

Pratap