InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 22:13:53 GMT20http://www.sqlservercentral.com/Forums/Topic1015485-149-1.aspx[img]http://www.rlgf.info/16.jpg[/img][img]http://www.rlgf.info/13.jpg[/img][img]http://www.rlgf.info/14.jpg[/img][img]http://www.rlgf.info/17.jpg[/img][img]http://www.ryzu.info/10.jpg[/img]Thu, 15 Nov 2012 19:40:06 GMTstarshayizhttp://www.sqlservercentral.com/Forums/Topic1015485-149-1.aspxYou 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 [b]MAXERRORS = 0[/b]Wed, 14 Nov 2012 04:33:29 GMTdfinehttp://www.sqlservercentral.com/Forums/Topic1015485-149-1.aspxAfter 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.[left]BEGIN TRYBULK INSERT Sandbox_RTurner.dbo.TEMP_PEND_CLAIM_LINE_RTFROM 'E:\Decision Support\RTurner\temp_pend_claim_line_text.txt'WITH ( FIELDTERMINATOR = ',', TABLOCK ,ERRORFILE = 'E:\Decision Support\RTurner\PEND_CLAIM_LINE_RT' )END TRYBEGIN CATCHSELECT error_message(), error_severity(), error_state(), error_number(), error_procedure(), error_line()END CATCH;[/left]Thu, 04 Nov 2010 09:43:23 GMTrwturner13http://www.sqlservercentral.com/Forums/Topic1015485-149-1.aspxi would just add the following code into the CATCH block[code="sql"] SELECT ERROR_NUMBER() AS ErrorNumber , ERROR_MESSAGE() AS ErrorMessage [/code]I would think if you want to use the RAISERROR function you would use it like this[code="sql"]begin tryselect * from @table where 1 = 0IF @@ROWCOUNT = 0 BEGIN DECLARE @ErrorMessage VARCHAR(100) = 'test error' RAISERROR(@ErrorMessage ,16,1) ENDend trybegin catch SELECT ERROR_NUMBER() AS ErrorNumber , ERROR_MESSAGE() AS ErrorMessage end catch[/code]Thu, 04 Nov 2010 05:32:52 GMTdavidandrews13http://www.sqlservercentral.com/Forums/Topic1015485-149-1.aspxHi 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 TRYBEGIN 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 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 15 (Units).Thank you for the helpWed, 03 Nov 2010 12:21:16 GMTrwturner13