Comments - Multiple servers
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
Hi Ed,
Reading and writing to SQL Server form MariaDB is possible using the CONNECT storage engine. CONNECT is designed to see external data as a regular MariaDB table (though the performance obviously cannot be optimal). To connect SQL Serveer, you can create a table of type ODBC. Specific documentation for MariaDB/SQL Server interoperability is work in progress, but I can point you to the CONNECT category and ODBC table type:
The opposite flow is also possible. SQL Server has a feature called Linked Servers. MariaDB can be added as a remote data source via the ODBC protocol.
Cheers, Federico
Thanks Federico. That was the way I set the table up, as a connect table. I was on the right track. What's happening is that i'm getting invalid character when inserting data from the SQL Server table into my MariaDB table. All the documentation says I need to use utf8mb4 and I have everything set up to utf8mb4 but still getting the error. Is there a trick to getting it to work?
The documentation about matching data types is now in place. Should you find any mistake, feel free to fix them. Feedback would be very welcome.
https://mariadb.com/kb/en/sql-server-and-mariadb-types-comparison/
SQL Server can use UTF-8, UTF-16 or UCS2, depending on the column's data type and the collation in use (quite confusing, I know). Check the columns you are having troubles with - which data types do they use in SQL Server? SQL Server documentation tells you the character set used for each type. Once you find that out, you can set the proper data type for each MariaDB column.
If you're in trouble trying to find out the correct character set used for a column, here's what I'd do. Make the MariaDB column BLOB - it will consider the text as binary, so there is not character set or collation. Import the data again. Then try to use some software or library to detect the text encoding.