Bulk Insert Error Trapping?

Bulk Insert Error Trapping?

Post by Brian Sheere » Tue, 03 Oct 2000 04:00:00



I'm having trouble trapping errors using Bulk Insert. From my SP:


BULK INSERT cxbalow
FROM '\\Caviar\esql\import\cixmirror\cxbalow.unl'
WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK);


However if the cxbalow.unl file is corrupt the SP appears to exit
without returning an error code. If I run the following in the Query
Analyzer (which is exactly the same as in my DTS Execute SQL Task), the
Query Analyzer reports an error, but the last line never runs.:





What is returned by the QA is:

Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 209811,
column 7 (end_date).
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give
any information about the error.
The statement has been terminated.

How can I trap such an error?

Thanks for your input.

Brian Sheeres

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Bulk Insert Error Trapping?

Post by Neil Pik » Wed, 04 Oct 2000 04:00:00


Brian - you can't - it's unfortunate that SQL Server's error handling doesn't
allow you to get control back on these occasions.


 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq

 
 
 

1. BULK INSERT and Error Trapping

Is it possible to trap errors that come off of the BULK INSERT command?  I

INSERT if something is wrong during the BULK INSERT.  What I trying to do is
if SQL Server is unable to find a file that it can insert or unable to load
the file due to an error in the file, then I want it to throw up an error so
I know rather than just have the program die right there.  Any ideas?
Thanks.

2. Help for a Non Profit

3. Bulk Insert - Trapping Error

4. VB3 Database book

5. BULK INSERT does not allow error trapping.

6. Remote Stored Procedures and the Task Schedular

7. Error when bulk insert follows another large bulk insert

8. way to turn off all constraint/trigger checking during file transfer?

9. Error Trapping - Can you trap a DTS Package error using SQLDMO running a job

10. Error Trapping: MS Access errors not trapped in VB code

11. Bulk Delete (as opposed to Bulk Insert)

12. BULK INSERT or BULK COPY

13. Bulk Insert / Bulk copy