Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: Inserting, Updating, Deleting Data from MS SQL 2005 to MySQL

    I am really struggling here, I have read numerous pages on this subject but nothing that ever really solves it.

    I have an MS SQL 2005 Express database that I want to sync the data from a few tables into a MySQL database with the identical tables and coloumns (created using mssql2mysql).

    I have downloaded and installed the MySQL ODBC driver and successfully setup a linked server instance in the MS SQL Express database.

    As I understand it, I need to setup 'trigger's' in MS SQL Express that will update, insert or delete data accordingly in the MySQL database using OPENQUERY. As discussed here INFI Developer

    Now for some reason I cannot get it to work.

    MS SQL 2005, from what I understand is to trys to use MSDTC to transfer the data but the MySQL ODBC Driver does not support it hence the error below.

    Code:
    OLE DB provider "MSDASQL" for linked server "MySQL_DB" returned message "[MySQL][ODBC 5.1 Driver]Optional feature not supported".
    Msg 7391, Level 16, State 2, Procedure MySQL_PointValue_trg, Line 17
    The operation could not be performed because OLE DB provider "MSDASQL" for linked server "MySQL_DB" was unable to begin a distributed transaction.
    I have tried adding COMMIT TRANSACTION after the BEGIN in the SQL trigger as suggested on one page I found info on but I get the following error

    Code:
    Msg 3609, Level 16, State 1, Line 2
    The transaction ended in the trigger. The batch has been aborted.
    The data will only change in the MS SQL database and therefore I will not need to sync any changes from the MySQL back to the MS SQL.

    Can someone please point me in the right direction. This is driving me mad!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not sure I have all the elements to answer (what's the code of your triggers?), but as a general principle try to avoid performing complex operations inside a trigger. It's usually better to have the trigger call an external stored prodedure or an external process to do the job. One of the reasons is that if for any reason something goes wrong inside the trigger, an automatic rollback is performed. If something systematically goes wrong inside the trigger then you are left with a read-only table or database.

    For the second error you receive, you cannot commit a transaction like that in a trigger. See for instance: Considerations for Transactional Replication

    Have a nice day!

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    Here is the trigger. How would I go about changing it to a stored procedure?

    Code:
    USE [i96X]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET IMPLICIT_TRANSACTIONS OFF
    GO
    
    ALTER TRIGGER [dbo].[MySQL_PointValue_trg] 
       ON  [i96X].[dbo].[PointValue] 
       AFTER INSERT,DELETE,UPDATE
    AS 
    BEGIN
    	SET NOCOUNT ON
    	SET XACT_ABORT ON
    	INSERT INTO OPENQUERY(MySQL, 'SELECT * FROM PointValue')
    	SELECT PointID, DataTime, DataValue, DataValueType, DataValueStatus FROM INSERTED
    END
    All I am looking to do is replicate the MS SQL table in MySQL with any changes and updates.

  4. #4
    Join Date
    Jun 2011
    Posts
    1

    Solution!!!

    TO ALL WHO ARE LOSING THEIR TIME ON THIS RIDICULOUS compability crap here is a working solution!!!!!

    JUST create the damn insert inside a stored procedure and call it from a trigger.


    AND voila all is good!!!

    I am thinking tht the couse it tht the insert statment returns the information from the insert as a warning and the triger responds to it via a rollback

Posting Permissions

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