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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.