BCP IN Fail when ported to different server

Question

BCP IN Fail when ported to different server

irobertson

Hall of Fame

Points: 3695
More actions
October 21, 2009 at 9:34 am

#227281

Hi,
I'm runnning the following BCP command on my dev server:
exec xp_cmdshell 'bcp Database.dbo.tblTable IN "MyTextFile.txt" -c -T -t, -S MyServerName -k '
Sample import row:
411189907,461881477,0,04-Aug-2001,,,,1,1
It works just fine. But, on my client's throws the error:
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
When he runs it on his exact copy of the database on his server.
Attempting a bulk insert from the file throws:
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 4 (MyDate).
So clearly a date format problem.
Both databases are SQL 2K5 collated Latin1_General_CI_AS.
Any suggestions for settings that might be causing the problem that my client could check? I'm a bit stumped as I can't replicate the error.
Thanks, Iain

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

HowardW One Orange Chip Points: 29109 More actions · Answer 1

Some char to datetime implicit conversions are dependent on the Windows regional settings on the SQL Server. What date format are you using in the file?

irobertson Hall of Fame Points: 3695 More actions · Answer 2

Hi Howard,

Im using dd-mmm-yyyy. I've also tried set dateformat dmy without any joy.

Thanks, Iain

HowardW One Orange Chip Points: 29109 More actions · Answer 3

If you can recreate the text file, try using yyyy-mm-dd (e.g. 2009-10-21). What regional settings does your client have? I would assume that date types that include abbreviated English month names (e.g. Aug) may not convert with non-English regional settings.

irobertson Hall of Fame Points: 3695 More actions · Answer 4

Unfortunately I have no control over the text file format.

I'll ask him to have a look at his regional settings, but I'd expect them to be EN UK - he's on a UK site for a UK company.

Thanks, Iain

HowardW One Orange Chip Points: 29109 More actions · Answer 5

I'd suggest either creating a format file for the BCP command, or as this is SQL 2005, use SSIS which is more user friendly (you can just use the import/export wizard to access the SSIS engine if you're not doing any data manipulation).

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 6

Have a look in SSMS at the Default Language for the Windows Login that is actually executing the BCP... I'd hazzard a guess that one of them is "English" and the other "British English"