BULK INSERT, errorfile, and TRY/CATCH siutation

Question

I’m trying to get a handle on this situation. I’m I missing something, or is there another way to deal with it?

 

SQL Server 2005, sp2, Developer edition

 

I’m doing a bulk insert. I want to trap and control errors, so that my ETL routine doesn’t crash and burn. So I have

 

BEGIN TRY

BULK INSERT MyTable

 from "C:\Temp\MyFile" 

 with (

   firstrow = 2 

  ,fieldterminator = '|' 

  ,rowterminator = '\n' 

  ,tablock 

  ,maxerrors = 0

  )

END TRY

BEGIN CATCH

    --  Generate meaningful error message

END CATCH

 

As anyone who’s ever tried to do this knows, you cannot easily generate a “meaningful error message” with what you get back from error_message() et. al. in that CATCH block from a failed BULK INSERT command. So now I’m looking into the files created by the bulk insert errorfile switch, e.g.

 

BULK INSERT MyTable

 from "C:\Temp\MyFile" 

 with (

   firstrow = 2 

  ,fieldterminator = '|' 

  ,rowterminator = '\n' 

  ,tablock 

  ,maxerrors = 0

  ,errorfile = 'C:\Temp\MyFile_BadData.log'

  )

 

When there’s bad data, the bulk insert fails and (as per BOL) creates two files:

   C:\Temp\MyFile_BadData.log

   C:\Temp\MyFile_BadData.log.Error.Txt

 

and the CATCH block runs and I can do cool stuff. Fine and good. However, if I run the bulk insert, and if either or both of those error files exists, not only do I get an error (4861, a level 16 error), but--the kicker--the TRY/CATCH fails to work. Execution does not pass to the catch block, and in fact does not even pass to the next line of code. It just stops cold and dies.

 

Has anyone else experienced this? Is there a fix or work-around?

 

Answer 1

TRY/CATCH does not catch most errors you will experience in this kind of process.

Short of writing an SSIS package, you will never be able to handle errors with this kind of load.

Answer 2

Do you know why this is? Is it just bulk inserts, a "family" of commands, or what? (I can CATCH 7330 data conversion errors, it's just trying to pull out why the data won't load that's stopping me.)

 

Forwarned against oddball behavior is forarmed against tomorrow's coding problems...

 

Answer 3

Philip,

You can catch the following way below. The CATCH is, you have to double up the TRY-CATCH logic within nested stored procedures..

Let us know if helpful.

Code Snippet

-- inner sproc where error occurs

create

proc sprocBetaBulk

as

begin

BEGIN

TRY

BULK

INSERT MyTable

from "C:\Temp\MyFile"

with (

firstrow = 2

,fieldterminator = '|'

,rowterminator = '\n'

,tablock

,maxerrors = 0

)

END

TRY

BEGIN

CATCH

print 'CATCH ERROR sprocBetaBulk'

END

CATCH

END

go

-- outer catching sproc

create

 proc sprocAlphaBulk

as

begin

BEGIN

TRY

exec

sprocBetaBulk

END

TRY

BEGIN

CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber

,ERROR_SEVERITY() AS ErrorSeverity

,ERROR_STATE() AS ErrorState

,ERROR_PROCEDURE() AS ErrorProcedure

,ERROR_LINE() AS ErrorLine

,ERROR_MESSAGE() AS ErrorMessage;

END

CATCH

END

go

exec

sprocAlphaBulk

go

The result:

ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
208               16               82             sprocBetaBulk 6            Invalid object name 'MyTable'.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 4

That error is a "terminal error". Which means you can do nothing.  The command, and stored proc simply terminate.

Although, the suggested method SQLUSA proposes does work, if you notice the first CATCH on the BULK INSERT never executed.  It is actually trapping the the nested stored proc failed.

You will be much happier if you write an SSIS package.