'T-SQL Command not entering CATCH
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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
