T-SQL Command not entering CATCH-CodePudding

I have a sql agent job that runs a job step to import an xml file into a table. This is working fine. I want to have a running log file to record info and errors along the way. Currently, the first insert into my Logs table works. If my OPENROWSET command fails, i.e. 1Events.xml was not available, the sql agent job returns a failure, which is fine. However, how can I get the insert into the log table to occur within the CATCH block before exiting?

BEGIN TRY
    INSERT INTO Logs (Message, Level, TimeStamp)
    SELECT 'Attempting to insert contents of Events.xml into sql table (XML_Events)', 'INFO', GETDATE();
   
    INSERT INTO XML_Events(XMLData, LoadedDateTime)
    SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
    FROM OPENROWSET(BULK '\\servername\c$\Events.xml', SINGLE_BLOB) AS x;
   
    INSERT INTO Logs (Message, Level, TimeStamp)
    SELECT 'Successfully inserted contents of Events.xml into sql table (XML_Events)', 'INFO', GETDATE();
END TRY
BEGIN CATCH
     INSERT INTO Logs (Message, Level, TimeStamp)
    SELECT 'Error inserting contents of Events.xml into sql table (XML_Events)', 'ERROR', GETDATE();
END CATCH

CodePudding user response：

This is, as Stu has (just) alluded to, one of those errors that can't be "caught" easily. One method you could use, however, is to use a defered statement and execute it inside sys.sp_executesql; this'll result in the behaviour you want:

BEGIN TRY
    INSERT INTO dbo.Logs (Message, Level, TimeStamp)
    SELECT 'Attempting to insert contents of Events.xml into sql table (XML_Events)', 'INFO', GETDATE();
    
    DECLARE @SQL nvarchar(MAX),
            @CRLF nchar(2) = NCHAR(13)   NCHAR(10);
    SET @SQL = N'INSERT INTO dbo.XML_Events(XMLData, LoadedDateTime)'   @CRLF  
               N'SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()'   @CRLF  
               N'FROM OPENROWSET(BULK ''\\servername\c$\Events.xml'', SINGLE_BLOB) AS x;';

    EXEC sys.sp_executesql @SQL;
   
    INSERT INTO dbo.Logs (Message, Level, TimeStamp)
    SELECT 'Successfully inserted contents of Events.xml into sql table (XML_Events)', 'INFO', GETDATE();
END TRY
BEGIN CATCH
     INSERT INTO dbo.Logs (Message, Level, TimeStamp)
     SELECT 'Error inserting contents of Events.xml into sql table (XML_Events)', 'ERROR', GETDATE();
     --Throw; --?
END CATCH

Note that without some kind of error being raised in the CATCH, SQL Agent won't know that the batch failed, hence the --Throw; --? in the CATCH.