Try Catch with Bulk Insert
-
Hi All,
I am having some trouble getting the following code to work, in 2005. I am trying to load a 1000 records, with one bad record, my goal is to trap the error information about that one bad record and finish the inserting the remaining 999 reords. The current code will insert 999 records, logs an error to the BULK INSERT ERRORFILE but the catch and raiserror statements do nothing.
BEGIN TRY
BULK INSERT
Sandbox_RTurner.dbo.TEMP_PEND_CLAIM_LINE_RT
FROM
'E:\Decision Support\RTurner\temp_pend_claim_line_text.txt'
WITH
(
FIELDTERMINATOR = ',',
TABLOCK
,ERRORFILE = 'E:\Decision Support\RTurner\PEND_CLAIM_LINE_RT'
)
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
The following error is generated if I run the bulk insert without using the try and catch statement.
This is the error information I am trying to capture;
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 15 (Units).
Thank you for the help
-
i would just add the following code into the CATCH block
SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage
I would think if you want to use the RAISERROR function you would use it like this
begin tryselect * from @table where 1 = 0
IF @@ROWCOUNT = 0
BEGIN
DECLARE @ErrorMessage VARCHAR(100) = 'test error'
RAISERROR(@ErrorMessage ,16,1)
END
end try
begin catch
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage
end catch
-
After reviewing raiserror some more, I do not think it is what I need here. I simply need to trap the error that is being thrown when the insert kicks out any records. I modified the code and I still am getting nothing in the catch statement.
BEGIN TRYBULK INSERT
Sandbox_RTurner.dbo.TEMP_PEND_CLAIM_LINE_RT
FROM
'E:\Decision Support\RTurner\temp_pend_claim_line_text.txt'
WITH
(
FIELDTERMINATOR = ',',
TABLOCK
,ERRORFILE = 'E:\Decision Support\RTurner\PEND_CLAIM_LINE_RT'
)
END TRY
BEGIN CATCH
SELECT error_message(),
error_severity(),
error_state(),
error_number(),
error_procedure(),
error_line()
END CATCH;
-
You have to include MAXERRORS option in your BULK INSERT statement.
The default MAXERRORS value is 10.
Since you have only one error, it was not caught by the TRY CATCH block.
So, in order to capture the error in catch block you change the MAXERRORS = 0
[font="Calibri"]Raj[/font] -
-
Here is an example of my code which is working... The Error line you see below has a comman delimiter so I can produce the error because the CSV file is tab delimited. thus producing this error message
IDErrorNumberErrorSeverityErrorStateErrorProcedureErrorLineErrorMessageDateTimeStamp
17330162usp_ProcessDailyCEData_Error60CSV File Bulk_Insert:Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".2/6/2014 8:55:22 AM
-- Here is my code
Truncate table [dbo].[DailyReportData];
Begin Try
begin
--- The CSV file must have all the columns
BULK INSERT [dbo].[DailyReportData]
FROM 'C:\CEData\Daily_CE_Dispenser_Status_Report.csv'
-- WITH (MAXERRORS = 0,FIELDTERMINATOR = '\,', ROWTERMINATOR = '', FIRSTROW = 4); --error line
WITH (MAXERRORS = 0,FIELDTERMINATOR = '\t', ROWTERMINATOR = '', FIRSTROW = 4);
PRINT '.. Completed: Bulk Insert Daily_CE_Dispenser_Status_Report.csv into [dbo].[DailyReportData].'
end
End Try
Begin CATCH
begin
INSERT INTO UserExitData.dbo.ProcessCSV_Error
(ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, ErrorMessage, DateTimeStamp)
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
'CSV File Bulk_Insert: ' + ERROR_MESSAGE() as ErrorMessage,
GetDate() as DateTimeStamp;
Print('** Bulk Insert Process error: see table dbo.ProcessCSV_Error for details.**')
goto ExitOnError
end
end catch
Hank Freeman
Senior SQL Server DBA / Data & Solutions Architect
hfreeman@msn.com
678-414-0090 (Personal Cell)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply