bcp:错误 = [Microsoft][SQL Server Native Client 10.0]字符串数据，右截断

问题描述

我最近在使用 bcp 时遇到了一个错误.这是错误.

SQLState = 22001，NativeError = 0 错误 = [Microsoft][SQL ServerNative Client 10.0]字符串数据，右截断

我正在尝试将数据解压缩到一个没有任何约束的临时表中，并且与数据相比，数据类型也相当大.我有大约 11 个来自不同表的文件被 bcp'd 并在解压缩错误时从其中只有一个文件压缩出来.这是我一直成功使用的命令.最近(尝试制作当前 WH 的副本并设置流程时)我遇到了一些问题.

bcp.exe employee_details in employee_details.dat -n -E -S "servername"-U sa -P "密码"

我尝试将命令更改为 -C -T -S，当我手动提供格式时，该命令有效.这是一个非常大且重要的数据包，我需要将其加载到我的 WH 中.
我不知道我是否在这里看到了格式文件.需要任何帮助.

谢谢

肉桂女孩.

解决方案

我们在做 BCP 时也遇到了同样的问题，结果是 .dat 文件中的换行符问题.

在Notepad++中查看文件，点击显示所有字符"即可看到换行符.

BCP 使用 -r "" 选项(即使用以下命令)引发以下错误

bcp dbo.Test in C:Test.dat -c -t "|"-r "
" -S "数据库服务器名" -T -E

" SQLState = 22001，NativeError = 0 错误 = [Microsoft][SQL ServerNative Client 10.0]字符串数据，右截断"

BCP 将文件中的所有行视为带有 -r "" 或 -r "" 选项的单行，即使用以下命令

bcp dbo.Test in C:Test.dat -c -t "|"-r "
" -S "数据库服务器名称" -T -E

当我们在 BCP 命令中为换行符使用十六进制值 (0x0a) 时问题已解决

bcp dbo.Test in C:Test.dat -c -t "|"-r "0x0a" -S "DBServerName" -T -E

I have recently encountered an error while working with bcp.Here is the error.

I'm trying to unpack the data into a staging table which does not have any constraints and the datatypes are also fairly large when compared to the data. I have about 11 files from different tables being bcp'd and zipped out of which only one file when unpacking errors out.This is the command which I have been using succesfully. Very recently(when trying to make a copy of the current WH and settign up the process) I have been facing issues.

I have tried changing the commands to -C -T -S which worked when I gave the format manually. This is a very big and important packet I need to load in to my WH.
I don't know if I see a format file here or not.Any help is needed.

Thanks

Cinnamon girl.

解决方案

We also faced same issue while doing BCP and it turned out to be an issue with new line character in .dat file.

View the file in Notepad++ and click on "Show All Characters" to see the new line character.

BCP throws following error with -r "" option i.e. with below command

bcp dbo.Test in C:Test.dat -c -t "|" -r "
" -S "DBServerName" -T -E

BCP treat all rows in file as a single row with -r "" or -r "" option i.e. with below command

bcp dbo.Test in C:Test.dat -c -t "|" -r "
" -S "DBServerName" -T -E

Issue was resolved when we used the Haxadecimal value (0x0a) for New Line character in BCP command

bcp dbo.Test in C:Test.dat -c -t "|" -r "0x0a" -S "DBServerName" -T -E

这篇关于bcp:错误 = [Microsoft][SQL Server Native Client 10.0]字符串数据，右截断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！