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.
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
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!
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.
Here is the trigger. How would I go about changing it to a stored procedure?
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET IMPLICIT_TRANSACTIONS OFF
ALTER TRIGGER [dbo].[MySQL_PointValue_trg]
SET NOCOUNT ON
SET XACT_ABORT ON
INSERT INTO OPENQUERY(MySQL, 'SELECT * FROM PointValue')
SELECT PointID, DataTime, DataValue, DataValueType, DataValueStatus FROM INSERTED
All I am looking to do is replicate the MS SQL table in MySQL with any changes and updates.