CODEWITHSUNDEEP
sql-serversql-server-2012
VenerableAgents
VenerableAgents

Reputation: 656

How to log multiple errors in TRY..CATCH?

My application runs SQL scripts to load in data and when there's a problem with the file it's loading, it only logs the last error which doesn't contain the useful information about why the file won't load.

Example code:

BEGIN TRY
    BULK INSERT MyTableName
    FROM 'C:\MyFilename.txt'
    WITH
    (
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '\n',
    TABLOCK, 
    MAXERRORS=0,
    ERRORFILE = 'C:\MyFilename_Errors.log'
    )
    ;
END TRY
BEGIN CATCH
    INSERT INTO MyErrorLog
    SELECT ERROR_MESSAGE() as Issue
    , ERROR_LINE() as IssueRowNum
    ;
END CATCH

This script will create an entry in [MyErrorLog] for the third error (see below). And the log file will tell me what line, but not what field:

Row 30539 File Offset 1910820 ErrorFile Offset 0 - HRESULT 0x80004005

Here's all 3 lines if I "THROW" the error inside the CATCH:

Msg 4864, Level 16, State 1, Line 3 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 5 (MyField).

Msg 7399, Level 16, State 1, Line 3 The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 3 Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

How do I capture that first (and second) error message?

Upvotes: 0

Views: 590

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

You can only capture a single error in a T-SQL CATCH block. According to this connect item, the workaround is to use THROW (or not use TRY/CATCH) and capture the errors in client code.

Consequently, you'll need to invoke the script from a client application (including SQLCLR) so that you can capture and log all errors.

Upvotes: 1

Related Questions