BCP character conversion

I bcped in data into a database with SQL_Latin1_General_Cp1_CI_AS collation. The input data has an embedded character (ascii 174). I did not specify any code page using the -C parm. The data was converted to character (ascii 171). I ran the bcp trying -C1252 and -CRAW and both maintained the correct character. -C437 and -COEM change the character to .
Why did this happen? I thought that data would be converted to correctly without any code page specification.Different code pages map binary values to glyphs (the graphic symbols that humans know and love) differently. One binary value can map to many different glyphs using different code pages.

If BPC doesn't know which code page to use for translation, you get "pot luck", especially for characters that aren't well defined. Typically, you want the code page that created the data. Occaisionally, you want the code page that was intended (or at least used) to view the data. Because of the pot-pouri of mappings supported by the different code pages, the business of getting data from point A to point B has grown yet another potentially "interesting" twist to amuse those of us that do the moving!

-PatP|||Thanks for the quick reply.
BOL states:
"When bulk copying data using native or character format, bcp, by default, converts character data to:

OEM code page characters when exporting data from an instance of Microsoft SQL Server.

ANSI/Microsoft Windows code page characters when importing data into an instance of SQL Server. "

So wouldn't the bcp in use code page 1252 by default. This should be similar to -C1252.|||Well where did the data come from?|||The bcp ran from my workstation with a code page 437 - if that's your question.|||From Books Online topic bcp: "OEM Default code page used by the client. This is the default code page used by bcp if -C is not specified."
From that I suppose that SQL Server interpreted your file as being OEM 437 CP. mojza|||I guess I still don't understand why the character was changed during the bcp. I can view it correctly from my workstation which is 437, but if I bcp using -C437 or without -C(which uses default OEM code page) it gets converted. I think I'm missing something.|||In what editor can you see that character correctly? in ANSI (e.g.Notepad) or in OEM (e.g.Edit)? mojza|||Correctly in notepad or textpad, not correctly in edit. So bcp, running in a command window, is using 437 which changes the character to ?|||Then, in my opinion, your file was created in code page ANSI 1252 (notepad ok) and bcp interprets your file as cp 437 (default client OEM code page). That leads to a loss of some extended characters that are not compatible between these two pages unless you tell sql server to interpret him as 1252 or without any translation (RAW). Check out this Microsoft article. There is a good explanation and excellent examples. mojza

http://support.microsoft.com/default.aspx?scid=kb;en-us;199819|||Thanks for your help. That article definitely helped explain things. I also looked at the nls files for 437 and 1252 and character 174(offset x0178) reflects in the 1252 file and int 437 file.
Again, thanks.

BCP - help with import/export using collation Chinese_PRC_CI_AS

Hi,

I've been trying to export/import a table from a DB with
Chinese_PRC_CI_AS collation, and I can't seem to get it working at all
- the export seems rather simple, it is the import that is not working
at all. I am using MSSQL 2000:

The table (under a Chinese_PRC_CI_AS collation DB) is as follows:

CREATE TABLE [dbo].[test_table] (
[id] [int] NOT NULL ,
[first_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]

Then I insert a set of rows using "Enterprise Manager", and then
export the table contents using the following command:

BCP "select * from testdb.dbo.test_table" queryout test_table.txt -c -
t -S"10.10.10.10" -U"sa" -P"mypassword"

(This is how I must to run the export - I can not change the way of
the statement is run - the reason is because I am using someone elses
exports)

No I try import the data back into the same table, test_table, but
first I take the following steps:

from iSQL: "DELETE FROM testdb.dbo.test_table"

Then I create a format file, test_table.fmt, which looks like this:

8.0
2
1SQLINT012""1id Chinese_PRC_CI_AS
2SQLNCHAR0100"\r\n"2first_name Chinese_PRC_CI_AS

So here is the problem:

When I import the data using the command

BCP "testdb.dbo.test_table" in "test_table.txt" -f"test_table.fmt" -
S"10.10.10.10" -U"sa" -P"mypassword"

The rows get imported - but they are nothing like the data that I
imported:

test_table.txt contents:

1
pete
2
rob
3
sam
4
carl

Imported results:

12800爀漀戀
13056猀愀洀
13312挀愀爀氀
3276543瀀攀琀攀

I've tried changing the format file line's separator to be as
follows: "\n", "\n\0", "\r\0\n\0" - but the results did not differ
much from each other.

Any help is greatly appreciated.

Thank you,

Jim.InvestorTrade (shija03@.gmail.com) writes:

Quote:

Originally Posted by

Then I insert a set of rows using "Enterprise Manager", and then
export the table contents using the following command:
>
BCP "select * from testdb.dbo.test_table" queryout test_table.txt -c -
t -S"10.10.10.10" -U"sa" -P"mypassword"
>...
Then I create a format file, test_table.fmt, which looks like this:
>
8.0
2
1 SQLINT 0 12 "" 1 id Chinese_PRC_CI_AS
2 SQLNCHAR 0 100 "\r\n" 2 first_name Chinese_PRC_CI_AS

The format file does not match the command that generates the file.
The export command is for a tab-delimited text file, your format file
is for a binary file. A format file for a text file uses only
SQLCHAR for the data type (or SQLNCHAR if it's a Unicode file).

Thus the correct format file would be:

8.0
2
1 SQLCHAR 0 0 "\r\n" 1 id Chinese_PRC_CI_AS
2 SQLCHAR 0 0 "\r\n" 2 first_name Chinese_PRC_CI_AS

Although I suspect that you could simply use -c to import the file.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx