SQL Debugger does not work from within the Query Analyzer 3

[9295]

I was trying to use the Transact-SQL Debugger.

I went to Object Browser, found my stored procedure, right clicked and chosed "Debug".

The "Debug Procedure" window came up. I entered values for all variables and clicked on the Execute button. I didn't see anything happen. I could not add "Watch" to watch any local and global variables (the paness were all empty.)

All of the "step" buttons (step-over, step-into, etc.), "restart", and "stop debugging" buttons were dimmed out.

I did a search on the web. An expert pointed out that the most likely reason was that the SQL Server is running under the local system account. It should be running on a domain account. His instruction was:

1. On the SQL Server machine, right click My Computer, choose Manager.
2. Find Services, and the find the MSSQL Server services. (There is one per instance.)
3. Right-click the service, select Properites and on the second tab is log on.

I followed the instruction and made sure it was NOT running under a local account. But it is still NOT working.

The expert said that I may also have problems if SQL Server is running SP3, but my Query Analyzer is SP2. I don't know what sp? my SQL Server and Query Analyzer are on (How to get that info?). I ran a "Legacy On" anyway but still not works.

I also found on the web that TCP/IP has to be set as default through DCOMCNFG.EXE. I checked it and TCP/IP was one of the default settings.

Can anyone help me? Many thanks.

 

[DaOtH]

Hiya,
Has indeed to do with the SP's

use SELECT @@VERSION
(See [URL unfurl="true"]http://support.microsoft.com/?kbid=321185[/url]

Query analyzer SP2 (actually it is about Win XP SP2) uses MDAC version 2.8
SQL Server SP3 uses MDAC 2.7

So, if you want to debug you have to upgrade the server to SP4 (or debug directly on the server)

See
[URL unfurl="true"]http://support.microsoft.com/default.aspx?scid=kb;en-us;839280[/url] as well

"In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963

 

[SQLSister]

First I do strongly suggest you install the service packs.

Personally even after I got the debugger to work, I found that it seems somewhat less than useful. But that's just me. Other methods for debugging are described here in case you are interested in them:
Debugging Complex Stored Procedures. faq183-4314

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[TysonLPrice]

Someimes the issue can be resolved by running:

sp_sdidebug

In the DB you are tesing.

 

[9295]

Dear DaOtH, SQLSister and TysonLPrice,

Thank you for all the quick and helpful responses from you all. I am sorry that I could not reply earlier because I was tied up by another project.

I was playing around with NorthWind sample database while waiting for help. I was surprised to find that the debugger was working on some stored procedures. After tried several stored procedures in the sample database, I found out the reason I had problem starting the debugger happened at entering the parameters' initial values.

I could start debugging without any problem if there was only parameter of 'int' data type. I will have the same problem as I described in my first posting if there were parameters of any other data type such as varchar, char, datetime, bit etc.

I did a search on the web and found a few articles about using the SQL debugger. Unfortunately, all examples use parameter of int data type.

Does anyone know what I should enter as the testing values for the parameters:

1. char: 'C' or "C" or C
2. varchar: 'Have a nice day' or "Have a nice day" or Have a nice day?
3. bit: 0 or 1; '0' or '1'; true or false; True or False
4. datetime: '9/25/2005' or "9/25/2005" or CAST('9/25/2005' as datetime)
5. Output parameter: ???? (no idea at all)

I have int, varchar, bit, datetime input parameters and one output parameter of int data type in my case.

Your help will be much appreciated.