Hi, I have a SQL Server for a SAP database with the collation
SQL_Latin1_General_CP850_BIN. When I connect to that server (or, in
this example, to another server with the SQL_Latin1_General_CP850_BIN
collation) and execute a select, the accents seem weird:

select t.TEXT
from [GC-SAP02].P01.p01.AGR_TEXTS t
where t.MANDT = '300'
and t.SPRAS = 'E'
and t.AGR_NAME = 'ZCD_GEST_DEUDA_PATENTE_SD'

TEXT
-----------------------------------
Gesti¾n de deudas patentes en SD UP

(1 row(s) affected)

But if I connect to a "normal" SQL Server with a
SQL_Latin1_General_CP1_CI_AS collation, and execute the same select
thru a linked server:

TEXT
-----------------------------------
Gestión de deudas patentes en SD UP

(1 row(s) affected)

I need to perform the select connected to the
SQL_Latin1_General_CP850_BIN and get the results as I were connected to
the SQL_Latin1_General_CP1_CI_AS server. I tried with cast and collate,
but I can't get it to work:

select t.TEXT collate SQL_Latin1_General_CP1_CI_AS
from [GC-SAP02].P01.p01.AGR_TEXTS t
where t.MANDT = '300'
and t.SPRAS = 'E'
and t.AGR_NAME = 'ZCD_GEST_DEUDA_PATENTE_SD'

Any ideas??? Thanks in advance for your help !!!


Manuel Daponte

MADS (mdaponte@gmail.com) writes:
> Hi, I have a SQL Server for a SAP database with the collation
> SQL_Latin1_General_CP850_BIN. When I connect to that server (or, in
> this example, to another server with the SQL_Latin1_General_CP850_BIN
> collation) and execute a select, the accents seem weird:
>
> select t.TEXT
> from [GC-SAP02].P01.p01.AGR_TEXTS t
> where t.MANDT = '300'
> and t.SPRAS = 'E'
> and t.AGR_NAME = 'ZCD_GEST_DEUDA_PATENTE_SD'
>
> TEXT
> -----------------------------------
> Gesti¾n de deudas patentes en SD UP
>
> (1 row(s) affected)
>
> But if I connect to a "normal" SQL Server with a
> SQL_Latin1_General_CP1_CI_AS collation, and execute the same select
> thru a linked server:
>
> TEXT
> -----------------------------------
> Gestión de deudas patentes en SD UP

It looks as if the problem is that the data in the CP850 database is
wrong. Or that the collation is wrong.

ó in Latin-1 is 0xF4. In CP850 ó is at 0xA2. And guess what character
that is at 0xF4 in CP850? Yup, ¾.

So what's happening when you select the data is that it get's converted
to Latin-1. Problem is that is already Latin-1, but labeled incorrectly.

Back in 6.5 days it was pretty easy to turn conversion on and off, but
I think it's more difficult these days. The best bet may be to run
your queries from the command-line tool OSQL. Since OSQL is an command-
line tool, it's character set is CP850, so you will not get any
conversion in this case. The output looks poor in the command-line
window, but if you save to file, the file will look good in an Windows app.

Of course, it would be a good idea to fix that database. But this may
require some care. I would not be surprised if there is a mix of
CP850 and Latin1 data in that database.

In the long run, try to get rid of the CP850 databases.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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