We help IT Professionals succeed at work.
Get Started

Insert into MySQL Table from MSSQL Insert Trigger

cf13
cf13 asked
on
6,798 Views
Last Modified: 2012-05-09
Hi there!

I'm now a few days on solving the following problem:

I have an application, that runs on a MSSQL Database. Now an other Application, running on an MySQL-Database.
Now - they share some data, which makes it necessary to transfer partial data between two tables.

The approach is now, to create a trigger on the MSSQL-Table (t1) and to write the data from "Insert"-Table to the MySQL-Table (t2).
Remember: I do only need a partial set of data, where column names on t1 and t2 vary. (ffieldms1-data will be filled to fieldmy1)

MySQL-Database is set up as Linked Server via ODBC 5.1 Driver.

My konzeptual Trigger is the follow:


CREATE TRIGGER myInsertTrigger
    ON [MSSQLDB].[dbo].[t1]
    FOR INSERT,UPDATE
    AS
		DECLARE @ID int
		SELECT @ID = (SELECT [id] FROM Inserted)
		PRINT @ID
		IF ( @ID >= 5000 ) AND ( @ID < 5999 )
        BEGIN
		SET XACT_ABORT ON
		BEGIN TRANSACTION
			INSERT INTO 
				OPENQUERY([MYSQL-REMOTE],'
				SELECT fieldmy1, fieldmy2
				FROM t1')
			SELECT fieldms1, fieldms2
			FROM Inserted

			DELETE FROM [MSSQLDB].[dbo].[t1] WHERE [ID] = @ID
		COMMIT TRANSACTION
		END

Open in new window


When Trigger is executed (e.g. with a update of ID to above range) following error occurs

The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[MYSQL][ODBC 5.1 Driver] Optional Feature not supported

I need specific help - google brings up a mass of threads, i did not find the right one.

If someone knows, how to configure the mssql-script, the mssql server or the mysql-server

THANK YOU
Comment
Watch Question
This problem has been solved!
Unlock 2 Answers and 10 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE