COMMIT has no BEGIN error only when run as scheduled job?

Question

I have a stored procedure in SQL Server that purges records from a table, committing every 5000:

DECLARE AuditEventCursor CURSOR FOR
    SELECT E.EVENT_ID 
      FROM XI31AUDIT2..AUDIT_EVENT E 
     WHERE E.START_TIMESTAMP < GETDATE() - 14;

DECLARE @EventID VARCHAR(64);
DECLARE @DetailID INT;
DECLARE @i INT;
BEGIN

    --- Initialize variables
    SET @EventID = '';  
    SET @DetailID = -1;
    SET @i = 0;

    -- Open cursor for records to delete from AUDIT_EVENT
    OPEN AuditEventCursor;

    -- Fetch first EVENT_ID
    FETCH NEXT FROM AuditEventCursor 
    INTO @EventID;

    -- Loop while we still have records
    WHILE @@FETCH_STATUS = 0
    BEGIN

        DELETE FROM AUDIT_EVENT
        WHERE EVENT_ID = @EventID;

        -- Increment counter and commit every 5000
        SET @i = @i + 1;
        IF @i % 5000 = 0            
            COMMIT;

        -- Fetch next EVENT_ID
        FETCH NEXT FROM AuditEventCursor 
        INTO @EventID;
    END; 

    -- Close cursor
    CLOSE AuditEventCursor;
    DEALLOCATE AuditEventCursor;

END;

When I run this manually in SSMS, it runs just fine. However, when I run it as a Job in SQL Server Agent, it produces the following error:

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. [SQLSTATE 25000] (Error 3902).

What is the difference? I use the same command to execute: exec Purge_Audit

Thanks!

score 0 · Answer 1 · answered Sep 18 '14 at 14:44

Try this

-- Change value from 0 to 1
SET @i = 1;

-- Open cursor for records to delete from AUDIT_EVENT
OPEN AuditEventCursor;

-- Fetch first EVENT_ID
FETCH NEXT FROM AuditEventCursor 
INTO @EventID;

-- Loop while we still have records
WHILE @@FETCH_STATUS = 0
BEGIN

-- Begin new code
     IF @i % 5000 = 1
         BEGIN TRANSACTION 
-- End new code

COMMIT has no BEGIN error only when run as scheduled job?

1 Answers1