T-SQL Merge not updating in explicit transaction

We have the following code in a stored procedure which has a merge statement and an update statement to a different table in an explicit transaction. The merge update does not update any data in the inventory.gtin table but the update statement below it works and data is updated in the import.file_data table when the stored procedure is called in a batch job. But, when the stored procedure is executed manually, the merge works fine and updates/inserts data. Is this a bug in the merge statement?

DECLARE @targetVendorCode nvarchar(20)
DECLARE gtin_cursor CURSOR FOR  

        SELECT DISTINCT aVendorCode
        FROM #fileData fd


    OPEN gtin_cursor   
    FETCH NEXT FROM gtin_cursor INTO @targetVendorCode
    WHILE @@FETCH_STATUS = 0   
    BEGIN   

    BEGIN TRANSACTION;

    MERGE [inventory].[gtin] AS target  
    USING (SELECT * FROM #fileData WHERE ngtin IS NOT NULL AND aVendorCode = @targetVendorCode ) AS source
    ON (target.[ngtin] = source.[ngtin])
    WHEN MATCHED AND ( ( source.createDateTime > COALESCE(target.fileCreateDateTime,'04/02/1982') ) OR source.[status] = 'override' ) THEN
        UPDATE SET 
        [skuId] = source.[skuId],  
        [lastUpdateFileId] = source.[fileId],  
        [vendorCode] = COALESCE(source.[aVendorCode],source.[vendorCode]),  
        [lastUpdateDateTime] = source.[lastUpdateDateTime],     
        [fileCreateDateTime]= source.createDateTime
    WHEN NOT MATCHED BY TARGET THEN 
        INSERT (
                [skuId],
                [lastUpdateFileId],
                [vendorCode],
                [lastUpdateDateTime],
                [fileCreateDateTime]
                )  
        VALUES (
                source.[skuId],
                source.[fileId],
                COALESCE(source.[aVendorCode],source.[vendorCode]),
                source.[lastUpdateDateTime],
                source.createDateTime
                ) 
            ;           
        SELECT @action = '[{"type":"distribute","actionDateTime":"' + CONVERT(VARCHAR(19), GETUTCDATE(), 120) + '","insertDateTime":"' + CONVERT(VARCHAR(19), GETUTCDATE(), 120) + '","referenceId":' + TRY_CAST(@fileId AS NVARCHAR(20)) + '}]'

        UPDATE fd SET [status] = 'processed',[modifiedDateTime] = GETUTCDATE(),[modifiedUserName] = SYSTEM_USER FROM [import].[file_data] fd WHERE fileId = @fileId


    IF @@TRANCOUNT > 0
    BEGIN
        COMMIT TRANSACTION;
    END
    ELSE
    BEGIN
        ROLLBACK TRANSACTION;
    END

    FETCH NEXT FROM gtin_cursor INTO @targetVendorCode
    END   

    CLOSE gtin_cursor   
    DEALLOCATE gtin_cursor

You MERGE is probably failing due to permissions issues to access the [gtin] tabale. , and then proceeding to the UPDATE statement.

These won’t solve access issues, but you won’t get funky results:

Add TRY and CATCH blocks as appropriate for your needs (inside the cursor to skip just the offending item, or outside to fail the whole thing along with a ROLLBACK).

Or just add SET XACT_ABORT ON at the top to fail whole thing when there is an error, rolling back if in the transaction.