Debugging TSQL Code in MS SQL 2000

Question

Debugging TSQL Code in MS SQL 2000

SQLGuy64

Ten Centuries

Points: 1081
More actions
June 7, 2007 at 11:46 am

#120352

Greetings,
Thanx for your time in helping me out. I am having difficulties debugging MS SQL 2000 procs. I am using the debuggers in MS SQL 2000 Query Analyzer and Visual Studio 2003 Enterprise Edition.
My problem is that once I step into the proc the proc runs to completion in both Query Analyzer and VS. That means I am unable to step through the proc. The SQL instance I am debugging the proc is run from a WindowsNT security account.
Any ideas on what I am doing wrong?

Viewing 6 posts - 1 through 5 (of 5 total)

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

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 1

I can only speculate, but I have had dificulties in the past successfully setting break points in SQL Stored procedures. There are two things that I found essential:

--Run the following in QA to turn on debugging after reboot.

Exec sp_sdidebug 'legacy_on'

--In Visual Studio somewhere on the project property page is an option to allow debugging in SQL server. I don't have 2003 handy right now only 2005 and in 2005 there is a "debug" tab and near the bottom of the page is an check box for "Enable SQL Server debugging". Make sure that option is enabled, recompile, set a NEW break point and try again.

Good luck,

James.

jmguazzo Valued Member Points: 65 More actions · Answer 2

Not quite sure about this but I think that debugging is not feasible on a SQL2000.

When we have to do some debugging on sql2k, we use the "PRINT ..." statement.

Ex from an old StoredProc we have. In this SP, the query is dynamically build depending on given params.

--Params

@Filter1 bit=0

@Filter2 bit=0

@description nvarchar(500)=N''

@orderby int=0

--SP's code

declare @querystring nvarchar(3000)

set @querystring= N'select * from tblInfo where Field1=1'

if @Filter10

SET @querystring = @querystring + 'AND Field2=1 '

IF @Filter20

SET @querystring = @querystring + 'AND Field3=1 '

-- Is there a description to look for ?

IF @description ''

SET @querystring = @querystring + N'AND Description like N''' + @description + N''' '

IF @orderby = 0

SET @querystring = @querystring + ' ORDER BY Field1,description'

ELSE

SET @querystring = @querystring + ' ORDER BY Description,Field1'

PRINT (@querystring)

EXECUTE(@QueryString)

Tom257 SSChampion Points: 11543 More actions · Answer 3

When using .NET 2003, I used to step up to the SQL call, then transfer to Query analyser to initiate debugging, setting up the stored procedure parameters manually - its not so convenient but it works. I don't remember ever getting Visual Studio 2003 to drop into SQL debugging

Print statements are useful when developing stored procedures. You can get the output from .NET using SqlInfoMessage (or InfoMessge) in ADO.NET (I think its a property of the connection object)

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 4

Tom: Once it's set up properly debugging SQL is a FANTASTIC feature. I love being able to step through the code, examine variable values, etc. The only problem I've ever had is getting it started. I've almost always had to run the Exec sp_sdidebug 'legacy_on' through Query Analyzer/Management Studio and then getting Visual Stuido to disconnect/reconnect and usually after exiting and restarting VS it would start working. Not sure why it always gave me fits but it was always worth the effort.

While I have been using VS 2005 for a while now I'm pretty sure I was debugging SQL with 2003 (though I won't swear to it, one day seems to blur into another now adays )

James.

SQLGuy64 Ten Centuries Points: 1081 More actions · Answer 5

thank you all for your posts. I'll let you know how it turns out.