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 Syntax

I am trying to use the BCP utility to copy a stored procedure out to a text
file.
1) Is there a way to include the field headers in the text file?
2) Is there a way to turn off the quotes between data?
So far, here is the syntax I have so far:
bcp "database..sp" queryout "c:\test.txt" -t, -Sserver -Uuser -Ppassword
Thank you,
JLFlemingOne option is to get the column names from the metadata & use UNION operator
to get a single resultset like:
SELECT -1 AS "sort_col",
MAX( CASE ORDINAL_POSITION WHEN 1 THEN col1 END ) AS "col1",
MAX( CASE ORDINAL_POSITION WHEN 2 THEN col2 END ) AS "col2",
..
MAX( CASE ORDINAL_POSITION WHEN n THEN coln END ) AS "coln"
FROM TABLE_NAME = 'tbl'
UNION
SELECT 0, col1, col2,... coln
FROM tbl ;
You can warp this into a view & BCP it out pretty easily, but watch out of
type mismatches with SYSNAME types. The above shows a general approach, but
you can avoid any reference to the meta-data by directly typing out your
column names like:
SELECT -1 AS "sort_col", "col1", "col2", ... "coln"
UNION
SELECT 0, col1, col2, ... coln
FROM tbl
ORDER BY "sort_col" ;
Another option is to create a ASCII file with the headers & then BCP out the
data to another file. Simply use the DOS COPY command like:
copy header.txt + body.txt data.txt
Anith

BCP and Stored Procedure

I am trying to use BCP to generate a textfile with the results of sp_help_revlogins.
xp_cmdshell 'bcp "execute sp_help_revlogin" queryout c:\test\test.txt -c -S"testserver" -U"sa" -P"test"'
The error I receive is the following:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at least one column
I would appreciate any incite on the solution to this problem as well as the cause.
Thank you in advance.
Robert
If all you want is the output of sp_help_revlogin to be placed in a file try
using OSQL. Something like this:
OSQL -S<yourserver> -U<login> -P<password> -Q"exec
sp_help_revlogin" -oc:\test\test.txt
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Robert M." <Robert M.@.discussions.microsoft.com> wrote in message
news:0C360F11-4D5A-4BC4-976E-35650510E775@.microsoft.com...
> I am trying to use BCP to generate a textfile with the results of
sp_help_revlogins.
> xp_cmdshell 'bcp "execute sp_help_revlogin" queryout
c:\test\test.txt -c -S"testserver" -U"sa" -P"test"'
> The error I receive is the following:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at
least one column
> I would appreciate any incite on the solution to this problem as well as
the cause.
> Thank you in advance.
> Robert
>
>
|||If all you want is the output of sp_help_revlogin to be placed in a file try
using OSQL. Something like this:
OSQL -S<yourserver> -U<login> -P<password> -Q"exec
sp_help_revlogin" -oc:\test\test.txt
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Robert M." <Robert M.@.discussions.microsoft.com> wrote in message
news:0C360F11-4D5A-4BC4-976E-35650510E775@.microsoft.com...
> I am trying to use BCP to generate a textfile with the results of
sp_help_revlogins.
> xp_cmdshell 'bcp "execute sp_help_revlogin" queryout
c:\test\test.txt -c -S"testserver" -U"sa" -P"test"'
> The error I receive is the following:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at
least one column
> I would appreciate any incite on the solution to this problem as well as
the cause.
> Thank you in advance.
> Robert
>
>

BCP and Stored Procedure

I am trying to use BCP to generate a textfile with the results of sp_help_re
vlogins.
xp_cmdshell 'bcp "execute sp_help_revlogin" queryout c:\test\test.txt -c -S"
testserver" -U"sa" -P"test"'
The error I receive is the following:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must conta
in at least one column
I would appreciate any incite on the solution to this problem as well as the
cause.
Thank you in advance.
RobertIf all you want is the output of sp_help_revlogin to be placed in a file try
using OSQL. Something like this:
OSQL -S<yourserver> -U<login> -P<password> -Q"exec
sp_help_revlogin" -oc:\test\test.txt
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Robert M." <Robert M.@.discussions.microsoft.com> wrote in message
news:0C360F11-4D5A-4BC4-976E-35650510E775@.microsoft.com...
> I am trying to use BCP to generate a textfile with the results of
sp_help_revlogins.
> xp_cmdshell 'bcp "execute sp_help_revlogin" queryout
c:\test\test.txt -c -S"testserver" -U"sa" -P"test"'
> The error I receive is the following:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at[
/vbcol]
least one column[vbcol=seagreen]
> I would appreciate any incite on the solution to this problem as well as
the cause.
> Thank you in advance.
> Robert
>
>

bcp easy for you, hard for me

I'm trying to use bcp to load a textfile of data into a db table. With my
real data, I always get "Unexpected EOF encountered in BCP data-file". So I
created a very simple test -- now I don't get the error, but I don't get any
results, either. Am I missing something obvious?
Here's the simple case:
1. Create a table
CREATE TABLE test (
foo int
);
2. Create a text file of data in xemacs, test.txt. Contents look like this:
1
2
3
4
5
3. Load it up
C:\>bcp test in c:\bcp\test.txt -S db.serverhost.com -U joe -P joe
Enter the file storage type of field foo [int-null]:
Enter prefix-length of field foo [1]:
Enter field terminator [none]:
Do you want to save this format information in a file? [Y/n] n
Starting copy...
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 1
What happened? Why were no rows copied? I tried again, this time with field
terminator = \n. And again with \r\n. No change.
TIA!Try using the data formats listed in BOL
Data format bcp utility switch BULK INSERT clause
Native -n DATAFILETYPE = 'native'
Character -c DATAFILETYPE = 'char'
Unicode character -w DATAFILETYPE = 'widechar'
Unicode native -N DATAFILETYPE = 'widenative'
"Jesse" wrote:

> I'm trying to use bcp to load a textfile of data into a db table. With my
> real data, I always get "Unexpected EOF encountered in BCP data-file". So
I
> created a very simple test -- now I don't get the error, but I don't get a
ny
> results, either. Am I missing something obvious?
> Here's the simple case:
> 1. Create a table
> CREATE TABLE test (
> foo int
> );
> 2. Create a text file of data in xemacs, test.txt. Contents look like this
:
> 1
> 2
> 3
> 4
> 5
> 3. Load it up
> C:\>bcp test in c:\bcp\test.txt -S db.serverhost.com -U joe -P joe
> Enter the file storage type of field foo [int-null]:
> Enter prefix-length of field foo [1]:
> Enter field terminator [none]:
> Do you want to save this format information in a file? [Y/n] n
> Starting copy...
> 0 rows copied.
> Network packet size (bytes): 4096
> Clock Time (ms.): total 1
>
> What happened? Why were no rows copied? I tried again, this time with fiel
d
> terminator = \n. And again with \r\n. No change.
>
> TIA!
>
>
Try using the|||Jesse (nospam@.nospam.com) writes:
> C:\>bcp test in c:\bcp\test.txt -S db.serverhost.com -U joe -P joe
> Enter the file storage type of field foo [int-null]:
> Enter prefix-length of field foo [1]:
> Enter field terminator [none]:
> Do you want to save this format information in a file? [Y/n] n
> Starting copy...
> 0 rows copied.
> Network packet size (bytes): 4096
> Clock Time (ms.): total 1
>
> What happened? Why were no rows copied? I tried again, this time with
> field terminator = \n. And again with \r\n. No change.
I've never tried this interactive form of BCP, but int-null is a
binary type. I don't really know what you are suppsed to answer there.
Likewise, prefix-length should be 0; prefix formats is for binary
formats.
The normal way to use BCP is to specify a format on the command line,
or use a format file. Formats on the command line makes use of -c
to specify character format, and you can combine this with -t and -r
to specify field and row terminators. Or you can use -n to specify
native formats. There are also Unicode variations.
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