Share via

Stored Procedure not executing as SQL Agent Job

Bryan Lee Briggs 1 Reputation point
2021-06-30T18:09:33.473+00:00

I have a stored procedure (1) that updates JSON data (via JSON_Modify()) in an auditing table to replace raw binary with a short string 'Binary column data.'. This stored procedure functions when executed in SSMS, but when I try to have a SQL Agent Job execute the stored procedure it reports success but does nothing. No errors are produced, the Job is owned by (and set to execute as) a user with permissions to all relevant tables, the stored procedure itself, and is a member of the SQLAgentUserRole.

This stored procedure does generate the SQL statement(s) it executes via sp_executesql.

Please note, I also have another stored procedure (2) which also updates JSON data in the same auditing table to remove all un-changed keys and to create a list of changed keys using similar methods and also generating the SQL statement(s) it executes via sp_executesql, it also performs cleanup to remove records where nothing actually changed. This other stored procedure functions just fine when called by a SQL Agent Job.

Both stored procedures use applocks.
Both stored procedures use "dynamic" sql.
Both stored procedures use cursors to force row-level processing.

I have tried the following:

  1. Having stored procedure (2) call stored procedure (1) after it finishes its main processing.
  2. Having stored procedure (1) be called by a separate job with a mildly staggered schedule.
  3. Altering stored procedure (2) to include the contents of stored procedure (1) inline after it finishes its main processing.

In all cases the results were as follows:

  1. When manually executed in SSMS both stored procedures work as expected producing no errors.
  2. When included in a SQL Agent Job, the sql code from stored procedure (2) works, but the sql code from stored procedure (1) reports success, produces no errors, but does nothing.

Stored Procedure code:

Procedure (1):

CREATE PROCEDURE dbo.[ip_ReplaceAuditBinaryData]
AS

BEGIN
SET NOCOUNT ON;

DECLARE @RC INT;
DECLARE @MSG NVARCHAR(2000);
DECLARE @Id BIGINT;
DECLARE @UpdType VARCHAR(10);
DECLARE @TblName VARCHAR(50);
DECLARE @ColName VARCHAR(50);
DECLARE @JSONNew NVARCHAR(MAX);
DECLARE @JSONOld NVARCHAR(MAX);
DECLARE @SQLStatement NVARCHAR(MAX);

CREATE TABLE #IdsReplace (Id BIGINT, Upd_Type VARCHAR(10), Tbl_Name VARCHAR(50), JSONData NVARCHAR(MAX), OldJSONData NVARCHAR(MAX));

EXEC @RC = sp_getapplock @Resource = 'AuditReplaceBinaries', @LockMode = 'Exclusive', @LockOwner = 'Session', @LockTimeout = '0';

IF @RC >= 0
BEGIN
IF EXISTS (SELECT RowGUId FROM dbo.[Start] WHERE ReplaceAuditBinaryData = 1)
BEGIN
INSERT INTO #IdsReplace (Id, Upd_Type, Tbl_Name, JSONData, OldJSONData)
SELECT Id, Upd_Type, Tbl_Name, JSONData, OldJSONData
FROM dbo.[Audit] WITH (READUNCOMMITTED)
WHERE BinaryDataReplaced = 0
AND (
(Upd_Type IN ('INSERT', 'DELETE'))
OR
(Upd_Type = 'UPDATE' AND ColumnsChangedIfUpdate IS NOT NULL)
); 

     DECLARE ReplaceBinariesCursor CURSOR FAST_FORWARD  
     FOR SELECT Id, Upd_Type, Tbl_Name, JSONData, OldJSONData  
         FROM #IdsReplace  
       
     OPEN ReplaceBinariesCursor  
       
     FETCH NEXT FROM ReplaceBinariesCursor  
     INTO @Id, @UpdType, @TblName, @JSONNew, @JSONOld  
       
     WHILE @@FETCH_STATUS = 0        
     BEGIN  
        BEGIN TRY  
           BEGIN TRANSACTION  
              SET @SQLStatement = N'';  
                
              DECLARE ColumnCursor CURSOR FAST_FORWARD  
              FOR SELECT c.[name]  
                  FROM sys.Objects AS o  
                     INNER JOIN sys.Columns AS c ON o.[object_id] = c.[object_id]  
                  WHERE o.[schema_id] = 1   
                     AND o.[type] = 'U'   
                     AND o.[name] COLLATE SQL_Latin1_General_CP1_CI_AS = @TblName COLLATE SQL_Latin1_General_CP1_CI_AS   
                     AND c.system_type_id IN (165, 173)  
                
              OPEN ColumnCursor  
                
              FETCH NEXT FROM ColumnCursor  
              INTO @ColName  
                
              WHILE @@FETCH_STATUS = 0  
              BEGIN  
                 SET @SQLStatement = N'UPDATE dbo.[Audit]

SET '; 

                 IF @UpdType = 'INSERT'  
                 BEGIN  
                    IF @JSONNew LIKE '%"' + @ColName + '"%'  
                       AND @JSONNew NOT LIKE '%"' + @ColName + '":null%'  
                    BEGIN  
                       SET @SQLStatement = @SQLStatement + N'JSONData = JSON_MODIFY(JSONData, ''$.'' + ''' + @ColName + N''', ''Binary column data.''),   
'  
                    END  
                 END;  
                   
                 IF @UpdType = 'UPDATE'  
                 BEGIN  
                    IF @JSONNew LIKE '%"' + @ColName + '"%'  
                       AND @JSONNew NOT LIKE '%"' + @ColName + '":null%'  
                    BEGIN  
                       SET @SQLStatement = @SQLStatement + N'JSONData = JSON_MODIFY(JSONData, ''$.'' + ''' + @ColName + N''', ''Binary column data.''),   
'  
                    END;  
                      
                    IF @JSONOld LIKE '%"' + @ColName + '"%'  
                       AND @JSONOld NOT LIKE '%"' + @ColName + '":null%'  
                    BEGIN  
                       SET @SQLStatement = @SQLStatement + N'OldJSONData = JSON_MODIFY(OldJSONData, ''$.'' + ''' + @ColName + N''', ''Binary column data.''),   
'  
                    END;  
                 END;  
                   
                 IF @UpdType = 'DELETE'  
                 BEGIN  
                    IF @JSONOld LIKE '%"' + @ColName + '"%'  
                       AND @JSONOld NOT LIKE '%"' + @ColName + '":null%'  
                    BEGIN  
                       SET @SQLStatement = @SQLStatement + N'OldJSONData = JSON_MODIFY(OldJSONData, ''$.'' + ''' + @ColName + N''', ''Binary column data.''),   
'  
                    END  
                 END;  
                   
                 SET @SQLStatement = @SQLStatement + N'BinaryDataReplaced = 1

FROM dbo.[Audit] WITH (READUNCOMMITTED)
WHERE Id = ' + CAST(@Id AS NVARCHAR) + N';
'; 

                 EXEC sp_executesql @SQLStatement;  
                   
                 FETCH NEXT FROM ColumnCursor  
                 INTO @ColName  
              END  
                
              CLOSE ColumnCursor  
              DEALLOCATE ColumnCursor;  
                
           COMMIT TRANSACTION  
        END TRY  
        BEGIN CATCH  
           SET @Msg = N'ERROR: ' + ERROR_MESSAGE() + N' at ' + COALESCE(ERROR_PROCEDURE(), N'') + COALESCE(N' line: ' + CONVERT(NVARCHAR(30), ERROR_LINE()), N'');  
             
           RAISERROR (@Msg, 0, 1) WITH NOWAIT;  
             
           ROLLBACK TRANSACTION  
             
           RAISERROR (@Msg, 16, 1);  
        END CATCH;  
          
        FETCH NEXT FROM ReplaceBinariesCursor  
        INTO @Id, @UpdType, @TblName, @JSONNew, @JSONOld  
     END  
       
     CLOSE ReplaceBinariesCursor  
     DEALLOCATE ReplaceBinariesCursor;  
  END;  
    
  EXEC sp_releaseapplock @Resource = 'AuditReplaceBinaries', @LockOwner = 'Session';

END
ELSE
BEGIN
SET @RC = 50000
END;

DROP TABLE #IdsReplace;

IF @RC = 50000
BEGIN
RAISERROR (N'Procedure already running in another session.', 0, 1) WITH NOWAIT
END;

RETURN @RC
END;
GO

----------

Procedure (2):

CREATE PROCEDURE dbo.[ip_ProcessAuditUpdates]
AS

BEGIN
DECLARE @RC INT;
DECLARE @MSG NVARCHAR(2000);
DECLARE @Id BIGINT;
DECLARE @NewJSON NVARCHAR(MAX);
DECLARE @OldJSON NVARCHAR(MAX);
DECLARE @ChangesOnlyJSONNew NVARCHAR(MAX);
DECLARE @ChangesOnlyJSONOld NVARCHAR(MAX);
DECLARE @ColValToKey NVARCHAR(MAX);
DECLARE @DynamicChangeOnlyJSONCommandNew NVARCHAR(MAX);
DECLARE @DynamicChangeOnlyJSONCommandOld NVARCHAR(MAX);
DECLARE @ChangedColumns NVARCHAR(MAX);

CREATE TABLE #Ids (Id BIGINT);
CREATE TABLE #JSONDIFFNew (newKey SYSNAME, newVALUE NVARCHAR(MAX));
CREATE TABLE #JSONDIFFOld (newKey SYSNAME, newVALUE NVARCHAR(MAX));

EXEC @RC = sp_getapplock @Resource = 'AuditUpdateJob', @LockMode = 'Exclusive', @LockOwner = 'Session', @LockTimeout = '0';

IF @RC >= 0
BEGIN
INSERT INTO #Ids (Id)
SELECT Id
FROM dbo.[Audit] WITH (READUNCOMMITTED)
WHERE Upd_Type = 'UPDATE'
AND ColumnsChangedIfUpdate IS NULL; 

  DECLARE AuditUpdateCursor CURSOR FAST_FORWARD  
  FOR SELECT Id  
      FROM #Ids  
    
  OPEN AuditUpdateCursor  
    
  FETCH NEXT FROM AuditUpdateCursor  
  INTO @Id  
    
  WHILE @@FETCH_STATUS = 0        
  BEGIN  
     BEGIN TRY  
        BEGIN TRANSACTION  
           SELECT @NewJSON = JSONData, @OldJSON = OldJSONData  
           FROM dbo.[Audit] WITH (READUNCOMMITTED)  
           WHERE Id = @Id;  
             
           SET @ColValToKey = '';  
           SET @ChangedColumns = NULL;  
             
           DELETE FROM #JSONDIFFNew  
           FROM #JSONDIFFNew;  
             
           DELETE FROM #JSONDIFFOld  
           FROM #JSONDIFFOld;  
             
           INSERT INTO #JSONDIFFNew (newKey, newValue)  
           SELECT TheKey, TheValue  
           FROM dbo.fn_CompareJSONs(@NewJSON, @OldJSON)  
           WHERE SideIndicator <> '==';  
             
           INSERT INTO #JSONDIFFOld (newKey, newValue)  
           SELECT TheKey, TheValue  
           FROM dbo.fn_CompareJSONs(@OldJSON, @NewJSON)  
           WHERE SideIndicator <> '==';  
             
           IF (SELECT COUNT(*) FROM #JSONDIFFNew) = 0  
           BEGIN  
              SET @ChangesOnlyJSONNew = NULL;  
              SET @ChangesOnlyJSONOld = NULL;  
              SET @ChangedColumns = '';  
           END  
           ELSE  
           BEGIN  
              SET @ColValToKey = (SELECT ',MAX(CASE WHEN newKey = ''' + newKey + ''' THEN newValue END) AS ' + QUOTENAME(newKey) FROM #JSONDIFFNew FOR XML PATH(''));  
              SET @ColValToKey = STUFF(@ColValToKey, 1, 1, '');  
                
              SET @DynamicChangeOnlyJSONCommandNew = 'SELECT @ChangesOnlyJSONNew = (SELECT ' + @ColValToKey + ' FROM #JSONDIFFNew FOR JSON AUTO, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER)';  
                
              EXEC sp_executesql @DynamicChangeOnlyJSONCommandNew, N'@ChangesOnlyJSONNew NVARCHAR(MAX) OUTPUT', @ChangesOnlyJSONNew OUTPUT;  
                
              SET @DynamicChangeOnlyJSONCommandOld = 'SELECT @ChangesOnlyJSONOld = (SELECT ' + @ColValToKey + ' FROM #JSONDIFFOld FOR JSON AUTO, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER)';  
                
              EXEC sp_executesql @DynamicChangeOnlyJSONCommandOld, N'@ChangesOnlyJSONOld NVARCHAR(MAX) OUTPUT', @ChangesOnlyJSONOld OUTPUT;  
                
              SELECT @ChangedColumns = COALESCE(@ChangedColumns + ',' ,'') + '[' + newKey + ']'  
              FROM #JSONDIFFNew;  
           END;  
             
           UPDATE dbo.[Audit]  
           SET JSONData = @ChangesOnlyJSONNew,   
               OldJSONData = @ChangesOnlyJSONOld,   
               ColumnsChangedIfUpdate = @ChangedColumns  
           FROM dbo.[Audit] WITH (READUNCOMMITTED)  
           WHERE Id = @Id  
              AND ColumnsChangedIfUpdate IS NULL;  
             
        COMMIT TRANSACTION  
     END TRY  
     BEGIN CATCH  
        SET @Msg = N'ERROR: ' + ERROR_MESSAGE() + N' at ' + COALESCE(ERROR_PROCEDURE(), N'') + COALESCE(N' line: ' + CONVERT(NVARCHAR(30), ERROR_LINE()), N'');  
          
        RAISERROR (@Msg, 0, 1) WITH NOWAIT;  
          
        ROLLBACK TRANSACTION  
          
        RAISERROR (@Msg, 16, 1);  
     END CATCH;  
       
     FETCH NEXT FROM AuditUpdateCursor  
     INTO @Id  
  END  
    
  CLOSE AuditupdateCursor  
  DEALLOCATE AuditUpdateCursor;  
    
  DELETE FROM dbo.[Audit]  
  FROM dbo.[Audit]  
  WHERE JSONData IS NULL  
     AND OldJSONData IS NULL  
     AND ISNULL(ColumnsChangedIfUpdate, 'NULL') = ''  
     AND Upd_Type = 'UPDATE';  
    
  EXEC sp_releaseapplock @Resource = 'AuditUpdateJob', @LockOwner = 'Session';  
    
  IF EXISTS (SELECT RowGUId FROM dbo.[Start] WHERE ReplaceAuditBinaryData = 1)  
  BEGIN  
     EXEC dbo.[ip_ReplaceAuditBinaryData]  
  END;

END
ELSE
BEGIN
SET @RC = 50000
END;

DROP TABLE #Ids;
DROP TABLE #JSONDIFFNew;
DROP TABLE #JSONDIFFOld;

IF @RC = 50000
BEGIN
RAISERROR (N'Procedure already running in another session.', 0, 1) WITH NOWAIT
END;

RETURN @RC
END;
GO

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,106 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 103.9K Reputation points MVP
    2021-06-30T18:50:41.453+00:00

    Not seeing the code, we can only contribute with guess. What first comes to my mind is that your procedure includes non-deterministic code, and you get different results depending on the plan.

    This is something I have written about, if more from a performance perspective, in my article Slow in the Application - Fast in SSMS.

    But before you start reading this, I would recommend that you add some debug logging to your procedure, so that you can get a better idea of where more exactly it produces different results.

    0 comments
  2. Cris Zhan-MSFT 6,611 Reputation points
    2021-07-02T01:28:05.517+00:00

    Hi,

    Please try to add some debugging/check statements to determine whether the stored procedure is performing as expected. For example, in Procedure (1), is the data in dbo.[Audit] inserted into the temporary table #IdsReplace as expected?

    After some searching, I found that sometimes the problem points to the SQL Server Agent limits the size of text fields such as nvarchar(max).you may need to add SET TEXTSIZE -1 in job step before executing the stored procedure.

    Best Regards,
    Cris

    0 comments