cf13
asked on
Insert into MySQL Table from MSSQL Insert Trigger
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:
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
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
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
ASKER
I need to move the data from MSSQL to MySQL.
The solution i want is also only a workaround.
I can not insert data into the MySQL table.
Getting the error above.
The solution i want is also only a workaround.
I can not insert data into the MySQL table.
Getting the error above.
ASKER
I tried to execute my INSERT INTO OPENQUERY seperately.
Sure - With FROM t1 and some WHERE bla.
I Inserted this exact code into a Trigger.
ERROR!
Someone knows how to execute this trigger jobs like they were executed normally?
Sure - With FROM t1 and some WHERE bla.
I Inserted this exact code into a Trigger.
ERROR!
Someone knows how to execute this trigger jobs like they were executed normally?
ASKER
Now, i tried the following:
I packed my INSERT (into Mysql) Script into a STORED PROCEDURE with parameter - and...IT WORKS (manually)!
BUT:
If i execute the script from within trigger, i get exactly the same error.
My trigger now looks like
[code]
CREATE TRIGGER bla
ON t1
FOR INSERT,UPDATE
AS
DECLARE @ID int
SELECT @ID = (SELECT [id] FROM Inserted)
PRINT @ID
IF ( @ID >= 5000 ) AND ( @ID < 5999 )
BEGIN
exec proce_dure @ID
END
[/code]
Someone?
I packed my INSERT (into Mysql) Script into a STORED PROCEDURE with parameter - and...IT WORKS (manually)!
BUT:
If i execute the script from within trigger, i get exactly the same error.
My trigger now looks like
[code]
CREATE TRIGGER bla
ON t1
FOR INSERT,UPDATE
AS
DECLARE @ID int
SELECT @ID = (SELECT [id] FROM Inserted)
PRINT @ID
IF ( @ID >= 5000 ) AND ( @ID < 5999 )
BEGIN
exec proce_dure @ID
END
[/code]
Someone?
First, and this is NOT germaine to your problem, but something else to think about. Inserted is a table. which means it can have more than one row. Should it have more than one row, SELECT @ID=(Select ID from inserted) will only return the the ID from one of the rows. In other words, your trigger will miss some of the updates.
Second, and perhaps more relevant. The error you were getting was related to distributed transactions. That means updates on two different servers are being coordinated so that neither will succeed without the other. That's probably a good thing for you, but it does make the update more complex. I'm even suspecting from your error message that your ODBC provider does not support distributed transactions (or at least the way you have it configured). So you need to get distributed transactions working.
The reason it works outside of the trigger is because, in that case, there is no transaction on the SQL Server side, thusNOT distributed. But the trigger runs in the context of an insert/update on the SQL server side and that implies a transaction that must be coordinated with the update on MSSQL. Thus a distributed transaction. You can easily prove/disprove my theory. Change your standalone test to include an update on SQL Server. Something like this:
--This works, because it is not distributed:
exec proce_dure @ID
--This fails (I'm guessing), because it is distributed
begin transaction
exec proce_dure @ID
commit transaction
--If my theory is correct, this may work. (The data integrity implications are ominous, but I will let you wrestle with that).
Second, and perhaps more relevant. The error you were getting was related to distributed transactions. That means updates on two different servers are being coordinated so that neither will succeed without the other. That's probably a good thing for you, but it does make the update more complex. I'm even suspecting from your error message that your ODBC provider does not support distributed transactions (or at least the way you have it configured). So you need to get distributed transactions working.
The reason it works outside of the trigger is because, in that case, there is no transaction on the SQL Server side, thusNOT distributed. But the trigger runs in the context of an insert/update on the SQL server side and that implies a transaction that must be coordinated with the update on MSSQL. Thus a distributed transaction. You can easily prove/disprove my theory. Change your standalone test to include an update on SQL Server. Something like this:
--This works, because it is not distributed:
exec proce_dure @ID
--This fails (I'm guessing), because it is distributed
begin transaction
exec proce_dure @ID
commit transaction
--If my theory is correct, this may work. (The data integrity implications are ominous, but I will let you wrestle with that).
CREATE TRIGGER myInsertTrigger
ON [MSSQLDB].[dbo].[t1]
FOR INSERT,UPDATE
AS
BEGIN
SET XACT_ABORT ON
COMMIT TRANSACTION
INSERT INTO OPENQUERY([MYSQL-REMOTE],'SELECT fieldmy1, fieldmy2 FROM t1')
SELECT fieldms1, fieldms2
FROM Inserted
WHERE Inserted.ID >= 5000
AND Inserted.ID < 5999
END
P.S.
I still don't understand why you have gravitated to a trigger, which complicates the scenario. You have this:
Insert to MS SQL table
Fires insert trigger
If certain Id range
Insert to MYSQL table
Delete from MS SQL table
Why not this:
If certain ID range
Insert into MYSQL table
Else
Insert into MS SQL Table
I still don't understand why you have gravitated to a trigger, which complicates the scenario. You have this:
Insert to MS SQL table
Fires insert trigger
If certain Id range
Insert to MYSQL table
Delete from MS SQL table
Why not this:
If certain ID range
Insert into MYSQL table
Else
Insert into MS SQL Table
ASKER
Thanks for your help, dqmq!
You said, i have to enable distributed transactions - can you tell me where to look?
I need a trigger, because every moment Application1 writes data to MSSQL-db, it has to be converted and instantly inserted into the other db. An option is to execute a stored procedure every few seconds - i do not know how this performs.
Your code does not work either.
I tried a trigger, where only a fixed insert is given to MYSQL (no Insert-table, nothing regarding to the current INSERT/UPDATE).
It gives me the same error.
Also i tried INSTEAD OF and AFTER (which is implicit with FOR).
Same error.
It seems like a Trigger itself works inside some distributed context...
So where do i enable this distributed transactions via my ODBC driver?
You said, i have to enable distributed transactions - can you tell me where to look?
I need a trigger, because every moment Application1 writes data to MSSQL-db, it has to be converted and instantly inserted into the other db. An option is to execute a stored procedure every few seconds - i do not know how this performs.
Your code does not work either.
I tried a trigger, where only a fixed insert is given to MYSQL (no Insert-table, nothing regarding to the current INSERT/UPDATE).
It gives me the same error.
Also i tried INSTEAD OF and AFTER (which is implicit with FOR).
Same error.
It seems like a Trigger itself works inside some distributed context...
So where do i enable this distributed transactions via my ODBC driver?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yeah right.
Meanwhile i found this solution by myself. It is like you suggested.
Thank you also for the great explanation of distributed transactions!
my way as follows:
[code]
CREATE TRIGGER processUpdate
ON [table]
FOR UPDATE
AS
INSERT INTO [temporaryTableWithSameStr ucture]
FROM Inserted
SET IMPLICIT_TRANSACTIONS ON
GO
exec processUpdate_ProcedureWit hMySQLActi on
[/code]
I still did not test, if it works without the SET IMPLICIT_TRANSACTIONS ON
Meanwhile i found this solution by myself. It is like you suggested.
Thank you also for the great explanation of distributed transactions!
my way as follows:
[code]
CREATE TRIGGER processUpdate
ON [table]
FOR UPDATE
AS
INSERT INTO [temporaryTableWithSameStr
FROM Inserted
SET IMPLICIT_TRANSACTIONS ON
GO
exec processUpdate_ProcedureWit
[/code]
I still did not test, if it works without the SET IMPLICIT_TRANSACTIONS ON
1. Generally, not a good idea to start a transaction inside a trigger.
2. Your trigger will not work properly on a multiple record insert or update.
3. I don't understand the delete part. Don't think you can delete the same record(s) you are inserting/updating in T1. Perhaps need an "instead of" trigger.
See if attached trigger moves you ahead.
Open in new window