Errors using BCP with SQL Server 2008 R2

Question

I'm working with an old codebase that sets up a SQL database with test data. The code has some batch files and SQL files that set things up, and I can't figure out why it's failing to add the data. Importantly, this is part of an install process I have used before which previously worked without issue. I've since reinstalled Windows (Windows 10 Enterprise, previously Windows 7 Pro). The project uses SQL Server 2008 R2 (10.50.4042.0).

Here's the SQL that creates the table

PRINT 'Creating Table VersionHistory'
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VersionHistory](
    [Id] [uniqueidentifier] NOT NULL,
    [VersionNumber] [varchar](50) NOT NULL,
    [VersionSequence] [int] NOT NULL,
 CONSTRAINT [PK_VersionHistory] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

And here's the input data (tab delimited) :

42E8397A-F913-402C-9F5D-013B0D78215C    1.0.1.13060 1
8BA50B6A-1813-42D5-A7F0-2EEC55D8A7CC    1.0.11.15222    13
B296B89F-CF3E-4A68-8A19-3440B67AEB84    1.0.9.14324 11
3299D4CD-E949-464B-B65B-4597FA5ED9D0    1.0.10.15091    12
ADE81361-41B9-42F8-BBF8-4A9A3E32AF2F    1.0.5.13331 7
4EFB3E84-DE22-4D34-BD7B-7E7294540487    1.0.1.13087 2
5AF55EE2-8D35-43F8-929C-9AD4BA256EC6    1.0.8.14233 10
97F98EAF-2CC1-42A2-A331-9B5E62AD52DF    1.0.3.13234 6
B31E1B3C-C556-431B-BE69-BF49758FF649    1.0.7.14146 9
1AA59F90-5F90-4058-8359-CBF79405649B    1.0.6.14056 8
46C3FD09-2E9E-4BBF-B800-D19DFB730A17    1.0.1.13150 5
D69E8D07-76DF-4698-ADBB-D48A7B8290B7    1.0.1.13105 3
31C975EA-FEF5-482D-B022-E631F56159CE    1.0.1.13116 4

A batch file attempts to add the input data with the following line:

bcp CLIENTDB.dbo.VersionHistory in "H:\PATH\TO\FILE\VersionHistory_Data.txt" -S .\sql2008r2 -U user -P password -E -c -e errors.txt

And this is failing with the following error:

Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification

And in the error file:

#@ Row 1, Column 3: Invalid character value for cast specification @#

So it sounds like it's bulking on casting the integer type in the file. This is also just an example of the problem at large, which is dozens of tables being added in this way, and they all fail for some reason. There are some bcp calls that don't produce any errors, but only one row is added.

I made sure the verison of bcp being used was installed by SQL Server 2008

> where bcp
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe

So an answer that explains what needs to be changed to get this call to work correctly would be an adequate solution. An answer that suggest why this might have once worked without issue, or shows conclusively that it would have never worked, would be incredible. Thank you for your time.

---

FAQ:

Q: Why SQL Server 2008 R2

A: It's what the original project used, I'm operating on a tight time schedule, there are dozens of sql files and stored procedures I don't feel confident I have time to investigate what would need to be changed to use a newer version of SQL Server.

UPDATE:

Solution

The tab delimited data files previously had CR+LF line endings before being committed a repo and switched to unix-style line endings. I had realized this had happened to some files, but did not know it had happened to the entire codebase at commit time. The BCP commands executes without issue if the files are returned to CR+LF endings, or if the additional flag -r 0x0a is applied to the BCP command.

Answer 1

#@ Row 1, Column [last column number]: Invalid character value for cast specification @# may be an indication that your line endings are wrong -- LF or CR rather than the Windows default of CR+LF.

This question discusses how to convert the files. This question discusses how to fix things in Git, if that is the source of your erroneously changed files.

Without changing files, the simplest solution is to pass -r 0x0a or -r 0x0d to BCP, which will make it eat pure LF or pure CR as a row terminator (respectively).