bcp:错误= [Microsoft] [SQL Server Native Client 10.0]字符串数据，右截断 [英] bcp: Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation

问题描述

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

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

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

SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation

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

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.

employee_details.dat中的bcp.exe employee_details -n -E -S服务器名" -U sa -P密码"

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

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

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.

谢谢

肉桂女孩.

推荐答案

在执行BCP时，我们也遇到了同样的问题，事实证明这是.dat文件中换行符的问题.

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

在Notepad ++中查看文件，然后单击显示所有字符"以查看换行符.

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

BCP使用-r"\ r \ n"选项(例如，使用以下命令)引发以下错误

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

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

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

" SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation "

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

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

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

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

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

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