Post reply

Bulk Insert Truncation and Try Catch

  • March 22, 2022 at 1:26 pm

    #4004179

    I ran into an issue where using bulk insert to load a text file with a field having a length greater than the length specified in the table will not be picked up as an error using try...catch. Here is an example of what I mean. The text file is attached and is tab delimited and has a new line character row terminator.

     

    CREATE TABLE Test.dbo.TestImport (
    	ID VARCHAR(10)
    	,[NAME] VARCHAR(10)
    	,CITY VARCHAR(10)
    	,[STATE] VARCHAR(10)
    	)

    You can see Timothy Plummer has a length of 15 characters in the text file but the table only allows up to 10 characters. When I run the bulk insert wrapped in a try...catch then it imports two rows and excludes Timothy but does not throw an error about truncation.

    DECLARE @cmd VARCHAR(500)

    TRUNCATE TABLE Test.dbo.TestImport

    BEGIN TRY
    	SET @cmd = 'BULK INSERT Test.dbo.TestImport
       FROM ''C:\Users\username\Desktop\TestData.TXT''
       WITH 
          (
            FIELDTERMINATOR = ''\t'',
            ROWTERMINATOR = ''\n'',
    	FIRSTROW=2,
    	KEEPNULLS,
    	TABLOCK
          )'

    	EXEC (@cmd)
    END TRY

    BEGIN CATCH
    	SELECT 'Bulk load error ' + ERROR_MESSAGE()
    		,17
    		,2
    END CATCH

    However, if I remove the try...catch then it will still import the same two records but also provides the error message

    Msg 4863, Level 16, State 1, Line 11

    Bulk load data conversion error (truncation) for row 3, column 2 (NAME).

    TRUNCATE TABLE Test.dbo.TestImport

    BULK INSERT Test.dbo.TestImport
    FROM 'C:\Users\username\Desktop\TestData.TXT' WITH (
    		FIELDTERMINATOR = '\t'
    		,ROWTERMINATOR = '\n'
    		,FIRSTROW = 2
    		,KEEPNULLS
    		,TABLOCK
    		)

    I need it to throw an error and stop processing rather than just exclude it and proceed. Any idea how to achieve that?

    Attachments:
    You must be logged in to view attached files.

  • March 22, 2022 at 6:10 pm

    #4004357

    Adding MAXERRORS = 0 to the bulk insert statement causes it to fail which is what I need. Trying to get a meaningful error to display in the catch is another issue but at least I have found the cause to the original problem.

  • March 28, 2022 at 5:48 am

    #4006863

    Most people won't even try it because it uses xp_CmdShell but if you use it to call SQLCmd to do the same thing and capture the output of the xp_CmdShell, the errors are captured just as if you were reading them of the screen and are pretty easy to pull apart.

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply