bcp accent problem

Hello,
I'm importing data from formatted text files with accent (Latin1). My
database is set to French_CI_AS. I use bcp with -C"Latin1" or
-C"French_CI_AS" but bcp seems to ignore the flag and every single accent is
left behind.
That is if on column is "rené" in the source file, it becomes "ren" in the
database.
From experience, the -C flag should fix that problem but here nothing seems
to work.
Any ideas anyone?
Thanks,
Sitar.
Sitar (Sitar@.discussions.microsoft.com) writes:
> I'm importing data from formatted text files with accent (Latin1). My
> database is set to French_CI_AS. I use bcp with -C"Latin1" or
> -C"French_CI_AS" but bcp seems to ignore the flag and every single
> accent is left behind.
> That is if on column is "ren" in the source file, it becomes "ren" in the
> database.
> From experience, the -C flag should fix that problem but here nothing
> seems to work.
For -C you specify a code page, not a collation, as I recall. Thus, if the
file is in the ANSI code page, specify -C ANSI or -C RAW.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Hi Erland,
Actually when I use bcp directly it works with either ANSI or Latin1. But
when I use a bcp format file it just stops working properly.
-- Sitar
"Erland Sommarskog" wrote:

> For -C you specify a code page, not a collation, as I recall. Thus, if the
> file is in the ANSI code page, specify -C ANSI or -C RAW.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>
|||Sitar (Sitar@.discussions.microsoft.com) writes:
> Actually when I use bcp directly it works with either ANSI or Latin1. But
> when I use a bcp format file it just stops working properly.
So how does the format file look like? Do you explicitly specify the
collation in it?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||The bcp format file looks like this:
8.0
11
1 SQLCHAR 0 0 "" 0
NUM_SEQ_SERVICE ""
2 SQLCHAR 0 6 ";" 2
CODE_ETABL_ID French_CI_AS
3 SQLCHAR 0 35 ";" 4
LIBELLE_ETABL French_CI_AS
4 SQLCHAR 0 4 ";" 5
CODE_CATEG_ETABL French_CI_AS
5 SQLCHAR 0 2 ";" 3
CODE_REGION_ID French_CI_AS
6 SQLCHAR 0 24 ";" 6
DATE_DEBUT ""
7 SQLCHAR 0 24 "\r\n" 7
DATE_FIN ""
8 SQLCHAR 0 0 "" 0
OCCURRENCE_PREVALIDEE ""
9 SQLCHAR 0 0 "" 0
ETAT_OCCURRENCE French_CI_AS
10 SQLCHAR 0 0 "" 0
OCCURRENCE_ACTIVEE ""
11 SQLCHAR 0 0 "" 0
DER_DATE_SAISIE ""
I tried with ANSI and RAW but it just does not do anything.
Sitar
___
"Erland Sommarskog" wrote:

> Sitar (Sitar@.discussions.microsoft.com) writes:
> So how does the format file look like? Do you explicitly specify the
> collation in it?
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>
|||Actually if I execute that commands from the server itself it works fine.
The problem occurs when I execute the command from another machine. More
specifically the IIS machine.
Sitar
"Erland Sommarskog" wrote:

> Sitar (Sitar@.discussions.microsoft.com) writes:
> So how does the format file look like? Do you explicitly specify the
> collation in it?
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>
|||Sitar (Sitar@.discussions.microsoft.com) writes:
> Actually if I execute that commands from the server itself it works fine.
> The problem occurs when I execute the command from another machine. More
> specifically the IIS machine.
And the regional settings of that machine is French?
Do you lose the accents, when run from a command-line window on the IIS
server? Or only when you run from within IIS?
Could you post the CREATE TABLE statement for the table, and some piece
of sample data? It's good if you can pack into a zip file, to prevent
that data get mashed in news transport.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||
> And the regional settings of that machine is French?
Both the IIS server and SQL Server machines' regional settings are french.

> Do you lose the accents, when run from a command-line window on the IIS
> server? Or only when you run from within IIS?
Actually I haven't tried yet from a command-line window on the IIS server. I
tried from SQL Query Analyzer on my dev machine and it works fine, providing
the format files and data files are hosted on the sql server. Otherwise the
accents are dropped.
I'll try on the IIS server.

> Could you post the CREATE TABLE statement for the table, and some piece
> of sample data? It's good if you can pack into a zip file, to prevent
> that data get mashed in news transport.
Not sure if I I'm allowed to post actual data but I'll see what I can do.
- Sitar
|||Sitar (Sitar@.discussions.microsoft.com) writes:
> Actually I haven't tried yet from a command-line window on the IIS
> server. I tried from SQL Query Analyzer on my dev machine and it works
> fine, providing the format files and data files are hosted on the sql
> server. Otherwise the accents are dropped.
This sounds funny. Did exactly say how you run the BCP command. You
tried from Query Analyzer. This is not from where I run my command-line
tools. Does this mean that you run BCP through xp_cmdshell? Or are you
in fact using BULK INSERT?

> Not sure if I I'm allowed to post actual data but I'll see what I can do.
It does not have to be actual data. Just make up some dummy data
that demonstrates the problem.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Hi,
Sorry I haven't been specific enough.
I ran bcp both from a dos prompt and also from the Query Analyzer with the
bulk import command. With the bulk import command both the data file and the
format file were stored on the SQL server.
The problem seems to occur only when the data files and format files are
stored on another machine than the Sql Server.
So I suspect somewhere between the client and the sql server the data may
get corrupted or converted to an from an english locale and the accent could
be dropped. But I'm not sure why this would be happening.
- Sitar

> This sounds funny. Did exactly say how you run the BCP command. You
> tried from Query Analyzer. This is not from where I run my command-line
> tools. Does this mean that you run BCP through xp_cmdshell? Or are you
> in fact using BULK INSERT?
>
> It does not have to be actual data. Just make up some dummy data
> that demonstrates the problem.