MSSQL query that puts results into MySQL database

Posted on 2011-10-19
Last Modified: 2012-05-12

using SQL server 2008 R2 and want to run a scheduled query that the results are put into A MySQL database. I have used SSISTO d o this the other way. But how can I get results from MSSQL to MySQL?

Question by:andybrooke
    LVL 142

    Accepted Solution

    create a linked server in your sql 2008 box (after installing the MySQL drivers on the windows box), then you can use openquery, aka insert into openquery

    mysql odbc:
    oledb provider as alternative:

    linked server (mysql):


    hope this helps

    Author Comment

    I think open query is the key. I have set up a linked server which works. I think maybe its best to create a trigger that once specific data is entered into a field it copies this to my myql table. I need

    Author Comment

    I want to make a trigger in a table that when a status column is populated with 1 of these 4 numebrs (2000,2010,2020,2030). It then runs a query that does the following. It looks up the ID of the current row Table A and uses this to find the related row in Table B.  

    From Table B it then gets a lead ref from that row. It would now connect to a MySQL database via openquery (Linked database already set up)look up the ID in a Table C and in the status column enter a value based on the initial trigger.

    So if 2000 was in the status column in Table A MSSQL. Now the mySQL Table C status would show 'Completed'.

    If 2010 was in the status column in Table A MSSQL. Now the mySQL Table C status would show 'ONGOING'.
    and so on...

    I know this possible but not sure where to start.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    via trigger: not recommended (if working at all), because mysql does not really support distributed transactions.

    so, you can put a trigger, but just to insert into a "process" table the key values of the rows that updated, and than need to be "replicated" to the mysql table.

    then, in a scheduled job, you can then perform the update (see this article):


    Author Comment

    I added a linked server and running the query works ok. But when I put it insidea  trigger it fails.

    OLE DB provider "MSDASQL" for linked server "TEST" returned message "[MySQL][ODBC 5.1 Driver]Optional feature not supported".
    Msg 7391, Level 16, State 2, Procedure STATUS_UPDATE, Line 6
    The operation could not be performed because OLE DB provider "MSDASQL" for linked server "TEST" was unable to begin a distributed transaction.
    create trigger yourtriggername
    on tableA
    for insert,update
    update t
    set t.status= case i.status  when 2000 then 'Completed' when 2011 then 'Ongoing' end
    from inserted i
    inner join tableB b
    on =
    inner join [linkedservername]...tablename t
    on = b.leadref

    Open in new window

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    that is exactly what I wrote: you won't be able to do this in the trigger itself directly, but only in a dedicated job outside of the trigger.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now