Unable to debug a sproc

  • I have a count variable incrementing and printing out. I have a log for every catch and every try. Nothing is unexpected except the number of items (rows) created in the destination database. I will test one of these non-existent rows with the relevant parameters to see if I can get an error at source.

  • It's hard to be specific with an advice without knowing how the procedure is built.

    but generally speaking - you can include any value of any variable you're interested in while debugging into those raiserror messages. You may have the message before population and immediately after the row population. "After" statement may contain the number of added records.

    This way you can see which particular variable(s) values caused missing rows.

    You can make those messages as detailed as you need for your debugging. I never felt a need for debug functionality - the messages tell me everything I may possibly want to know, including flow control.

    _____________
    Code for TallyGenerator

  • ok it is solved and posted here for any interest.

    Seems it comes down to my inexperience with cursors. I'd normally avoid them due to the bad press but at times I find them a necessary evil. With that said.....

    So to recap, there were 57 rows of data with only 31 iterations of the cursor and no errors. To confuse me even more the last line of the cursor, which flags a row (in the source data) as processed updated 55 rows (2 rows had data errors).

    So, it was my assumption that when you did a select, with a where clause,  for a cursor you got a snapshot of the data or a temp table in memory of sorts. But it appears that for each iteration the source data was rechecked.

    The last line of code was to update the row as processed where column X = Variable X. Seems this test data had duplication so column X may appear 3 times and all 3 rows would get flagged. Then rightfully that row was not processed. Hence the mismatch in the initial select and the number of iterations. Seems I inadvertently added a safety net to deal with duplicate values :). Anyways mystery solved.

    A quick refresh of the cursor doco reminds me of all the available keywords that can alter a cursors behaviour.

  • I missed debugging in SSMS for a short while although I didn't miss the number of times debugging would crash SSMS. I think I'm actually happier with my current method for debugging stored procedures.

    Stored procedure:

    CREATE OR ALTER PROCEDURE dbo.GetObjectTypeFromSysObjects (
    @ObjectType CHAR(2)
    )
    AS
    BEGIN
    /*
    Object types:
    TT IF SN U SQ SO F PC C D P V S TR K IT TF FN
    */

    SELECT
    sc.name,
    s.name,
    s.type,
    s.type_desc
    FROM sys.objects AS s
    INNER JOIN sys.schemas AS sc ON s.schema_id = sc.schema_id
    WHERE s.type = @ObjectType
    ORDER BY sc.name, s.name;

    END;

    I'll just open the SP in SSMS and comment out a couple lines, add a DECLARE and default values and RAISERROR() as Sergiy mentioned

    --CREATE OR ALTER PROCEDURE dbo.GetObjectTypeFromSysObjects (
    DECLARE
    @ObjectType CHAR(2) = 'P'
    --)
    --AS
    BEGIN
    /*
    Object types:
    TT IF SN U SQ SO F PC C D P V S TR K IT TF FN
    */

    -- Use RAISERROR(). PRINT will not display until the batch finishes.
    -- RAISERROR() displays immediately using WITH NOWAIT
    RAISERROR(N'Object type = %s', 0, 1, @ObjectType) WITH NOWAIT;

    SELECT
    sc.name,
    s.name,
    s.type,
    s.type_desc
    FROM sys.objects AS s
    INNER JOIN sys.schemas AS sc ON s.schema_id = sc.schema_id
    WHERE s.type = @ObjectType
    ORDER BY sc.name, s.name;

    END;

    RAISERROR() WITH NOWAIT will output to the message window as soon as that line is encountered unlike PRINT which will wait for the batch to complete. I scatter these throughout to check state and usually that is enough to tell me what is going on.

  • Tom Uellner wrote:

    I missed debugging in SSMS for a short while although I didn't miss the number of times debugging would crash SSMS. I think I'm actually happier with my current method for debugging stored procedures.

    ...

    Did you see my post? Debugging is still available in VS, should you decide you'd like to use it.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin wrote:

    Tom Uellner wrote:

    I missed debugging in SSMS for a short while although I didn't miss the number of times debugging would crash SSMS. I think I'm actually happier with my current method for debugging stored procedures.

    ...

    Did you see my post? Debugging is still available in VS, should you decide you'd like to use it.

    I did Phil, thank you. I am very rarely in Visual Studio anymore but it's good to have the option. Just giving the OP another option.

  • I think you can get that behavior with one of those keywords right?

    INSENSITIVE

    Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications. When ISO syntax is used, if INSENSITIVE is omitted, committed deletes and updates made to the underlying tables (by any user) are reflected in subsequent fetches.

    Just reading about your solution out of curiousity thanks for posting what actually happened!

     

Viewing 7 posts - 16 through 21 (of 21 total)

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