1

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;
  • How do you know it didn't happen vs. it didn't find the row? If it didn't find the row, do you really want SQL Server to call that an error? If an update fails, you will get an error (but it's also very easy to write code that will ignore it in one way or another). – Aaron Bertrand Oct 19 '18 at 19:19
  • Ouch, SQL 2000, eh? Got some ancient applications I see. – clifton_h Oct 20 '18 at 4:58
1

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.

  • thanks so much for the help! I ran the query you suggested on SQL Server 2000 many times and #tmp was never populated, but it did prompt me to look into how long this query took to execute when this happened (I had already done that, but hadn't put two and two together). The update statement didn't update any records because the job name didn't match, since this query started at 0720, but by the time it finished it was 0721). This was the first time the SP ever took longer than 1 minute to execute, which explains why it had never happened before. Now assigning GETDATE() to a variable! – Cameron Brister Oct 21 '18 at 0:14

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.