SQL Server 2016+ Bulk Insert使用格式文件导入失败求助

算力阿峰

2026-7-5

SQL Server BCP格式文件导致BULK INSERT失败排查

使用SQL Server 2016及以上版本

开发导入程序时，因需求需要创建动态格式文件。通过BCP生成格式文件后，使用其进行BULK INSERT时失败，报错如下：

Msg 4866, Level 16, State 17, Line 45
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7301, Level 16, State 2, Line 45
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

不使用格式文件时导入可正常完成。生成的格式文件采用制表符分隔和标准行终止符，内容如下：

14.0
19
1       SQLNCHAR            8       0       "\t"   1     Command                                          Latin1_General_CI_AS
2       SQLNCHAR            2       100     "\t"   2     Source                                           Latin1_General_CI_AS
3       SQLNCHAR            8       0       "\t"   3     CompanyImportID                                  Latin1_General_CI_AS
4       SQLNCHAR            2       100     "\t"   4     AccountNo                                        Latin1_General_CI_AS
5       SQLNCHAR            2       510     "\t"   5     Profile                                          Latin1_General_CI_AS
6       SQLINT              1       4       "\t"   6     CompanyActive                                    ""
7       SQLNCHAR            2       200     "\t"   7     CompanyName                                      Latin1_General_CI_AS
8       SQLNCHAR            2       510     "\t"   8     Admin_Email_Address                              Latin1_General_CI_AS
9       SQLNCHAR            2       100     "\t"   9     PaymentMethod                                    Latin1_General_CI_AS
10      SQLINT              1       4       "\t"   10    DefaultStockLocation                             ""
11      SQLINT              1       4       "\t"   11    ChargeCompanyVAT                                 ""
12      SQLNCHAR            2       20      "\t"   12    DefaultCurrency                                  Latin1_General_CI_AS
13      SQLINT              1       4       "\t"   13    EnforcePORef                                     ""
14      SQLINT              1       4       "\t"   14    FixedDeliveryAddress                             ""
15      SQLINT              1       4       "\t"   15    SendOrderConfirmations                           ""
16      SQLNCHAR            2       100     "\t"   16    DefaultPriceCode                                 Latin1_General_CI_AS
17      SQLNCHAR            2       100     "\t"   17    SpecialPriceCode                                 Latin1_General_CI_AS
18      SQLNCHAR            2       510     "\t"   18    DivCode1                                         Latin1_General_CI_AS
19      SQLNCHAR            2       510     "\n"   19    DivCode2                                         Latin1_General_CI_AS

生成格式文件及导入的示例代码：

-- Setup tables
DROP TABLE IF EXISTS dba.companies

CREATE TABLE [dba].[companies] ( [Command]                [NVARCHAR](MAX) NULL
,                                [Source]                 [NVARCHAR](50)  NULL
,                                [CompanyImportID]        [NVARCHAR](MAX) NULL
,                                [AccountNo]              [NVARCHAR](50)  NULL
,                                [Profile]                [NVARCHAR](255) NULL
,                                [CompanyActive]          [INT]           NULL
,                                [CompanyName]            [NVARCHAR](100) NULL
,                                [Admin_Email_Address]    [NVARCHAR](255) NULL
,                                [PaymentMethod]          [NVARCHAR](50)  NULL
,                                [DefaultStockLocation]   [INT]           NULL
,                                [ChargeCompanyVAT]       [INT]           NULL
,                                [DefaultCurrency]        [NVARCHAR](10)  NULL
,                                [EnforcePORef]           [INT]           NULL
,                                [FixedDeliveryAddress]   [INT]           NULL
,                                [SendOrderConfirmations] [INT]           NULL
,                                [DefaultPriceCode]       [NVARCHAR](50)  NULL
,                                [SpecialPriceCode]       [NVARCHAR](50)  NULL
,                                [DivCode1]               [NVARCHAR](255) NULL
,                                [DivCode2]               [NVARCHAR](255) NULL )

-- Build format file
DECLARE @strSQL NVARCHAR(MAX)

SELECT  @strSQL = N'EXEC xp_cmdshell ''bcp ' + DB_NAME () + N'.dba.companies format nul -n -f &quot;c:\examples\1_Companies-OK.fmt&quot; -t &quot;\t&quot; -r &quot;0x0a&quot; -T'''
EXEC sp_executesql @stmt = @strSQL

-- Read without format file - works fine
BULK INSERT dba.companies
FROM 'c:\examples\1_Companies-OK.txt'
WITH ( FIRSTROW = 2
,      ERRORFILE = 'c:\examples\1_Companies-OK_errors.txt'
,      FIELDTERMINATOR = '\t'
,      ROWTERMINATOR = '0x0a'
,      CODEPAGE = 65001 )

SELECT  *
  FROM  dba.companies

GO

-- Read with format file - unexpected EOF
BULK INSERT dba.companies
FROM 'c:\examples\1_Companies-OK.txt'
WITH ( FIRSTROW = 2
,      ERRORFILE = 'c:\examples\1_Companies-OK_errors.txt'
,      FORMATFILE = 'c:\examples\1_Companies-OK.fmt'   
,      CODEPAGE = 65001 )

SELECT  *
  FROM  dba.companies

尝试使用XML格式文件，命令如下：

bcp LatestCode.dba.companies format nul -n -f &quot;c:\examples\1_Companies-OK.fmt&quot; -x -r &quot;0x0a&quot; -T

但生成的XML格式文件在记录定义末尾多了一行无ID的字段，Bulk Insert会将其识别为必填属性：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="NCharPrefix" PREFIX_LENGTH="8" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="8" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="4" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="5" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="6" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
  <FIELD ID="7" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="200" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="8" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="9" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="10" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
  <FIELD ID="11" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
  <FIELD ID="12" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="20" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="13" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
  <FIELD ID="14" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
  <FIELD ID="15" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
  <FIELD ID="16" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="17" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="18" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="19" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
  <FIELD xsi:type="NCharTerm" TERMINATOR="\n" MAX_LENGTH="2" COLLATION="Latin1_General_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Command" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="2" NAME="Source" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="CompanyImportID" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="AccountNo" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="Profile" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="6" NAME="CompanyActive" xsi:type="SQLINT"/>
  <COLUMN SOURCE="7" NAME="CompanyName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="8" NAME="Admin_Email_Address" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="9" NAME="PaymentMethod" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="10" NAME="DefaultStockLocation" xsi:type="SQLINT"/>
  <COLUMN SOURCE="11" NAME="ChargeCompanyVAT" xsi:type="SQLINT"/>
  <COLUMN SOURCE="12" NAME="DefaultCurrency" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="13" NAME="EnforcePORef" xsi:type="SQLINT"/>
  <COLUMN SOURCE="14" NAME="FixedDeliveryAddress" xsi:type="SQLINT"/>
  <COLUMN SOURCE="15" NAME="SendOrderConfirmations" xsi:type="SQLINT"/>
  <COLUMN SOURCE="16" NAME="DefaultPriceCode" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="17" NAME="SpecialPriceCode" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="18" NAME="DivCode1" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="19" NAME="DivCode2" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

已尝试设置行终止符的常规建议，后续会提供数据文件链接，恳请提供解决建议。

内容的提问来源于stack exchange，提问作者Matthew Baker