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.

I can reproduce your issue perfectly if I change the line endings to merely CR (Macintosh) or merely LF (Unix) rather than the CR + LF that is usual on Windows. Verify your files use the correct line endings everywhere. (I can also import the file just fine if all the lines do end in CR + LF.) You may also want to verify there are no invisible spacing characters possibly gumming up the works, although that seems less likely. — Jeroen Mostert, May 24 '18 at 18:45
@JeroenMostert this was my thought also, sounds like either the line endings have been changed or else the these file has been converted from ascii to unicode. — RBarryYoung, May 24 '18 at 18:55
@RBarryYoung: Unicode was my second idea (UTF-8 with a BOM, in fact) but in this case BCP fails on the first column of the first row only, not the third. UTF-16 with no BOM makes it fail with an EOF. (Even so, it may be worth checking the code page and/or explicitly specifying it with `-C`, as that could also be an issue, though admittedly not for the ASCII-compatible data presented here.) — Jeroen Mostert, May 24 '18 at 19:00
@JeroenMostert This sounds incredibly feasible. I believe a co-worker added the files to a repo and replaced all the Windows style line endings with unix ones. I'm going to do some testing to see if this solves the issue! — Lokno, May 24 '18 at 19:10
Should that be the issue, see also [this question](https://stackoverflow.com/q/724083/4137916), and if specifically the repo is a Git repo, see [this one](https://stackoverflow.com/a/4683783/4137916). ...and I almost forgot, but if you're not really interested in changing the files, using `-r 0x0a` should instruct BCP to use only LF as a row terminator. (Not sure if that works with the 2008R2 BCP, though.) — Jeroen Mostert, May 24 '18 at 19:17
@JeroenMostert I've tested it and that was totally the problem. The command works perfectly if I either switch the file to CR + LF endings or use the additional flag -r 0x0a THANKS SO MUCH!!! — Lokno, May 24 '18 at 19:28
I'm sure this question has to be a duplicate of some other bcp question, given that you can't possibly be the first one to come across this on SO, but I'll be damned if I can find an actual duplicate. I'll cook up an answer just in case. — Jeroen Mostert, May 24 '18 at 19:31

score 3 · Answer 1 · answered May 24 '18 at 19:34

#@ 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).

Errors using BCP with SQL Server 2008 R2

1 Answers1