October 8, 2010 at 7:38 am
When I use BULK INSERT with TRY CATCH Block, only the last error message is caught in the TRY CATCH block. But, last message does not signifies the original error. I tried to set SET XACT_ABORT ON, to stop at the first error itself, but, I am still getting the last error message.
BULK INSERT TestDB.dbo.Table1
FROM 'C:\Table1.dat'
WITH
(
BATCHSIZE = 10000,
CODEPAGE = 'RAW',
KILOBYTES_PER_BATCH = 8,
FIELDTERMINATOR ='\t',
ROWTERMINATOR = ''
)
Is there a way, where I can capture
a. The whole error message like below:
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 5 (PatientName).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 5 (PatientName).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 3, column 5 (PatientName).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 4, column 5 (PatientName).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 5, column 5 (PatientName).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 6, column 5 (PatientName).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 7, column 5 (PatientName).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 8, column 5 (PatientName).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 9, column 5 (PatientName).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 10, column 5 (PatientName).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 11, column 5 (PatientName).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
b. Atleast the first error
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 5 (PatientName).
Thanks in advance for your help.
October 8, 2010 at 9:39 am
I don't have an answer except to suggest that you use bcp utility instead. It provides for saving a detailed error message regarding the data issue that is failing.
We use bulk insert with great success but I run a pre-processor on the raw data first, perform validity checks and any needed conversions so that the data that bulk insert sees is always clean.
That's my .02.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply