USING TRY...CATCH WITH OPENROWSET...BULK

• Sam Bendayan

  SSC-Addicted

  Points: 479

  More actions

  January 25, 2012 at 1:47 pm

  #249728

  Greetings,

  Having a problem with OPENROWSET...BULK and the way it handles error messages. I am trying to import a file using OPENROWSET...BULK which has several column values that are too long and it's failing with column truncation errors. I get about 15 error messages when I run the SQL in SSMS, something like this:

  Msg 4863, Level 16, State 1, Line 1

  Bulk load data conversion error (truncation) for row 1, column 16 (TceCreatePayType).

  Msg 4863, Level 16, State 1, Line 1

  Bulk load data conversion error (truncation) for row 2, column 16 (TceCreatePayType).

  Msg 4863, Level 16, State 1, Line 1

  Bulk load data conversion error (truncation) for row 3, column 16 (TceCreatePayType).

  Msg 4863, Level 16, State 1, Line 1

  Bulk load data conversion error (truncation) for row 4, column 16 (TceCreatePayType).

  Msg 4863, Level 16, State 1, Line 1

  Bulk load data conversion error (truncation) for row 5, column 16 (TceCreatePayType).

  Msg 4863, Level 16, State 1, Line 1

  Bulk load data conversion error (truncation) for row 6, column 16 (TceCreatePayType).

  Msg 4863, Level 16, State 1, Line 1

  Bulk load data conversion error (truncation) for row 7, column 16 (TceCreatePayType).

  Msg 4863, Level 16, State 1, Line 1

  Bulk load data conversion error (truncation) for row 8, column 16 (TceCreatePayType).

  Msg 4863, Level 16, State 1, Line 1

  Bulk load data conversion error (truncation) for row 9, column 16 (TceCreatePayType).

  Msg 4863, Level 16, State 1, Line 1

  Bulk load data conversion error (truncation) for row 10, column 16 (TceCreatePayType).

  Msg 4863, Level 16, State 1, Line 1

  Bulk load data conversion error (truncation) for row 11, column 16 (TceCreatePayType).

  Msg 4865, Level 16, State 1, Line 1

  Cannot bulk load because the maximum number of errors (10) was exceeded.

  Msg 7399, Level 16, State 1, Line 1

  The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

  Msg 7330, Level 16, State 2, Line 1

  Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

  However, if I wrap this in a TRY...CATCH block, I only see the last message in the above list:

  Msg 50000, Level 16, State 1, Procedure spErrorHandler, Line 47

  Error 7330, Level 16, State 2, Procedure , Line 2, Message: Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

  Is there any way to display ALL the error messages by OPENROWSET...BULK using TRY..CATCH?

  Thanks,

  SB

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  January 25, 2012 at 10:25 pm

  #1438567

  No... I don't believe there is.

  However, if you use BULK INSERT instead of OPENROWSET, you can route all of the errors to an error file and import that, as well.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions