Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    3

    Unanswered: SQL Server 2k5 Linked with MySQL 5 issue

    Hi all,
    I am having an issue with a MySQL Server that is linked to a SQL Server 2k5 instance.
    I am working on a test machine so i have been able to do just about anything to try and get this to work.

    The linking works fine(ish), i am using the mysql odbc 3.51 connector.

    Both the MySql and SQL Server are currently on the same Windows XP SP2 installation (I wanted to eliminate network as the root of my problem)

    I have set up DB's on each server with the same names and a test table in each with the same name and 2 columns for now, being pkID and data. Very straight forward.

    Working from MSSMS I can read from the MySQL table using this code:
    Code:
    SELECT * FROM [LOCAL_MYSQL]...[fqweb.data];
    I can also insert using:

    Code:
    INSERT INTO [LOCAL_MYSQL]...[fqweb.data] (data) Values ('testing!');
    No problems there.

    However, Triggers created on the MS SQL table will not work, code:
    Code:
    CREATE TRIGGER items_insert ON dbo.data
    AFTER Insert
    AS 
    SET XACT_ABORT ON
    INSERT INTO OPENQUERY(LOCAL_MYSQL, 'SELECT * FROM data')
    SELECT pkID, data FROM INSERTED;
    I have tried a few variations on that trigger to check if it was my syntax. Same story;
    On an insertion i get the following error:
    Msg 7391, Level 16, State 2, Procedure items_insert, Line 5
    The operation could not be performed because OLE DB provider "MSDASQL" for linked server "LOCAL_MYSQL" was unable to begin a distributed transaction.

    Now i have googled extensively for an answer and every thing i find points to MSDTC. I followed all the guides and have tried pretty much every combination of settings (although i believe this is for network issues in any case) and have pretty much come to a dead end now.

    If any one has any idea why the triggers will not work it will be much appreciated and possibly save me balding prematurely!

    Thanks in advance,
    Darren

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I have to get in two important points up front. First of all, if you are trying to implement replications, triggers are not how I'd recommend doing it. Second, if replication really is what you want to do then you should probably check into Microsoft SQL Replication (which is provided as part of SQL Server).

    With that noted, I can probably help you get your existing code working. I'd be willing to bet that the problem lies in MS-DTC (Microsoft Distributed Transaction Coordinator). Just starting DTC is rarely enough... You probably need to the directions and configure DTC for your needs.

    If that doesn't work, let us know where you get stuck and somebody can almost certainly help you straighten things out!

    -PatP

  3. #3
    Join Date
    Aug 2008
    Posts
    3
    Pat, thank you for your reply.

    Ultimately, the trigger will contain business logic and pass xml to MySQL for a PHP page to deal with (Not sure how, will look into SQL Server XML capabilities when I get there, probably as a string?)

    For now, just getting it to trigger and ship some form of data across to the MySQL server when the MS SQL table is updated is my goal. It has to be on trigger and will not be a straight replication.

    With regards to DTC, I have pretty much enabled absolutely everything and nothing, and every option in between. There are a few guides out there on DTC as this seems a common issue. If I how somehow overlooked some setting with regards to DTC I will be surprised and headbutt the wall as I have spent over a day now trying to get this working

    Unfortunately I can't seem to find anything that helps. I seldom turn to forums as 99.99% of the time someone has had the same problem before.

    Any help will be much appreciated.

    Thanks,
    Darren

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I think the problem lies in the way you configured your linked server or the provider settings. Check these settings:

    USE [master]
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'OleMySql.MySqlSource', N'AllowInProcess', 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'OleMySql.MySqlSource', N'DynamicParameters', 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'OleMySql.MySqlSource', N'NestedQueries', 1
    GO

    Also, in linked server options see if RPC and distributed transactions are enabled.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Aug 2008
    Posts
    3
    Thanks, I tried that but no change I'm afraid. Really stuck on this one. RPC is enabled, don't see an option for enable distributed transactions under the linked server properties however.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •