Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Try Catch with Bulk Insert Expand / Collapse
Author
Message
Posted Wednesday, November 03, 2010 12:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 9:15 AM
Points: 3, Visits: 158
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
Post #1015485
Posted Thursday, November 04, 2010 5:32 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 551, Visits: 3,165
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 try

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

Post #1015781
Posted Thursday, November 04, 2010 9:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 9:15 AM
Points: 3, Visits: 158
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 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

SELECT error_message(),
error_severity(),
error_state(),
error_number(),
error_procedure(),
error_line()


END CATCH;

Post #1015987
Posted Wednesday, November 14, 2012 4:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 1:42 AM
Points: 337, Visits: 441
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


Joe
Post #1384534
Posted Thursday, November 15, 2012 7:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 7:33 PM
Points: 9, Visits: 0




Post #1385421
Posted Thursday, February 06, 2014 10:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 11, 2014 11:34 AM
Points: 9, Visits: 28
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
ID ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage DateTimeStamp
1 7330 16 2 usp_ProcessDailyCEData_Error 60 CSV 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
Post #1538756
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse