CODEWITHSUNDEEP
sql-serversql-server-2008bulkinsert
Adam Rackis
Adam Rackis

Reputation: 83376

Odd Bulk Insert Error

I'm trying to debug a SQL Server 2008 bulk insert, and I'm getting errors that don't really add up.

The T-SQL is

BULK INSERT [dbo].SYNC_OFFERDISCOUNTS 
FROM 'c:\inetpub\ftproot\ecometrydev\offer-discounts.csv' 
WITH (BATCHSIZE = 500, FIRSTROW = 2, ROWTERMINATOR = '\n', FIELDTERMINATOR ='||^^||')

And my errors are

Msg 4863, Level 16, State 1, Line 3 Bulk load data conversion error (truncation) for row 2, column 72 (ACTION_TYPE).

Msg 4863, Level 16, State 1, Line 3 Bulk load data conversion error (truncation) for row 3, column 72 (ACTION_TYPE).

Msg 4863, Level 16, State 1, Line 3 Bulk load data conversion error (truncation) for row 4, column 72 (ACTION_TYPE).

And so on, up to row 12, at which point 10 errors are reached, and the whole thing is shut down. My database schema and the partial contents of the CSV are below. It looks to me like a single character I is getting inserted for ACTION_TYPE, so I'm not sure why this error is happening.

EDIT

Especially frustrating is the fact that there are only 71 columns in the file, and the last column of the file corresponds to ACTION_TYPE.

/EDIT

I've already tried increasing this column to varchar(50), with the thought that maybe the legacy system that created this csv was slipping in some hidden unicode nonsense, but that changed nothing.

I know there's a lot of crap here. I've already counted over all those zeroes time and again, and it always ends up that that single I character is exactly slotted to go into the ACTION_TYPE column. I'm hoping there's some obscure error somewhere that an expert here can spot.

CSV

DISCOUNTTABLE||^^||MULTIQTY_001||^^||MULTIQTY_002||^^||MULTIQTY_003||^^||MULTIQTY_004||^^||MULTIQTY_005||^^||MULTIQTY_006||^^||MULTIQTY_007||^^||MULTIQTY_008||^^||MULTIQTY_009||^^||DISCOUNTPERCENT_001||^^||DISCOUNTPERCENT_002||^^||DISCOUNTPERCENT_003||^^||DISCOUNTPERCENT_004||^^||DISCOUNTPERCENT_005||^^||DISCOUNTPERCENT_006||^^||DISCOUNTPERCENT_007||^^||DISCOUNTPERCENT_008||^^||DISCOUNTPERCENT_009||^^||DISCOUNTITEM_001||^^||DISCOUNTITEM_002||^^||DISCOUNTITEM_003||^^||DISCOUNTITEM_004||^^||DISCOUNTITEM_005||^^||DISCOUNTITEM_006||^^||DISCOUNTITEM_007||^^||DISCOUNTITEM_008||^^||DISCOUNTITEM_009||^^||DISCOUNTQTY_001||^^||DISCOUNTQTY_002||^^||DISCOUNTQTY_003||^^||DISCOUNTQTY_004||^^||DISCOUNTQTY_005||^^||DISCOUNTQTY_006||^^||DISCOUNTQTY_007||^^||DISCOUNTQTY_008||^^||DISCOUNTQTY_009||^^||DISCOUNTPRICE_001||^^||DISCOUNTPRICE_002||^^||DISCOUNTPRICE_003||^^||DISCOUNTPRICE_004||^^||DISCOUNTPRICE_005||^^||DISCOUNTPRICE_006||^^||DISCOUNTPRICE_007||^^||DISCOUNTPRICE_008||^^||DISCOUNTPRICE_009||^^||DISCOUNTDOL_001||^^||DISCOUNTDOL_002||^^||DISCOUNTDOL_003||^^||DISCOUNTDOL_004||^^||DISCOUNTDOL_005||^^||DISCOUNTDOL_006||^^||DISCOUNTDOL_007||^^||DISCOUNTDOL_008||^^||DISCOUNTDOL_009||^^||ITEMQUANTITY_001||^^||ITEMQUANTITY_002||^^||ITEMQUANTITY_003||^^||ITEMQUANTITY_004||^^||ITEMQUANTITY_005||^^||ITEMQUANTITY_006||^^||ITEMQUANTITY_007||^^||ITEMQUANTITY_008||^^||ITEMQUANTITY_009||^^||DESC1||^^||DISCOUNTUPSELL||^^||USERID||^^||DATE||^^||TIME||^^||STATUS||^^||ROWACTION||^^||
0006||^^|| 3000||^^|| 999999999||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 2000||^^|| 2000||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^||20% OFF $30+ ORDER ||^^||SAVE 20% ON $30+ ORDER ||^^||LKWILHEL||^^||20110214||^^||11141050||^^||%D||^^||I||^^||
0023||^^|| 5000||^^|| 999999999||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 1500||^^|| 1500||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^||15% OFF $50+ ORDER ||^^||SAVE 15% ON $50+ ORDER ||^^||LKWILHEL||^^||20110214||^^||11013470||^^||%D||^^||I||^^|| 0049||^^|| 4600||^^|| 999999999||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 3000||^^|| 3000||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^|| 0||^^||30% OFF $46+ ORDER ||^^||SAVE 30% ON $46+ ORDER ||^^||LKWILHEL||^^||20110214||^^||11384810||^^||%D||^^||I||^^||

Columns

enter image description here

Upvotes: 0

Views: 3696

Answers (2)

Shlomi
Shlomi

Reputation: 11

Try removing the SLOTID column and it should fix the problem. Assuming SLOTID does not exist as a column in the source file.

Upvotes: 1

Patrick Kelly
Patrick Kelly

Reputation: 33

Determine what the End Of Line (EOL) character(s) is. If the end of line is \r\n (and you've only specified \n) then it could be that the bulk insert is reading into the next line and attempting to truncate what it believes to be the ACTION_TYPE.

Use Notepad++ to find out what the EOL character is. Click View > Show Symbol > Show End of Line.

Upvotes: 2

Related Questions