BCP Command using code page 65001 fails, if both SQL Server 2000 and SQL Server 2005 tools are installed

  • Article

Problem description:

The BCP command with code page 65001 fails when both SQL Server 2000 and SQL Server 2005 tools are present on the server.

Cause:

This issues occurs because when both SQL Server 2000 and SQL Server 2005 tools are present on the server then the BCP.EXE is used from
C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\
instead of
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\

Behavior observed:

Used BCP to select out data to a CSV file.

BEGIN

     DECLARE @fileName VARCHAR(500)
DECLARE @bcpCmd VARCHAR(4000)
SET @fileName = 'C:\BCP_TEST.csv'
/*

-c -t, => For setting comma delemeter
-C 65001 => Codepage setting for Unicode
-T => Trusted connection
-S => Server name

 */

      SET @bcpCmd = 'BCP "SELECT * FROM test_bcp.dbo.Exporttable" queryout ' + @fileName + ' -c -t, -C 65001 -T -S' + @@SERVERNAME
EXEC master..xp_cmdshell @bcpCmd

END

We get the following error :

NULL
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Code page 65001 is not supported by SQL Server
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to resolve column level collations
NULL

BCP copy out failed

From Process Monitor, we can see that the BCP.EXE is being picked up from

C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\bcp.exe

The BCP without the code page 65001 completes although it is using the BCP.EXE from “80\Tools\Binn\bcp.exe”

Since Code page 65001, is not supported in both SQL Server 2005 and SQL Server 2008, any BCP command using this Code Page would fail.

Workaround :

Please compare the path environmental variable between the working and non-working nodes. To do this go to
My computer >> Properties >>Advanced>>Environment variables
We will find that the path to the SQL Server 2000 BCP.EXE is ahead of the path to the SQL Server 2005 BCP.EXE. Simply reverse the path order, reboot and we should have the expected behavior.

 

Ashutosh Tripathi
Support Engineer, Microsoft SQL Server

Reviewed By

Sourabh Agarwal
Technical Lead, Microsoft SQL Server