Sql-server – Should SQL Server 2000 throw an error if update statement fails within a SP

error handlingsql serversql-server-2000transactionupdate

I'm troubleshooting an issue on SQL Server 2000 where it appears that a update statement in a stored procedure failed, and the check for @@ERROR <> 0 didn't catch it, so the transaction was committed.

Below is a condensed version of the code. I've removed a lot of details and proprietary information, but this is the gist of it. This code has been running daily for several months and producing the expected result, except for once last week, where the statement: 

UPDATE /*app table*/ 
   set dateprocessed = @currentDate 
 where jobname = 'Job_' 
       + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') 
       + LEFT(REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', ''), 4) 
       + '_' 
       + (SELECT Description FROM #tempTable1 WHERE id = @i)

…apparently did not actually update the referenced table, however the rest of the code executed and the transaction was committed.

If the update statement didn't complete sucessfully, should it have been caught by my @@ERROR check?

DECLARE @startTime DATETIME,
        @procedureName NVARCHAR(50),
        @i INT,
        @currentDate DATETIME,
        @minimumLetterCount INT;

SET @startTime = GETDATE();
SET @procedureName = (SELECT OBJECT_NAME(@@PROCID));
SET @i = 1;
SET @currentDate = GETDATE();

BEGIN TRANSACTION
    IF (--constraints are met)
    BEGIN
        IF OBJECT_ID('tempdb.dbo.#tempTable1') IS NOT NULL  
            DROP TABLE #tempTable1;

        IF OBJECT_ID('tempdb.dbo.##globalTempTable1') IS NOT NULL  
            DROP TABLE ##globalTempTable1;

        CREATE TABLE #tempTable1 (
            --columns defined here
        );

        CREATE TABLE ##globalTempTable1 (
            --columns defined here
        );

        INSERT INTO #tempTable1
            EXEC /*SP name*/

        --Process pending items by ID
        WHILE (@i <= (SELECT COUNT(*) FROM #tempTable1))
        BEGIN
            DECLARE @CurrentItemID VARCHAR(3);
            DECLARE @CurrentJobName VARCHAR(100);
            SELECT @CurrentItemID = (SELECT LetterID FROM #tempTable1 WHERE id = @i);
            SELECT @CurrentJobName = 'Job_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') + LEFT(REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', ''), 4) + '_' + (SELECT Description FROM #tempTable1 WHERE id = @i);
            EXEC /*SP name*/ @TableID = @CurrentItemID, @ThroughDate = @currentDate, @JobName = @CurrentJobName, @SeedItem = 1;

            INSERT INTO ##globalTempTable1
            SELECT --some data from other tables into this temp table

            UPDATE /*app table*/ set dateprocessed = @currentDate where jobname = 'Job_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') + LEFT(REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', ''), 4) + '_' + (SELECT Description FROM #tempTable1 WHERE id = @i)

            UPDATE l
                SET l.LinkedValue1 = lb.LinkedValue2
                FROM ##globalTempTable1 l
                INNER JOIN (SELECT UniqueID, SUM(CAST(REPLACE(REPLACE(REPLACE(REPLACE(value, '$', ''), ',', ''), '(', ''), ')', '') AS DECIMAL(12,2))) AS LinkedValue2 FROM ##globalTempTable1 GROUP BY UniqueID) lb ON l.UniqueID = lb.UniqueID;

            SET @i = @i + 1;

        END

    END

IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION

        --log failure
        INSERT INTO JobLog (jobName, success, startTime, endTime) VALUES (@procedureName, 0, @startTime, GETDATE());
    END
ELSE
    BEGIN
        COMMIT TRANSACTION

        --log success
        INSERT INTO JobLog (jobName, success, startTime, endTime) VALUES (@procedureName, 1, @startTime, GETDATE());
    END

--Cleanup temp tables
IF OBJECT_ID('tempdb.dbo.#tempTable1') IS NOT NULL  
    DROP TABLE #tempTable1;

IF OBJECT_ID('tempdb.dbo.##globalTempTable1') IS NOT NULL  
    DROP TABLE ##globalTempTable1;

Best Answer

I suspect what happened is that the row you expected to find was not found, perhaps because the name of the job was off, or because GETDATE() being evaluated multiple times will, once every so often, be evaluated on different seconds. So I'm not quite sure why you have a new job for every timestamp you're handling, but it's certainly plausible that it didn't match for this reason (just hard to reproduce on modern versions). See if #tmp ever gets populated:

SET NOCOUNT ON;
CREATE TABLE #tmp(i int);
GO

INSERT #tmp(i) SELECT o4.id + 0
 FROM sysobjects AS o0, sysobjects AS o1,  syscolumns AS o2, 
      sysobjects AS o3, syscomments AS o4, syscolumns AS o5
WHERE CONVERT(char(8), getdate(), 108) <> CONVERT(char(8), getdate(), 108);
GO 1000

SELECT i FROM #tmp;
GO

DROP TABLE #tmp;

If you get one or more rows, then you know getdate() was evaluated differently. If you don't, you were lucky, this time.

To avoid this possibility, assign getdate() to a variable:

UPDATE /*app table*/ 
   set dateprocessed = @currentDate 
 where jobname = 'Job_' 
       + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') 
       + LEFT(REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', ''), 4) 
       + '_' 
       + (SELECT Description FROM #tempTable1 WHERE id = @i);

Remember (not just you, but future readers too), try this on SQL Server 2000. If memory serves, older versions were much more susceptible to this, and it is unlikely you will reproduce on modern SQL Server.

Related Question