BCP txt works fine under SQL 2000, fails under SQL 2008 R2

My destination table is on SQL Server 2008 R2.  The source server doing the BCP of the txt file is on SQL Server 2000.  All that works fine  (also the txt file imports into Excel2007 just fine).  I am going to replace the source server with a new server running SQL Server 2008 R2.  I am testing the new Source server (2008) going to a Test  server 2008 (copy of Prod. sever) to verify all is well.  All my BCPs are working fine in the new scenario (using a format file) except for one that is delimited and I use the ";" delimiter instead of the format file.

This is  the statement that works going from 2000 to 2008:

bcp db..TheNewKLOC in h:\download\AMAPS\KLOCfile.txt -c -t; -Usa -Ppw -Sserver -r\n -m100 -F2

That statement going from 2008 to 2008 produces this in the error file:

#@ Row 1, Column 7: Invalid character value for cast specification @#
A-BM2SAD             212             K-CS                 003      KBAD        00002000      10/03/2013           
#@ Row 2, Column 7: Invalid character value for cast specification @#
A-BM2SAD             CS              K-CS-50-6A           001      P           00002000      07/10/2014           
#@ Row 3, Column 7: Invalid character value for cast specification @#
...

Data in txt file is this:

A-BM2SAD       ;212       ;K-CS           ;003;KBAD  ;00002000;10/03/2013;      
A-BM2SAD       ;CS        ;K-CS-50-6A     ;001;P     ;00002000;07/10/2014;      
A-BM2SAD       ;LP        ;K-LP-8-2J      ;003;P     ;00002000;02/18/2015;      
...

The records in a hex editor (1st complete record highlighted):
RecsHex.JPG
I thought maybe I needed to tell it to act like SQL 2000  so I added "-V80" like this:

bcp db..TheNewKLOC in h:\download\AMAPS\KLOCfile.txt -c -V80 -t; -Usa -Ppw -Sserver -r\n -m100 -F2

The resulting error file from that statement is this:

#@ Row 1, Column 7: Invalid character value for cast specification @#
EDB34125RP           NF              K-NF-21-4A           001      S           00000036      /10/2014;      
EDB34125RP     ;NF        ;K-NF-5-3C      ;001;S     ;00000036;07/10/2014;      
EDB34125RP     ;NF        ;K-NF-5-3D      ;001;S     ;00000036;07/10/2014;      
EDB36015RP     ;NF        ;K-NF-16-4E     ;001;P     ;00000054;07/10/2014;      
EDB36020RP     ;NF600V    ;K-NF-16-3A     ;001;S     ;00000018;07/10/2014;      
EDB36030RP     ;NF600V    ;K-NF-16-3C     ;001;P     ;00000018;07/10/2014;      
EDPA           ;LP        ;K-LP-39-3B     ;006;P     ;00000150;02/18/2015;      
E      RP     ;LP        ;K-LP-29-2E     ;001;P     ;00000018;02/18/2015;      
#@ Row 2, Column 7: Invalid character value for cast specification @#
EJB34080RP           NF              K-NF-19-5D           001      S           00000018      /10/2014;      
EJB34080RP     ;NF        ;K-NF-5-2E      ;001;P     ;00000018;07/10/2014;      
EJB34100RP     ;LP        ;K-LP-29-2F     ;002;P     ;00000027;02/18/2015;      
EJB34100RP     ;LP        ;K-LP-29-2G     ;002;P     ;00000027;02/18/2015;      
EJB34100RP     ;NF        ;K-NF-19-4C     ;001;S     ;00000027;07/10/2014;      
EJB34100RP     ;NF        ;K-NF-5-2F      ;001;P     ;00000027;07/10/2014;      
EJB34125RP     ;LP        ;K-LP-31-3C     ;002;P     ;00000018;02/18/2015;      
E      B      ;ILI       ;ILF-Z1-3D      ;001;KBAD  ;00000003;12/04/2003;      
#@ Row 3, Column 7: Invalid character value for cast specification @#
FC24090AC            ILI             ILF-Z1-3E            001      KBAD        00000002      /04/2003;      
FC24090BC      ;ILI       ;ILF-Z1-3F      ;001;KBAD  ;00000003;12/04/2003;      
FC24100AB      ;ILI       ;ILF-Z1-3G      ;001;KBAD  ;00000001;12/04/2003;      
FC24100AC      ;ILI       ;ILF-Z1-3H      ;001;KBAD  ;00000002;08/22/2003;      
FC24100BC      ;ILI       ;ILF-Z1-3I      ;001;KBAD  ;00000003;12/04/2003;      
FC34020Z       ;ILI       ;ILP-23-4C,D,E  ;001;KBAD  ;00000006;02/11/2005;      
FC34025Z       ;ILI       ;ILP-23-4F      ;002;KBAD  ;00000006;02/11/2005;      
F       ;001;S     ;00000012;07/10/2014;      
#@ Row 4, Column 7: Invalid character value for cast specification @#

In that scenario it  start with records further down in the txt file, but the records also look fine in the txt file and in hex:

EDB34125RP     ;LP        ;K-LP-28-1B     ;002;P     ;00000036;02/18/2015;      
EDB34125RP     ;NF        ;K-NF-21-4A     ;001;S     ;00000036;07/10/2014;      
EDB34125RP     ;NF        ;K-NF-5-3C      ;001;S     ;00000036;07/10/2014;      
EDB34125RP     ;NF        ;K-NF-5-3D      ;001;S     ;00000036;07/10/2014;      
EDB36015RP     ;NF        ;K-NF-16-4E     ;001;P     ;00000054;07/10/2014;      
EDB36020RP     ;NF600V    ;K-NF-16-3A     ;001;S     ;00000018;07/10/2014;      
EDB36030RP     ;NF600V    ;K-NF-16-3C     ;001;P     ;00000018;07/10/2014;      
EDPA           ;LP        ;K-LP-39-3B     ;006;P     ;00000150;02/18/2015;      
EDPA           ;NF        ;K-NF-42-4B     ;001;S     ;00000150;07/10/2014;      
EDPAF          ;LP        ;K-LP-39-3C     ;003;P     ;00000100;02/18/2015;      

RecHex2.JPG
I find it interesting in the error file, where it sort of "Freaks out" and partial puts in a line that is not even the next line in the txt file (see 2nd line below):

EDPA           ;LP        ;K-LP-39-3B     ;006;P     ;00000150;02/18/2015;      
E      RP     ;LP        ;K-LP-29-2E     ;001;P     ;00000018;02/18/2015;    

^ that line should actually look like this:

EJB34080RP     ;LP        ;K-LP-29-2E     ;001;P     ;00000018;02/18/2015;      

I have also tried using the "-n"  argument in place of the "-c" and that was even worse.  It wrote nothing to the error file and only had error on the screen " SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0] Invalid field size for datatype"

The field sizes in the table are fine for the data in the txt file:

      [ItemNbr] [varchar](15) NULL,
      [ProdLine] [varchar](10) NULL,
      [Location] [varchar](15) NULL,
      [NbrCard] [varchar](3) NULL,
      [Type] [varchar](6) NULL,
      [Qty] [bigint] NULL,
      [AddDate] [date] NULL,
      [Filler] [varchar](10) NULL

Anyone have any ideas what might be causing this or how I can fix it?  

(I have no control of the txt file being sent to me)

Thanks
sqdperuAsked:
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.

jogosCommented:
Loock at the default collation of your database/instance
0
sqdperuAuthor Commented:
It is set at "SQL_Latin1_General_CP1_CI_AS" the same as the production server.  I never change this - I just use the default.

thanks
0
sqdperuAuthor Commented:
Sometimes I post my question on here and on the MSDN forum.  Usually the MSDN forum offers little help and my answer in found on here.  Not this time.  With the help from MSDN posters here is the solution I ended up using.


I tried to use Bulk Insert only to find out the .txt file must be on the server that is executing the query.  In my case, the .txt file is on my FTP server and where the .bat file is executing.  It seemed more of a hassle to set up a user account on the FTP server and a share and then map a drive to it on the SQL server or use "Net Use" command in the .bat file.

So instead I created a table (KLOC_BCPin) on SQL server that was like the table currently in use, but made all the fields varchar.  I was then able to BCP into that new table without issues.  After that I "INSERT INTO TheNewKLOC SELECT * FROM KLOC_BCPin" and everything is happy.
0

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
sqdperuAuthor Commented:
No one offered any working suggestions.  This is the solution that ended up working for me.
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.