Debugging TSQL Code in MS SQL 2000

  • SQLGuy64

    Ten Centuries

    Points: 1051

    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?

  • -=JLK=-

    SSCertifiable

    Points: 5315

    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

    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: 11475

    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

    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: 1051

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

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

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