Translate chars with linked server OpenQuery

scr8er 20 Reputation points
2023-05-17T11:20:51.9166667+00:00

Hi Guys

I have a question about Chars Encoding between 2 SQL servers. Our BI team tries to import some info from our legacy SAP database to a legacy BI server.

DataSource: Sql Server 2008 - SQL_Latin1_General_CP850_BIN2

Destination: Sql Server 2014 - Modern__Spanish__CI_AS

Data on the source doesn't store spanish special chars converted, tables has varchar data types and UNICODE. For instance all the strings shows "Ð" instead of "Ñ", "Ý" instead of "Í"... and so on.

Seems like SAP uses some inner function to properly convert the special spanish chars cause you can see the data fixed in the UI.

I'm trying to provide a way for our BI team to bring the data fixed, working on the linked server properties without success trying to avoid they to handle this conversion locally.

The question is, could we manage this conversion in order to get the data fixed when we run a simple openquery like 'select SNAME from TABLE where ENAME like '%example%''? I.E. we get: MUÐOZ and we want to get MUÑOZ.

Or It must be handled after?

Thanks in advance!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,330 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 106.5K Reputation points
    2023-05-18T11:59:26.5666667+00:00

    OK, so they have messed things up a little bit. The collation is for code page 850, and in code page 850 0xD1 is indeed "Đ". But if they turn off translation when sending data to the application, the application may still display Ñ. However, if they sort the data, what looks like MUÑOZ would sort between MUCHO and MUERTO.

    From this experiment, we can sense a workaround for this would be

    SELECT convert(nvarchar(40), convert(varchar(40), binary_value))
    FROM   OPENQUERY(SERVER, 'SELECT convert(varbinary(40), col) AS binary_value FROM tbl')
    
    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Olaf Helper 43,166 Reputation points
    2023-05-17T11:29:27.38+00:00

    What's the data type used in data source and destination to store the text; varchar or navarchar = Unicode? If ist's Unicode the collation don't matter.


  2. Erland Sommarskog 106.5K Reputation points
    2023-05-17T21:52:22.6533333+00:00

    The easiest way to get out this quagmire is to convert to nvarchar on the source:

    SELECT * FROM OPENQUERY(SERVER, 'SELECT convert(nvarchar(40), col) FROM tbl')