SQL server 2014 BCP SQLState = 22001, NativeError = 0

I am trying to BCP a text file using a File template  (FMT) file.  and when I execute the command i get this error

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 281

This is the content of the FMT file.
1       SQLCHAR             2       10      "|"   1     LUI        SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       8       "|\r\n"   2     CUI        SQL_Latin1_General_CP1_CI_AS

This is the format of the target table in sql server
      [LUI] [varchar](10) NOT NULL,
      [CUI] [varchar](8) NOT NULL
) ON [Metathesaurus_Data]

the command that was executed is.

 bcp dateabase.[dbo].[LUI]  IN C:\Documents\LUI.txt  -f C:\document\LUI.fmt -T -S Secure.server.local


and here is a sample of the data

Can any one point me in the correct direction to make this work?
TylerSR. Data Systems AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You just need to have one record in the text file with the incorrect size to trigger that error. You really need to check in all file which row has oversized values.
TylerSR. Data Systems AdministratorAuthor Commented:
I loaded the file in excel then ran this value in a blank cell in the column  =MAX(LEN(A1:A193199)) the returned results was this
9.00      8      so the field values are with in the tolerance, there is something that is causing this error and i suspect its an enomely in the way BCP is handling the FMT file upon the data file.
Mark WillsTopic AdvisorCommented:
Well, I can import using BCP....

Couple of small tweaks.

For a start, you are using field and row terminators, so dont be afraid to make your format file more flexible....
Secondly make sure your data file is clean and finished with the \r\n that you have told it (might have been something with the download, but it seemed as if I was missing a carriage return)
Third, make your receiving table more flexible - I often (if not always) import to a staging table first - just a plain table with a unique name.

Here's what I did
(      [LUI] [varchar](10) NOT NULL,
      [CUI] [varchar](10) NOT NULL
)-- ON [Metathesaurus_Data]

--the command that was executed is.

exec master..xp_cmdshell 'bcp  ee.[dbo].[tmp_LUI]  IN C:\mrwtest\ee\LUI.txt  -f C:\mrwtest\ee\LUI.fmt -T '


select * from tmp_lui

LUI        CUI
---------- ----------
L11056346  C3542425
L1105638   C0015967
L1105638   C0563594
L11056420  C3554848
L11056420  C3556751

(5 rows affected)

Open in new window

And the Format file
1       SQLCHAR             0       20      "|"            1     LUI        SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       20      "|\r\n"        2     CUI        SQL_Latin1_General_CP1_CI_AS

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Then it maybe considering the pipe "|" as character and not a separator. You can test this with a text file with a single row to see if the error also occurs.
Mark WillsTopic AdvisorCommented:

Yes, that can happen when the format file isnt flexible enough to accommodate the data elements - if too short it might not pick up the terminators and you end up with unexpected inclusions (such as '|') so the format file needs to consider all the characteristics of a field length - especially when delimited - you dont want it cut off before it gets to the delimiters.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Mark, IMHO there's no enough information to consider a valid answer for this question as the user never return with a feedback.
I suggest to delete this question instead.
Mark WillsTopic AdvisorCommented:
As a reference to closing, see the support pages : http://support.experts-exchange.com/customer/portal/articles/2527982

The first step in that process is
In order to close a question, a user must:
Mark the comments you found helpful
So, I do believe there was a return to the question and marked #a42574228 as Helpful.

What happened next is unknown (and not the first time the Close process has been confusing), but the intentions are clear.

There is good information in the thread and the BCP + Format is an often confusing beast. It definitely does not warrant deleting.

It is just as easy to say that Tyler did get the answer and now happily using BCP. Tyler did return to mark an answer as helpful.

I will again recommend the closure.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.