Try ... Catch Bulk Insert Errors

• Jacob Pressures

  SSCertifiable

  Points: 5804

  More actions

  October 23, 2018 at 11:08 am

  #375773

  Hello I have the code below but what might i put in my catch statement that wold be meaningful? The Try Catch is suppressing the errors which is what i want. But what should I do with the Catch?  Also i'm wondering under which conditions will it be executed because it is not being triggered based on errors in the file.

  Thanks!

  
   begin try
    -- Uploads data into staging table.
    insert into VendorUpcStaging
    (
        -- ID - This column value is auto-generated
        StoreID,
        VendorNumber,
        VendorName,
        ItemNumber,
        ProductDescription,
        UnitOfMeasure,
        Code   
    )
    select
        b.StoreID,    
        b.VendorNumber,
        b.VendorName,
        b.ItemNumber,
        b.ProductDescription,
        b.UOMs,
        b.Code
    from openrowset
    (
        bulk 'D:\Data\Open\MasterFile.txt',
        formatfile = 'E:\Data\Open\Format.xml',
        errorfile = 'E:\Data\Open\BadData.log',
        maxerrors = 1000000000,
        firstrow = 2
    ) as b
    end try
    begin catch
    print '****************************************** ERRORS FOUND ************************************************************'
    end catch

• epivaral

  SSC-Addicted

  Points: 428

  More actions

  October 23, 2018 at 11:46 am

  #2010832

  Hi Jacob,

  You can use the following system functions right after the BEGIN CATCH to display/handle the errors:

  
SELECT  
ERROR_NUMBER() AS ErrorNumber  
,ERROR_SEVERITY() AS ErrorSeverity  
,ERROR_STATE() AS ErrorState  
,ERROR_PROCEDURE() AS ErrorProcedure  
,ERROR_LINE() AS ErrorLine  
,ERROR_MESSAGE() AS ErrorMessage;


  if you want to use them various times on the catch, you must save them into variables first.

  regarding to the errors not being catched in your openrowset, you specified an error file, so any issue with the sentence will be stored onto the file.
  error not catched are, on this case, compilation errors (for example file does not exists), you can read more of errors not handled by try-catch here:

  https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-2017#errors-unaffected-by-a-trycatch-construct

  hope this helps!

  MCSE, MCSA SQL Server Database Developer/Administrator

• Jacob Pressures

  SSCertifiable

  Points: 5804

  More actions

  October 24, 2018 at 9:31 am

  #2010963

  Thanks Journeyman,
  I was wondering what to do with it. I guess I'll just leave the sql statement there. Even though its meaningless.
  Thanks for the info.

  If i missed something please let me know.