Sql Query / Table Join between two servers

w.hackl posted 5 years ago in General
Hi there,

is it possible to execute a query between two different servers ?

The first is MS SQL the second is MYSQL.

The syntax to completely define the first is [Server\Instance].db.dbo.table.

By using this syntax i get the error :

Wrong syntax near '\' ...

Any suggestion or workarounds ?


ansgar posted 5 years ago
No, you cannot join between two server instances, only between tables on the same server. I recall Oracle can do that, a colleague of me did that to join some AS400/DB2 database with an Oracle instance on another machine. I can ask him how that worked exactly if you want.
jfalch posted 5 years ago
Between MySQL Servers, this is possible using MySQL´s FEDERATED storage engine, or MariaDB´s FEDERATEDX fork.

According to this, it should be possible (but decidedly non-trivial) to modify FEDERATEDX to also access other types of servers (.."write connection classes for other RDBMSs"..).
jfalch posted 5 years ago
DataController appears to be such an extension of FEDERATED(X).
jonlegras posted 2 months ago

After years of using MySQL and HeidiSQL, I'm now in a position of using MSSQL - I'm so glad that I can still use Heidi, because MSSMS is awful.

The main problem I have is the system I'm now working on has 2 SQL Servers, and so cross-server queries are required - a feature that MSSMS does have called 'Linked Server'. The issue described here seems to be exactly the same as I have. Obviously this post is from quite some time ago, so things might have changed, especially as Heidi now work with MSSQL a lot more.

Somewhere in MSSQL, the link is setup, but the result is queries like:

SELECT c1., c2. FROM [SERVER1].[DB1].dbo.customer AS c1 LEFT JOIN [SERVER2].[DB2].dbo.customer AS c2 ON c2.ID = c1.ID

Is this feature now available within Heidi? If not, is there any chance it will be?

AppleTechSupport posted 2 months ago

Facing the requirement. Please, someone, provide the complete SQL code for the same. Thank you in advance.

Please login to leave a reply, or register at first.