SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Try Catch with Bulk Insert


Try Catch with Bulk Insert

Author
Message
rwturner13
rwturner13
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 171
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
davidandrews13
davidandrews13
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1929 Visits: 4641
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


rwturner13
rwturner13
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 171
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;


dfine
dfine
Right there with Babe
Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)

Group: General Forum Members
Points: 716 Visits: 597
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

Raj
starshayiz
starshayiz
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 0





hfreeman
hfreeman
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 46
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

Hank Freeman
Senior SQL Server DBA / Data & Solutions Architect
hfreeman@msn.com
678-414-0090 (Personal Cell)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search