# 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.
---start---
12.0
2
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
--end--

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

the command that was executed is.

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

--end--

and here is a sample of the data
--start--
L11056346|C3542425|
L1105638|C0015967|
L1105638|C0563594|
L11056420|C3554848|
L11056420|C3556751|
--end--

Can any one point me in the correct direction to make this work?
###### 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.

MSSQL 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.
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.
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
--Start--
CREATE TABLE tmp_LUI
(      [LUI] [varchar](10) NOT NULL,
[CUI] [varchar](10) NOT NULL
)-- ON [Metathesaurus_Data]

--the command that was executed is.

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

--end--

select * from tmp_lui

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

(5 rows affected)


And the Format file
12.0
2
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


Experts Exchange Solution brought to you by

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

MSSQL 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.
@Vitor,

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.
MSSQL 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.
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: