1

I'm working with SQL Server 2012 SP2 and I have a problem with a stored procedure.

I have a table where we do a lot of things in a second: we insert and update rows very often.

I have a stored procedure to update a row, and it works fine most of the time, but sometimes it doesn't update a row but @@ERROR is zero. This is the update statement (@isAuto is another parameter):

-- Set new Commisioning Flag value depending on is an auto or manual read.
if (@isAuto = 1)
    set @newCommFlagValue = 20  -- Commissioning Auto
else 
    set @newCommFlagValue = 120 -- Manual Commissioning.

[ ... ]

-- Get Code PK to do fast queries. @code is a stored procedure parameter
-- Serial has an unique constraint
set @codeId = (select CodeId from Code where Serial = @code);

-- Get current flag.
SET @currentCommFlag = (SELECT CommissioningFlag
                            FROM Code
                            WHERE CodeId = @codeId);

if (@currentCommFlag is null)
begin
    if (@@TRANCOUNT > 0)
        rollback transaction

    SELECT @message = 'ReadCode ' + @code + ' return -17';      
    EXEC master..xp_logevent 60000, @message, informational;

    return -17 -- @code doesn't exist on Code table.
end

[ ... ]

begin try
    if (@helperCodeId is not null)
        UPDATE Code
            SET CommissioningFlag = @newCommFlagValue
                        , Source = @source
                        , UserName = @username
                        , LastChange = CAST(SYSDATETIMEOFFSET() as nvarchar(50))
                        , SentToNextLevel = 0
                        , HelperCodeId = @helperCodeId
        WHERE CodeId = @codeId
    else
        UPDATE Code
            SET CommissioningFlag = @newCommFlagValue
                        , Source = @source
                        , UserName = @username
                        , LastChange = CAST(SYSDATETIMEOFFSET() as nvarchar(50))
                        , SentToNextLevel = 0
        WHERE CodeId = @codeId

    SELECT @ErrorVar = @@ERROR  
            , @RowCountVar = @@ROWCOUNT; 
end try
begin catch
    SELECT @message = 'ReadCode ' + @code + ' error ' + ERROR_NUMBER() + ' - ' + ERROR_MESSAGE();
    EXEC master..xp_logevent 60000, @message, informational;
end catch
-- If it hasn't been updated...
IF @ErrorVar != 0
BEGIN

    SELECT @message = 'ReadCode ' + @code + ' error update Code';
    EXEC master..xp_logevent 60000, @message, informational;

    SET @code = NULL
    if (@@TRANCOUNT > 0)
        rollback transaction
    RETURN -1
END
else
begin   
    if (@@TRANCOUNT > 0)
        commit transaction;

    if @RowCountVar = 0
    begin
        SELECT @message = 'ReadCode ' + @code + ' - no actualizado';
        EXEC master..xp_logevent 60000, @message, informational;

        return -1;
    end
    else
    begin
        set @commFlag = (Select CommissioningFlag from Code where CodeId = @codeId);

        SELECT @message = 'ReadCode ' + @code + ' - flag ' + CAST(@commFlag as varchar(2)) + ' - return 0';
        EXEC master..xp_logevent 60000, @message, informational;

        return 0;
    end
end

There isn't any transaction on the stored procedure. Maybe the table is locked by an insert or another process.

After stored procedure execution I do a select for that row and it has the previous value on CommissioningFlag column.

I'm sure this stored procedure works because it updates nearly 98% of the rows. But there is a 2% that it isn't updated.

XACT_ABORT is not set.

Do you why an Update doesn't update a row but it doesn't return an Error?

I've been investigating what it is happening and I have seen something very interesting: enter image description here

Row number 5 hasn't been updated. This is the error. My program first update row 1, then row 2, etc. Each update triggers 0.15s. Update between row 4 and row 6 gets 0.30s (more or less). It seems that row 5 is updated in 0.15s without any delay but it isn't stored the change in database.

  • Since you're checking CommissionFlag after execution, where does @newCommFlagValue come from and are you sure it does not contan the actual current value in the table so that it appears the update is not working when it really is? I'm guessing you've tried stepping this through the debugger with no luck? You may have to resort to putting some 'poor-man-debugger' code in the failing SP in an attempt to discover the problem. While it's certainly possible there is a bug in Sql Server, my money is on a logic problem in the SP. – Scott Hodgin Nov 24 '16 at 12:49
  • @ScottHodgin If 98% of the time the SP does well, and it fails only on a 2% I think the problem is not in the SP's logic. Every time I make a call to the SP I only change the @code parameter and the others parameters have the same value. This is why I think there is another kind of problem that I don't handle like table lock, SP's execution doesn't end propertly, etc. – VansFannel Nov 24 '16 at 14:17
  • I've found something interesting here: stackoverflow.com/a/19964907/68571 – VansFannel Nov 24 '16 at 15:08
  • the only thing i get from the link is that an update that doesn't update any rows is not considered an error - but that would be obvious that it's not an error - the fact that no rows were found for an update 'may' be determined to be an error from a business rule standpoint, but it's certainly not an error from a sql server standpoint - did i miss something else in the link you posted? 'Our' challenge in trying to help you is, we simply do not have your environment at hand to play with. Honestly, I'm personally out of ideas, but if i think of something, i'll add another comment. – Scott Hodgin Nov 24 '16 at 15:25
  • Maybe the UPDATE doesn't update any rows but @codeId exists on Code table (I've check it before I do the update). This is why I'm asking: why an update doesn't update a row if the where clause exists? – VansFannel Nov 24 '16 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.