BCP Field terminator character changing automatically

The Field Terminator specified in the BCP command changes to a different character than specified. This occurs when the bcp command is run in a batch file. The bcp works as expected when it’s run directly in the command prompt.

In our example the section sign (§) was changed to a ordinal Indicator (º)

Cause
====
The catch here is with the Code Pages. In code page 1252, the (§) corresponds to Decimal code 167.
The character that corresponds to Decimal Code 167 in Code page 437 is (º)
This simply means the system is converting the character with Code Page 1252 to Code Page 437.
We have the “-C” option in the bcp utility. However this can be used for the data bcp retrieves and not for the bcp commands itself.

The default code page set on the server is 437. Hence when you run the batch file, the command with these kind of special characters get converted to code page 437.

1252 - OEM CODEPAGE 437 - IBM CODE PAGE
(§) (º)
00A7 - Unicode 00BA - Unicode
167 - Decimal 167 – Decimal

Check out the below links for more info regarding the code pages.

Code Page 1252 Windows Latin
https://msdn.microsoft.com/en-us/library/cc195054.aspx

Code Page 437 IBM/MS-Dos Latin Code Page
https://msdn.microsoft.com/en-us/library/cc195060.aspx

Resolution
==========
1. Check your default Codepage
Open a command prompt and type (chcp) without the parenthesis.
This should return ”Active code page: 437"

2. Edit your bcp batch file. On the top, just above the bcp command, add the below command in a new line, without the parenthesis
(chcp 1252)

3. Save the file and you are good.

More Info :
This command will change the code page for only this command prompt. However the default code page of your system will still be 437.

Sample BCP batch file code:

@echo off
chcp 1252
bcp.exe "Select * From <Database>..<Table>" queryout C:\test.txt -c –t § -T -S <SQL Server>
Pause
 

Levi Justus,
TL, Microsoft SQL Server