无法确定大量插入错误的原因 [英] Can't identify reason for BULK INSERT errors

问题描述

我正在尝试运行此查询(我也没有指定FIELDTERMINATOR和ROWTERMINATOR对其进行了尝试).它使用的是我事先手动创建的数据文件(而不是bcp输出).

I'm trying to run this query (I also tried it without specifying FIELDTERMINATOR and ROWTERMINATOR). It's using a datafile that I am manually creating beforehand (not with bcp out).

BULK INSERT FS.dbo.Termination_Call_Detail
FROM 'C:\Termination_Call_Detail__1317841711.dat'
WITH
(
 FORMATFILE = 'C:\Termination_Call_Detail__update_TerminationCallDetailData.fmt',
 FIELDTERMINATOR = '\t',
 ROWTERMINATOR = '\r\n'
)

我得到的错误:

服务器消息号= 4864严重性= 16状态= 1行= 1 服务器= USA109MSSQL1A
text =批量加载数据转换错误(类型不匹配或无效) 指定代码页的字符) 对于第1行，第9列(RouterCallKey).

Server message number=4864 severity=16 state=1 line=1 server=USA109MSSQL1A
text=Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 9 (RouterCallKey).

服务器消息号= 4832严重性= 16状态= 1行= 1 服务器= USA109MSSQL1A
text =批量加载:在 数据文件.

Server message number=4832 severity=16 state=1 line=1 server=USA109MSSQL1A
text=Bulk load: An unexpected end of file was encountered in the data file.

服务器消息号= 7399严重性= 16状态= 1行= 1 服务器= USA109MSSQL1A
text =链接服务器(null)"的OLE DB提供程序"BULK" 报告了错误.
提供程序未提供有关该错误的任何信息.

Server message number=7399 severity=16 state=1 line=1 server=USA109MSSQL1A
text=The OLE DB provider "BULK" for linked server "(null)" reported an error.
The provider did not give any information about the error.

服务器消息号= 7330严重性= 16状态= 2行= 1 服务器= USA109MSSQL1A
text =无法从OLE DB提供程序"BULK"获取行以进行链接 服务器(空)".

Server message number=7330 severity=16 state=2 line=1 server=USA109MSSQL1A
text=Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

我正试图让它只处理1行.

I'm trying to get it working with just 1 row.

ASCII数据文件:

ASCII datafile:

611658275664    1                       5027    5001    5005    5010    2011-10-05 14:53:44     1317840824      240     2011-10-05 14:54:04     1317840844      150026  211     2       1       4480    21543   28      0       29      0       0       0       0       29      0       0                       0       IPT_Call        TAC_Rollover                                                                    424755/4        N       0       5030    5000    2865                    805336626                       2674794299      N                                               1       3       N       0               0

数据文件的十六进制转储: http://pastebin.com/grnwQrdT

Hex dump of the datafile: http://pastebin.com/grnwQrdT

格式文件: http://pastebin.com/WwSQvCGg

表架构: http://pastebin.com/gFjp4xYy

此插入使用66列；该表包含84列.如果我使用整个表的bcp和同一行的数据文件创建格式文件，则可以毫无问题地运行此BULK INSERT QUERY.

This insert is using 66 columns; the table contains 84 columns. If I create a format file with bcp of the whole table, and a datafile of this same row, I can run this BULK INSERT QUERY without a problem.

由bcp在同一行数据(包含所有列)上创建的数据文件的十六进制转储: http://pastebin .com/MpMk6iq4

Hex dump of a datafile created by bcp on the same row of data (with all the columns): http://pastebin.com/MpMk6iq4

我认真地寻找任何缺少的换行符或制表符分隔符，但我找不到任何换行符或制表符分隔符.您可以在数据文件的十六进制转储中看到所有看起来正确分隔的东西.我将其与bcp创建的数据文件进行了比较，找不到任何格式差异.

I meticulously looked for any missing newlines or tab delimiters, but I cant find any. You can see on the hex dump of the datafile that everything looks properly delimited. I compared it to a datafile created by bcp and couldn't find any formatting differences.

我也无法想象为什么它抱怨那个特定字段(RouterCallKey)-格式文件中在它上面的其他字段具有相同的类型(例如RouterCallKeyDay).

I also cant imagine why its complaining about that particular field (RouterCallKey) - there are other fields above it in the format file that are the same type (like RouterCallKeyDay).

谢谢！

推荐答案

您不知道吗，在感到沮丧后就提出来了，我马上就弄清楚了.在代码中的其他地方存在一个逻辑错误，该错误创建了数据文件，该数据文件以错误的形式提供了此批量插入查询

Wouldnt you know it, I figure it out right after I get frustrated enough to post the question. There was a logical bug elsewhere in the code that created the datafile that was feeding this bulk insert query the wrong form

这篇关于无法确定大量插入错误的原因的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！