problem with inserting data in to MySQL from MSSQL

[Manishankar]

We tried to use the openquery() as the COM object is not working in the new windows server. We tried to insert data into MySQL from SQLServer using the “openquery()”. Here is what we did to get the openquery work:-



1. Configured the MySql server as a linked server with MSSql, and named that as ‘TTS’.

2. Started the MSDTC (Microsoft Distributed Transaction Coordinator) service from administrative tools.

3. Then wrote the triggers – which will insert the data when ever a record is inserted.


To test it we created a table named ‘student’ with a single varchar field in the MySQL database and ‘test’ table in the MSSQL database. Here is what we managed t did so far:

Issue#: 1



The openquery() works fine with the direct command. We used the following query in SQLServer query analyzer and it worked perfectly.



insert OPENQUERY (TTS, 'SELECT * from student')

values ('Stuart')



The above inserted The data ‘Stuart’ is into the student table in the MySql database.



Issue#: 2.



Then we did the same by add that in the trigger as follows:



CREATE TRIGGER insert_trigger_1 ON test

FOR INSERT

AS

insert OPENQUERY (TTS, 'SELECT * from student ')

values ('Stuart1')



Finally when we tried to insert a value in the MSSql Database into the table ‘test’ with the following query we get the error message “The current transaction could not be exported to the remote provider. It has been rolled back”.



Query used - insert into test values ('tst')



Issue# 3



We thought the there might be some connection problem while we try to use the openquery inside a trigger. So we tried to check with “Select ” query and found it returns all records from the MySQL table whenever any record is inserted into the ‘test’ table.



CREATE TRIGGER insert_trigger_1 ON test

FOR INSERT

AS

Select * from openquery (TTS, 'SELECT * from student ')



We will try to fix it on our next shift.

[Manishankar]

Solution of using 'OPENQUERY' in triggers:
The trigger should be written as the following example:



create trigger [tirgger name] on [table name]
for insert
As
commit transaction
insert OPENQUERY (LINKEDSERVER-NAME, 'SELECT * from [dbasename].[tablename] ')
values ( ..... )




The main solution is the line 'commit trigger'.
That means we are now making insertion or updates in the linked server after the transaction of the sql server has been committed.

[demodav]

Do you have to set up values? I want it to automatically update, insert, delete anytime the table gets changed? How is this done?