TSQL Debugger in Query Analyzer

Question

Post reply

TSQL Debugger in Query Analyzer

walker31808

Right there with Babe

Points: 724
More actions
January 3, 2005 at 8:22 am

#59880

I am trying to get the debugger to work and I am having a problem. I can right click on a stored procedure in the object browser and select debug. Then the parameter screen comes up and I enter my input parameters and click Execute. It then executes the whole stored procedure without letting me step through the code. Even when I toggle on a breakpoint it still seems to execute the whole stored procedure and the step into/over/out buttons are disabled? Why is that?
Install SQL 2000 Client SP3 [added 7/26/2004]

Run C:\TEMP\sql2000\SP3a\x86\setup\setupsql.exe
Take the defaults and agree to have your computer restarted when prompted.
After reboot verify that you have the right version # 8.00.760 when you run the “SQL Query Analyzer”

Install MDAC 2.8 [added 7/27/2004]

Run C:\TEMP\sql2000\MDAC\V2.8\MDAC_TYP.EXE
This requires another reboot.

i. The benefit you should expect is to be able to connect to SQLRSRSTAGE.BANKONE.NET/REPORTING

Install Security Patch for SQL Server Service Pack #3a

Run C:\TEMP\sql2000\SecurityPatch MS03-031\SQL2000-KB815495-8.00.0818-ENU.exe

[If you want to destroy SQL debugging, Optionally install G:\softinstall\SQL_service_pack\2000\SQL2KSP4\x86\setup\setupsql.exe which is Service Pack #4 for SQL Server 2000]

Blessings to all who struggle to make M*oft debuggers work.

Bill

TriggerMe SSChampion Points: 11752 More actions · Answer 1

Are you sure the T-SQL Debugger has been installed on the SQL Server?

Kindest Regards,

walker31808 Right there with Babe Points: 724 More actions · Answer 2

Is that not automatically installed when you install sql server?

MattieNH SSCertifiable Points: 7463 More actions · Answer 3

That sounds like what happens when the following is not true

From BOL:

SQL Query Analyzer comes equipped with a Transact-SQL debugger that allows you to control and monitor the execution of stored procedures. The debugger supports traditional functionality such as setting breakpoints, defining watch expressions, and single stepping through procedures. This functionality is available when the SQL Server service uses a domain user account.

In a client/server environment, the client workstation running the debugger must be a member of the same domain as the computer running the corresponding instance of SQL Server.

Mattie

walker31808 Right there with Babe Points: 724 More actions · Answer 4

That was it. When I tried the debugger on the server it worked.

Thanks for your help

tony-220752 SSC Veteran Points: 211 More actions · Answer 5

I also can use the debugger from the server that is running SQL 2000. I can't get it to work properly when I use it back at my client desktop connecting thru tcp/ip. Properly meaning being able to set breakpoints, stepping etc. It just executes the entire proc to the end and the only button that is available (not grayed out) is the 'go' button (f5).

I read on BOL subject t-sql debugger window and have set and reset the DCOM settings with everything wide open meaning access to 'everyone' for DCOM. I also checked sp_sdidebug to ensure that permissions are ok. I've been looking at many things for a while now to no good end.

Worse part is, 3 of use can't get it to work on our client desktops (3 seperate desktops) but 1 person it does work for. He doesn't know why it works and swears he didn't change any settings on QA.

The SQL server 2000 is running with a domain account HPL\SQLServer for it's services. All of us are running off the same domain HPL\username and we windows authenticate into the server with 'system admin' authority.

Every site and forum that I've read so far has this issue but no one really has a good answer for it including MS site which basically states what BOL says about the DCOM settings. They also mention some known issues with proc names that are duplicated that may cause the issue, but this is not my case once again.

Any hint is appreciated!

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

Out of curiosity, what SQL Server service pack is your SQL Server on? Have any SQL Server service packs been applied to any of the client machines?

K. Brian Kelley
@kbriankelley

tony-220752 SSC Veteran Points: 211 More actions · Answer 7

We are running SQL Server 2000 with SP3 with the latest hotfix to 8.00.818. None of the client machines have SQL Server 'server' installed, just the connectivity tools like EM, QA, Profiler etc.

Do these tools need to be upgraded maybe?? How do you check what level current tool sets are at? Is there a command or place to look?

tony-220752 SSC Veteran Points: 211 More actions · Answer 8

I just found that the server is running engine 8.00.818 but my client tools are at 8.00.194. I am going to try to upgrade my client tools first and see if this fixes it.

I've been looking at MS site to see how to upgrade just the client tools but have not seen anything that gives a step by step. I'm assuming that I just run the same sp3a or hotfix package for 8.00.818 on my desktop machine which has no database server on it, and this will just detect that I only have connect tools and upgrade these to 8.00.818.

Has anyone ever upgraded client tools before from 8.00.nnn to 8.00.818? I already have the package but am waiting for our network guy to come around since I don't have install priviledges on my desktop. Just want to make his visit a one time event instead of waiting for him to try again and again.

Thanks for you time.

Bill Moore SSC Journeyman Points: 84 More actions · Answer 9

To walker31808 and Tony Lau:

I was having similar problems with the debugger. We could connect to the SQL server 2000 (which supposedly has SP4 but that has not been proven). Several Most people in our group had upgraded their client tools to SP4 and could no longer debug.

But one programmer could debug (Thankfully he had not upgraded). That programmer had a query analyzer that reported Version 8.00.760 which we later determined was indicating MS SQL Server Service Pack 3a. After uninstalling my client, then reinstalling it with Service Pack 3a, I was able to debug again.

Eureka!

That's the gist of it but here are a few more tedious details of the steps that I followed after uninstalling the MS SQL Server client. The only piece of information you are not getting that would be critical is just exactly is the service pack we have on our server. I'm sorry, I've not been able to get that yet.

Install MS SQL Server Client Tools

Copy S:\soft_install\sql2000\*.* to C:\TEMP\
Run C:\TEMP\sql2000\ENT\X86\SETUP\SETUPSQL.EXE (ignore initial warning/error and proceed…selecting all items for installation which is not the default)

Bill Moore SSC Journeyman Points: 84 More actions · Answer 10

To walker31808 and Tony Lau:

I was having similar problems with the debugger. We could connect to the SQL server 2000 (which supposedly has SP4 but that has not been proven). Several Most people in our group had upgraded their client tools to SP4 and could no longer debug.

But one programmer could debug (Thankfully he had not upgraded). That programmer had a query analyzer that reported Version 8.00.760 which we later determined was indicating MS SQL Server Service Pack 3a. After uninstalling my client, then reinstalling it with Service Pack 3a, I was able to debug again.

Eureka!

That's the gist of it but here are a few more tedious details of the steps that I followed after uninstalling the MS SQL Server client. The only piece of information you are not getting that would be critical is just exactly is the service pack we have on our server. I'm sorry, I've not been able to get that yet.

Install MS SQL Server Client Tools

Copy S:\soft_install\sql2000\*.* to C:\TEMP\
Run C:\TEMP\sql2000\ENT\X86\SETUP\SETUPSQL.EXE (ignore initial warning/error and proceed…selecting all items for installation which is not the default)