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
DECLARE @ID int
SELECT @ID = (SELECT [id] FROM Inserted)
IF ( @ID >= 5000 ) AND ( @ID < 5999 )
SET XACT_ABORT ON
SELECT fieldmy1, fieldmy2
SELECT fieldms1, fieldms2
DELETE FROM [MSSQLDB].[dbo].[t1] WHERE [ID] = @ID
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