BCP Command gives different output in SQL Server 2005 compared to 2008?

Question

Hello all,

I have executed two identical bcp commands on two different setups with the same data.

Machine A = Windows Vista machine which is running SQL Server 2008

Machine B = Windows Server 2003 machine running SQL Server 2005

The output text file of the bcp command is different! For a start, Machine B does not add column names to the beginning of the text file and it seems to get line breaks wrong. Each row should be separated by line breaks instead it seems as if its putting in line breaks after two fields?!

The bcp command is this:

bcp DBn_Mitor..TI40 out C:\prepTI4O.txt -c -T -t"\",\"" -r"\"\n\"" -S SQLServer01 -E

What is going on here? What is different in SQL Server 2005 and 2008 that will make bcp work differently?

Answer 1

Has the server ever been upgraded, or have other DBMS tools installed? AKA, are you running the correct bcp.exe?

I've seen this before where the "bcp.exe" found when looking in the %PATH% variable is not the one you expect to run...

Answer 2

BCP is a SQL tool ,2008 does come with a different version I believe. I think if you are using BCP to export/import between versions you should use RAW and not Native.

From Access to SQL, the best option is to use a format file - details of this can be found on MSDN.

http://msdn.microsoft.com/en-us/library/ms162802.aspx