Translate chars with linked server OpenQuery

Question

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!

Answer 1

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

Answer 2

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.

Answer 3

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