Bad string encoding between BE and LE

Question

Hi

I have two sql servers: MariaDB, MS SQL, both server are connected over ODBC, (engine connect, linked server). But if I run query in MariaDB over engine connect as remote command, strings are reverse coded in BE a LE on servers. Advise how to ensure that the data is converted to the same format in both systems during exec command?

Example:
CREATE TABLE ExternCommand1 (
cmd VARCHAR(255) CHARACTER SET 'utf8' NOT NULL flag=0 ,
number INT flag=1,
message VARCHAR(255) flag=2)
ENGINE=connect
TABLE_TYPE=odbc
CONNECTION='DSN=MSSQL;UID=111;PWD=1111'
CHARSET = utf8
BLOCK_SIZE = 1
OPTION_LIST='Execsrc=1';

SELECT* FROM `ExternCommand1` WHERE`cmd` = CAST("INSERT INTO [DTB_INTERCHANGE].[dbo].[TestA] (ID, Tx) SELECT 15, N'ßÁȵ$€Ł'" AS VARCHAR(255) CHARACTER SET utf8);  

But in table in column Tx is: ßÃȵ$€Å

Thank

Jaroslav

Answer 1

This is an SQL Server forum, and I cannot answer questions related MariaDB.

But I note that you say CHARSET = utf8, but you the command you send to SQL Server has an nvarchar literal, so that is UTF-16LE. I would suspect, though, that data has been demolished already when it reaches SQL Server. You can use Profiler to see what SQL Server receives.