确定导致“意外的文件结束”的ROW。批量插入错误？ [英] Determine ROW that caused "unexpected end of file" error in BULK INSERT?

问题描述

我正在批量插入：

DECLARE @row_terminator CHAR;
SET @row_terminator = CHAR(10); -- or char(10)

DECLARE @stmt NVARCHAR(2000);
SET @stmt = '
  BULK INSERT accn_errors
   FROM ''F:\FullUnzipped\accn_errors_201205080105.txt''
   WITH 
      (
        firstrow=2,
FIELDTERMINATOR = ''|''  ,
ROWS_PER_BATCH=10000
   ,ROWTERMINATOR='''+@row_terminator+'''
   )'
exec sp_executesql @stmt;

，并收到以下错误：

Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2
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 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

有没有办法知道此错误发生在哪一行？

is there a way to know on which ROW this error occurred?

我能够导入10,000,000行而不会出现问题，并且此后会发生错误

i am able to import 10,000,000 rows without a problem and error occurs after that

推荐答案

麻烦的行使用错误文件说明符。

To locate the troublesome row use the errorfile specifier.

BULK INSERT myData
FROM 'C:\...\...\myData.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
ERRORFILE = 'C:\...\...\myRubbishData.log' 
);

myRubbishData.log将包含有问题的行和一个伴随文件
myRubbishData.log.txt将为您提供行号和文件的偏移量。

myRubbishData.log will have the offending rows and a companion file myRubbishData.log.txt will give you row numbers and offsets into the file.

伴侣文件示例：

Row 3 File Offset 152 ErrorFile Offset 0 - HRESULT 0x80004005
Row 5 File Offset 268 ErrorFile Offset 60 - HRESULT 0x80004005
Row 7 File Offset 384 ErrorFile Offset 120 - HRESULT 0x80004005
Row 10 File Offset 600 ErrorFile Offset 180 - HRESULT 0x80004005
Row 12 File Offset 827 ErrorFile Offset 301 - HRESULT 0x80004005
Row 13 File Offset 942 ErrorFile Offset 416 - HRESULT 0x80004005

这篇关于确定导致“意外的文件结束”的ROW。批量插入错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！