collate SQL_Latin1_General_CP850_BIN to SQL_Latin1_General_CP1_CI_AS

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
-----------
Gestin 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
-----------
Gestin 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 DaponteMADS (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
> -----------
> Gestin 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
> -----------
> Gestin 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/techin.../2000/books.asp