如何判断 SQL 2k5 BCP 在导入失败时抱怨的是哪个字段？
在输出日志上导入失败之前，我收到错误 Error - [Microsoft][SQL Native Client] String data, right truncation 大约 10 次。该错误与其相关的字段有点模糊。通常，如果数据集真正处于第三范式，这不会成为问题。但是，该数据集具有超过 125 个字段，包含超过 100,000 条记录（作为制表符分隔的 txt 文件）。在这种情况下，找到有问题的数据将是一项艰巨的任务。 如何判断 SQL 2k5 BCP 在导入失败时抱怨哪个字段？如果我能找出哪个字段出错，这将极大地帮助缩小问题范围。
bcp db.dbo.table in "dataset.txt" -f "fmt.table.txt" -S instancename -U user -P password -o log.txt
是的，有一个更大的故事，但为了简单起见，我将其限制在这个特定问题上。否则，进一步需要提出另一个问题。 :)
I am getting the error Error - [Microsoft][SQL Native Client] String data, right truncation about 10 times before the import fails out on the outpu log. The error is a bit ambigous to which field in which it is related. Normaly this wouldnt' be a problem if the dataset was in truly in 3rd normal form. However, the dataset has over 125+ fields with over 100,000 records as a tab delimited txt file. In this case, it will be quite an undertaking to find the offending data. How do you tell which field SQL 2k5 BCP is complaining about in a failed import? If I could find out which field is erroring out, this will greatly help narrow down the problem.
bcp db.dbo.table in "dataset.txt" -f "fmt.table.txt" -S instancename -U user -P password -o log.txt
Yes, theres a bigger story, but am limmiting it to this specific problem for simplicity sake. Otherwise going further will require another question to be asked. :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
将空排序规则添加到格式文件中。即
我导入行终止符之间没有任何内容的文件以获取空值。
确保包含连续的选项卡。
Add an empty collation to the format file. i.e.
I import files with nothing between the row terminators to get null.
Make sure consecutive tabs are included.
问题出现在第 112 个字段上，该字段被定义为 char(1)，并且提取的数据实际上是“”，每条记录的长度均为 2。为此，我将数据作为制表符分隔文件导入 Excel，将每个字段设置为文本（这一点至关重要，因为 Excel 自动将日期字段转换为数值），并将文本限定符从 " 设置为无（Excel 的另一个转换功能），将其设置为工作表 1。我使用
=len(Sheet1!A1)、=len(Sheet1!A2),=len(Sheet1!A3)等（感谢上帝自动填充）并将shema信息粘贴到第三张纸上，以比较每个字段的最大长度=max(Sheet2!A1: A126324）。基本问题在于提取数据的方式。这就是我的做法，但我感觉这远不是一个好方法......
The problem was found on the 112th field where the field was defined as char(1) and where the data that was extracted was literally "" giving a length of 2 all the way down each record. I did this by importing the data as tab-delimited file into Excel setting each field as text (crucial given that Excel automatically converts date fields to a numeric value) and set the text qualifier from " to none (another conversion feature Excel does) and set it to sheet 1. I check the field length of every datum in the dataset on the second sheet using
=len(Sheet1!A1),=len(Sheet1!A2),=len(Sheet1!A3), etc (thank god for auto fill) and pasted the shema information on the third sheet to compare the max length of each field=max(Sheet2!A1:A126324). The base problem lies in the way the data was extracted.This is how I did it, but I get the feeling this is far from a good way of doing it....