Linked server creation to MySQL Server

Question

Post reply

Linked server creation to MySQL Server

mailtovinayaka

SSC-Addicted

Points: 409
More actions
October 12, 2014 at 10:04 pm

#290836

Comments posted to this topic are about the item Linked server creation to MySQL Server

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

martin-705756 SSC-Addicted Points: 460 More actions · Answer 1

Beware!

SELECT TOP 10 * FROM MYSQL...test_table

Will pull all rows from test_table across the network to the MSSQL server, then return the top 10. If that sounds like a bad idea (try doing this on a large table over a slow link), use OPENQUERY to get your results (you'll need to use the correct MYSQL syntax).

DanOrc SSC Enthusiast Points: 165 More actions · Answer 2

martin-705756 (10/13/2014)
Beware!
SELECT TOP 10 * FROM MYSQL...test_table
Will pull all rows from test_table across the network to the MSSQL server, then return the top 10. If that sounds like a bad idea (try doing this on a large table over a slow link), use OPENQUERY to get your results (you'll need to use the correct MYSQL syntax).

Is this behavior the same in SQL Server linked servers or only in MySQL?

martin-705756 SSC-Addicted Points: 460 More actions · Answer 3

I'm ready to be corrected, but I'm pretty sure it's only non-MSSQL servers.

This is because you can't directly query MYSQL (or other SQL flavours) with MSSQL server syntax - e.g. the example "select top 10 * from MYSQL...test_table" translates to MYSQL as "select * from test_table limit 10"

I'm not an authority on this - just got burnt by querying a production MySQL db in an outside data centre from MSSQL - nobody loves you when you kill the link!

webrunner SSC-Dedicated Points: 31715 More actions · Answer 4

martin-705756 (10/13/2014)
I'm ready to be corrected, but I'm pretty sure it's only non-MSSQL servers.
This is because you can't directly query MYSQL (or other SQL flavours) with MSSQL server syntax - e.g. the example "select top 10 * from MYSQL...test_table" translates to MYSQL as "select * from test_table limit 10"
I'm not an authority on this - just got burnt by querying a production MySQL db in an outside data centre from MSSQL - nobody loves you when you kill the link!

If true, that is disappointing. MySQL does have syntax to limit rows, e.g.:

select * from MYSQL...test_table LIMIT 10

It seems a driver could make this translation at the server, but I'm not expert in that area either.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

samuel.jones SSC Enthusiast Points: 127 More actions · Answer 5

Just to point out,

GRANT ALL ON *.* to fooUser@'<IP Address/Host name of the machine from where you are trying to access MYSQL Server' IDENTIFIED BY '<Password>';

essentially creates a user with full permissions (except for GRANT permissions) on your MySQL server. I would still recommend security best practices and only grant necessary rights to your linked server account (i.e if you're only going to read, GRANT SELECT instead of GRANT ALL).