Bulk Insert Truncation and Try Catch

Question

Bulk Insert Truncation and Try Catch

RonMexico

Hall of Fame

Points: 3474
More actions
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.

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

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

RonMexico Hall of Fame Points: 3474 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

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)

Bulk Insert Truncation and Try Catch

Attachments: