The BCP command with code page 65001 fails when both SQL Server 2000 and SQL Server 2005 tools are present on the server.
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\
Used BCP to select out data to a CSV file.
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
We get the following error :
NULLStarting copy...SQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Code page 65001 is not supported by SQL ServerSQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Unable to resolve column level collationsNULL
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.
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
Sourabh Agarwal Technical Lead, Microsoft SQL Server