SQL SERVER BULK INSERT ERROR NOT HANDLED

Inherited maintenance of  a stored procedure used in a daily sql agent job.  
A BULK INSERT statement failed in it because a file drive letter changed (without my knowledge).  
I corrected the letter drive and all is well.  
But I'm curious as to why it blew up and the error handler was not invoked.  
I tried playing around with both Try/Catch and @@Error to see what would happen.
They both just blow up.  
How can I circumvent the blowing up  and get a nice error handler to handle it?



--Using  Try/Catch
BEGIN TRY
      IF OBJECT_ID('tempdb..#Import') IS NOT NULL
            DROP TABLE #Import

      CREATE TABLE #Import (FileData VARCHAR(10))

      BULK INSERT #Import FROM 'X:\MyFolder\MyFile.txt'
      WITH (MAXERRORS = 0, ROWTERMINATOR = ';',  FIRSTROW=1, LASTROW=1)
END TRY
BEGIN CATCH
      SELECT 'Error'
END CATCH

---------------------------------------------------------------------------------------------------------------------------

--Using @@Error

DECLARE @Error BIGINT

IF OBJECT_ID('tempdb..#Import') IS NOT NULL
      DROP TABLE #Import

CREATE TABLE #Import (FileData VARCHAR(10))

BULK INSERT #Import FROM 'X:\MyFolder\MyFile.txt'
WITH (MAXERRORS = 0, ROWTERMINATOR = ';', FIRSTROW=1, LASTROW=1)

SELECT @Error = @@Error

IF @Error > 0
      BEGIN
            SELECT 'Error'
      END
MariaHaltAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Interesting. I already knew that TRY...CATCH block doesn't handle some type of errors but went to investigate this situation further and found this article that somebody wrote about this specific situation as well. I think it's all explained there.
Looks like the workaround is to verify before the existence of the file.
1
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
If I am not mistaken, the only "return" that the caller would get is whether the BULK INSERT was successful or was cancelled. Error handling is controlled by various parameters of the BULK INSERT, namely:
- ERRORFILE
- MAXERRORS
- MAXERRORS

However, this will not provide a mechanism to handle the condition that you ran into (invalid file path). You would probably need some sort of a validation on the file path and access rights that runs before calling BULK INSERT.

Again, I may be mistaken here - my experience with BULK INSERT is limited and there may be various tricks that other experts may be using. I would be eager to learn about these as well.
0
 
Mark WillsTopic AdvisorCommented:
Error handling for BULK INSERT sucks.

No other way of putting it.

You could investigate OPENROWSET( BULK ... ) a little bit better with its errorfile handling.

Or if contemplating xp_cmdshell then use the BCP utility.
1
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Mark WillsTopic AdvisorCommented:
Oh, I have done this before :
IF OBJECT_ID('tempdb..#Import') IS NOT NULL
      DROP TABLE #Import

CREATE TABLE #Import (FileData VARCHAR(10))

exec ('BULK INSERT #Import FROM ''X:\MyFolder\MyFile.txt''
WITH (MAXERRORS = 0, ROWTERMINATOR = '';'', FIRSTROW=1, LASTROW=1)')

select @@error, *
from sys.messages where message_id = @@error and language_id = 1033

Open in new window

1
 
MariaHaltAuthor Commented:
I did what the article said, use dynamic sql and was able to catch the error.  Thank you.
0
 
Mark WillsTopic AdvisorCommented:
Sorry, what Article are you talking about ?
0
 
Mark WillsTopic AdvisorCommented:
Found it... Shows up on a green background... And Erland is a demi-god in my books - not just "somebody" but a well known and highly respected MVP :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.