Sql Query / Table Join between two servers

w.hackl posted 6 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 6 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 6 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 6 years ago
DataController appears to be such an extension of FEDERATED(X).
jonlegras posted 10 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 10 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.