Linked server creation to MySQL Server

  • mailtovinayaka

    SSC-Addicted

    Points: 409

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

  • martin-705756

    SSC-Addicted

    Points: 459

    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

    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: 459

    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

    One Orange Chip

    Points: 29987

    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 says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • samuel.jones

    SSC Enthusiast

    Points: 127

    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).

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

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