BCP utility replaces German with junk characters

Hi All,
I am using command line bcp utility of SQL Server to import data from a text
file to database.

I have some german words in the text file and after
import the German characters are lost.

see eg below.

Input : Khner, Klaus -> Text file value
OutPut: Khner, Klaus -> Table data, after import.

I am using unicode in the commad line.
The collation setting is LATIN

can anyone show some light.

Thanks in advance

Manoj.Manoj (mcmanoj_2000@.yahoo.com) writes:
> I am using command line bcp utility of SQL Server to import data from a
> text file to database.
> I have some german words in the text file and after
> import the German characters are lost.
> see eg below.
> Input : Khner, Klaus -> Text file value
> OutPut: K?hner, Klaus -> Table data, after import.
> I am using unicode in the commad line.
> The collation setting is LATIN

Since BCP is a command-line utility it's defaul code page is the OEM
code page. Therefore, if your server uses an ANSI page, and this is
the normal, there is an automatic conversion from OEM to ANSI. Problem
is if the file is in ANSI already - which it often is.

BCP offers the -C option to control this. The simplest is to use
-C RAW to turn off conversion.

See the description of BCP in Books Online for more details.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

BCP Problems with ISO Latin-1 Characters past ASCII 192

I am trying to export data from a table with char and varchar fields
(Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
with the character past ASCII 192 the characters like , , , etc. My
export statement looks like this as I am using the -c flag.
bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
But I get the following:
MXICO| |MX|0|1
The data in the table looks like this
MXICO| |MX|0|1
-n (Native) did not seem to work.
-w (Unicode character) did work, geting it out, but I can not put it back
in...
Do I have some sort of code page/collation not set correct?
Thanks
Reeves
Hi
Have you tried -C RAW?
John
"Reeves Smith" <ReevesSmith@.WillTellYouLater.com> wrote in message
news:uXjvY5UVEHA.3988@.tk2msftngp13.phx.gbl...
> I am trying to export data from a table with char and varchar fields
> (Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
> with the character past ASCII 192 the characters like , , , etc. My
> export statement looks like this as I am using the -c flag.
> bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
> But I get the following:
> MXICO| |MX|0|1
> The data in the table looks like this
> MXICO| |MX|0|1
> -n (Native) did not seem to work.
> -w (Unicode character) did work, geting it out, but I can not put it back
> in...
> Do I have some sort of code page/collation not set correct?
> Thanks
> Reeves
>
|||Reeves,
We had a similar problem that I believe was fixed when we switched to
sing -C ACP. (RAW may do for you as well, try it out.) The problem is
that the default is -C OEM, which is basically the old DOS character set and
that is trashing some characters for you.
Russell Fields
"Reeves Smith" <ReevesSmith@.WillTellYouLater.com> wrote in message
news:uXjvY5UVEHA.3988@.tk2msftngp13.phx.gbl...
> I am trying to export data from a table with char and varchar fields
> (Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
> with the character past ASCII 192 the characters like , , , etc. My
> export statement looks like this as I am using the -c flag.
> bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
> But I get the following:
> MXICO| |MX|0|1
> The data in the table looks like this
> MXICO| |MX|0|1
> -n (Native) did not seem to work.
> -w (Unicode character) did work, geting it out, but I can not put it back
> in...
> Do I have some sort of code page/collation not set correct?
> Thanks
> Reeves
>

BCP not exporting the "{" or "}" characters

Hello,

I am running a bcp command to export all data from one single table. One of the table field has the flower brackets at the begining and the end and the column is defined as of data type uniqueidentifier (length 16).

The original command that I used is :

c:\bcp dbo.Product out c:\product.csv -w -t"|" -r\n

The ProdID column in Product table have values like : {3E116F82-5E52-4EF9-9A97-8756EA6E9A16}

But in the out put file, the flower brackets are being omitted.

So, I tried the following query :

c:\bcp "Select ID,Name,"+'"{"+'+"convert(varchar(100),ProdID)"+'+"}"'+",ProdType,IsActive,TitleCode,CreationDate,CreatorID,AllowView,AllowRead,AllowWrite,AllowDelete,AdminURL,ReportProc from dbo.Product" queryout c:\Product16Feb.csv -w -t"|" -r\n

But it is giving the following error message:

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near ','.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
prepared.

The SQL statement is correct because when I run it individuall, it works but not with the bcp command.

I just want the ProdID column to have the flower brackets in the output file using bcp. How can I achieve this?

Many thanks

Ratan

You can define your SQL statement as a view and then BCP the view. Otherwise, consider editing the file after the BCP operation and applying the brackets then.

bcp Limitation

Is there any limitation on the length of the query used? When the query
exceeds 128 characters, I get the error
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'Select Id,Name,Descrip, Address,
others,
case when len((Select distinct Street from mydb..testw2 where w2' is
too long. Maximum length is 128.
MadhivananYou can use a view and then bcp from it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115982145.425540.79910@.o13g2000cwo.googlegroups.com...
Is there any limitation on the length of the query used? When the query
exceeds 128 characters, I get the error
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'Select Id,Name,Descrip, Address,
others,
case when len((Select distinct Street from mydb..testw2 where w2' is
too long. Maximum length is 128.
Madhivanan|||Can you post the exact command you are excuting?. BCP expects double
quotation marks around the query and single quotation marks around anything
embedded in the query. For some reason, sql server is interpreting the query
as an identifier and the max length for identifiers is 128. Check database
option "quoted identifier" using sp_dboption or use SET QUOTED_IDENTIFIER in
the batch.
AMB
"Madhivanan" wrote:

> Is there any limitation on the length of the query used? When the query
> exceeds 128 characters, I get the error
> Server: Msg 103, Level 15, State 7, Line 1
> The identifier that starts with 'Select Id,Name,Descrip, Address,
> others,
> case when len((Select distinct Street from mydb..testw2 where w2' is
> too long. Maximum length is 128.
>
> Madhivanan
>

BCP in of hex data

Can anyone please tell me if it is possible to bcp in a file with hex
data into SQL Server 2000?

I have a file that the characters appear as blocks when viewed in
notepad but appear as hex values in a hex editor. I have put just one
of these characters (hex 1A) into a file and tried to bcp it in to a
table with one column that is of type binary using a format file. I
can't get it to work.

Can anyone please help?

Thank you
TomHi

You don't give the BCP commands that you are using, but if you create the
format file by exporting the data then the same format file should work when
importing it.

create table mybinarydata ( col1 varbinary(4), col2 binary(4) )

INSERT INTO mybinarydata ( col1, col2 ) VALUES ( 1234, 5678 )
INSERT INTO mybinarydata ( col1, col2 ) VALUES ( 2345, 6789 )

bcp "test..mybinarydata" out mybinarydata.bcp -T -S MyServer

Format file if you take all defaults:

8.0
2
1 SQLBINARY 1 4 "" 1 col1
""
2 SQLBINARY 2 4 "" 2 col2
""

If you open the data file you should notice it is not readable.

If you use

bcp "test..mybinarydata" out mybinarydata.bcp -c -T -S MyServer

You will not be prompted for a format file and get the data file contains:

00001234 00005678
00002345 00006789

For using format files see books online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adm
insql.chm::/ad_impt_bcp_9yat.htm

HTH

John

"Thomas Richards" <tom.richards@.rocketmail.com> wrote in message
news:f118866.0404140941.21eb0091@.posting.google.co m...
> Can anyone please tell me if it is possible to bcp in a file with hex
> data into SQL Server 2000?
> I have a file that the characters appear as blocks when viewed in
> notepad but appear as hex values in a hex editor. I have put just one
> of these characters (hex 1A) into a file and tried to bcp it in to a
> table with one column that is of type binary using a format file. I
> can't get it to work.
> Can anyone please help?
> Thank you
> Tom|||Hi

You don't give the BCP commands that you are using, but if you create the
format file by exporting the data then the same format file should work when
importing it.

create table mybinarydata ( col1 varbinary(4), col2 binary(4) )

INSERT INTO mybinarydata ( col1, col2 ) VALUES ( 1234, 5678 )
INSERT INTO mybinarydata ( col1, col2 ) VALUES ( 2345, 6789 )

bcp "test..mybinarydata" out mybinarydata.bcp -T -S MyServer

Format file if you take all defaults:

8.0
2
1 SQLBINARY 1 4 "" 1 col1
""
2 SQLBINARY 2 4 "" 2 col2
""

If you open the data file you should notice it is not readable.

If you use

bcp "test..mybinarydata" out mybinarydata.bcp -c -T -S MyServer

You will not be prompted for a format file and get the data file contains:

00001234 00005678
00002345 00006789

For using format files see books online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adm
insql.chm::/ad_impt_bcp_9yat.htm

HTH

John

"Thomas Richards" <tom.richards@.rocketmail.com> wrote in message
news:f118866.0404140941.21eb0091@.posting.google.co m...
> Can anyone please tell me if it is possible to bcp in a file with hex
> data into SQL Server 2000?
> I have a file that the characters appear as blocks when viewed in
> notepad but appear as hex values in a hex editor. I have put just one
> of these characters (hex 1A) into a file and tried to bcp it in to a
> table with one column that is of type binary using a format file. I
> can't get it to work.
> Can anyone please help?
> Thank you
> Tom

BCP generates 0x00 characters

Hello,
I'm using BCP with a .FMT file to generate exports of some tables.
BCP sometimes messes up the file by placing 0x00 characters (ASCII value 00)
in the export. It looks like BCP translates NULL values or empty fields like
this.
Strange enough, this doesn't happen all the time. It will happen every once
in a while (data changes all the time). When it happens, it messes up the
entire file.intGod (intGod@.discussions.microsoft.com) writes:
> I'm using BCP with a .FMT file to generate exports of some tables.
> BCP sometimes messes up the file by placing 0x00 characters (ASCII value
> 00) in the export. It looks like BCP translates NULL values or empty
> fields like this.
> Strange enough, this doesn't happen all the time. It will happen every
> once in a while (data changes all the time). When it happens, it messes
> up the entire file.
My guess the 0x00 characters are there, because they appear in the data.
How does your format file look like?
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|||"Erland Sommarskog" wrote:
> My guess the 0x00 characters are there, because they appear in the data.
> How does your format file look like?
Dear Erland,
I found the root of the problem but I don't know why BCP behaves like this.
The problem is: sometimes (only sometimes) BCP will treat an empty field
incorrectly and insert a 0x00 character instead of the string. By using a
CASE on that field (case fieldName when '' then ' ' else fieldName), the
problem is solved. Still, it's annoying.
I've never come across this problem but I've never used .fmt files before.
I've always used tab-delimited.|||intGod (intGod@.discussions.microsoft.com) writes:
> I found the root of the problem but I don't know why BCP behaves like
> this.
> The problem is: sometimes (only sometimes) BCP will treat an empty field
> incorrectly and insert a 0x00 character instead of the string. By using a
> CASE on that field (case fieldName when '' then ' ' else fieldName), the
> problem is solved. Still, it's annoying.
> I've never come across this problem but I've never used .fmt files before.
> I've always used tab-delimited.
Since you did not post the format file, I cannot comment much more. But
it appears that you use the queryout option. I have not use queryout myself
much, but I have the impression that it is considered buggy.
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

bcp failed when special characters {;" were used in the password from command line in Win 2

bcp worked with password in normal characters, but failed with {;"

I tried for SQL 2005 and SQL 2000, from Window 2003 SP1. Is this a bug?

When the special character { was used in the password, bcp failed with Native error 0.

>>

F:\ftp\log\Cases>bcp "sem5.sem5.server_system_log_1" in server_system_log_1.dat
-n -Smodonald -Usem5 -P{
SQLState = 08001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Neither DSN nor SERVER keyword supplied
SQLState = 01S00, NativeError = 0
Warning = [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
>>

When the special character ; or " was used in the password, bcp failed with Native error 18456.

>>

F:\ftp\log\Cases>bcp "sem5.sem5.server_system_log_1" in server_system_log_1.dat
-n -Smodonald -Usem5 -P;
SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sem5'.

F:\ftp\log\Cases>bcp "sem5.sem5.server_system_log_1" in server_system_log_1.dat
-n -Smodonald -Usem5 -P"
SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sem5'.
>>

Could you try :

bcp "sem5.sem5.server_system_log_1" in server_system_log_1.dat
-n -Smodonald -Usem5 -P"{"|||

I tried the followings but it didn't work.

bcp "sem5.sem5.server_system_log_1" in server_system_log_1.dat
-n -Smodonald -Usem5 -P"{"

bcp "sem5.sem5.server_system_log_1" in server_system_log_1.dat
-n -Smodonald -Usem5 -P'{'

bcp "sem5.sem5.server_system_log_1" in server_system_log_1.dat
-n -Smodonald -Usem5 -P\{

|||Please try {{} for pwd {|||

I opened a case and got an answer.

Based on the Microsoft SQL Server 2005 online help for “Strong Passwords[SQL Server]”,

>>

if used in an OLE DB or ODBC connection string, a login or password must not contain the following characters: [] {}() , ; ? * ! @.. These characters are used to either initialize the connection or separate connection values.

>>

As the result, SQL Server Enterprise Manager allows all special characters but the OLE DB or ODBC connection is different.

|||

Thanks for following up your own post. It helps others when you share your solution.

BCP & odd charachters

Hi

When I BCP out some data from a database that contains german charachters ( in particular characters with hyphens & unlatts on them ), the letter appears as a square box when I open it in a text file.

I run BCP locally on the server that the database is on.

Any ideas what might be causing this? I have the local Regional settings of the server set to German but still no joy.

Any ideas?

Help!!

JWhat happens when you BCP the data back into a database? Sometimes certain products that we use to view files can not display none printable characters to they write out the 'box'. It does not mean that data is incorrect. You should create another test table and BCP the data into the new test table.