April 29, 2003 at 8:47 am
I need to trap errors occuring in BULK INSERT, such as wrong file format, etc. When such an error occurs, I need to have the calling script continue execution and handle the error. According to BOL, I ought to be able to do this, but I have not been able to get it to work.
Example code:
BEGIN TRAN
BULK INSERT <tablename> FROM <filename> WITH (BATCHSIZE=100000, DATAFILETYPE='char', FIELDTERMINATOR='|')
-- Here's where I need to trap the error
-- @@ROWCOUNT didn't seem to work
-- @@ERROR didn't seem to work either
IF @@ERROR <> 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
Any ideas?
Doug Brashear
Doug Brashear
April 29, 2003 at 10:09 am
One reason could be that the bulk insert is using default MAXERRORS 10. Hence it is not aborting on the first error.
Try specifying MAXERRORS = 0 in the WITH clause. Then @@ERROR might work.
April 29, 2003 at 11:21 am
I added MAXERRORS=0 to the WITH clause and ran it again. This is what I get:
-----------------------------------------------------
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 5 (YrInDcd).
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.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.
-----------------------------------------------------
Still the same behavior, but it only repeats the error once.
Doug Brashear
Doug Brashear
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply