473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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
-----------------------------------
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

Jul 23 '05 #1
1 12971
MADS (md******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Nanchil | last post by:
Hi, We created a database (DB2 UDB 7.2 on solaris 8) without this (COLLATE USING IDENTITY ) option. But we need this now for binary sorting. The database is siebel database. Is it possible to drop...
1
by: Dag Henriksson | last post by:
I'm trying to compare strings written in Swedish using: std::locale loc(""); // System locale is Swedish loc(first, second); But this does not give me the right return value. for example, ...
0
by: Francisco Araujo | last post by:
I don't know if this is a bug or if i'm doing something wrong, but when the print dialog is shown on VB.NET and the user select multiple-copies and check the collate option and click ok, the...
3
by: Robert Liles | last post by:
I am using Visual Basic.NET 2002 and cannot get "collate" from the PrintDialog1 or e.PageSettings.PrinterSettings.Collate to return anything but "False." What am I doing wrong? I am using...
0
by: Robert Liles | last post by:
If I check the "Collate" box in the PrintDialog it is ignored, always returns "False" and does not collate. If I add the line " e.PageSettings.PrinterSettings.Collate = True" in my print routine,...
1
by: Zengfa Gao | last post by:
Hi, all: I have a schema definition, worked fine for PostgreSQL 7.2 and 7.3. But I saw errors when I use it on PostgreSQL 7.4. Can anyone tell me what's changed in 7.4? Thanks a lot!
0
by: Ron Adam | last post by:
I've made a few more changes to my little collate module. There might be better ways to handle the options, or better choices for the options themselves. I tried to keep it as general as...
1
by: mabubakarpk | last post by:
HI. I am using SQL Server 2000. I generate a script of some table from EmpDB database when I run script in query analyzer it return error "Incorrect syntax near 'COLLATE'." Scripts is ...
1
by: baburk | last post by:
CREATE TABLE Employees( EmployeeID int IDENTITY(1,1) NOT NULL, LastName nvarchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) What is use of Collate. In what are all the places can we...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.