SQLserver2008

Last Post 08 Mar 2013 04:53 AM by gunneyk. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Ramu
New Member
New Member

--
07 Mar 2013 12:50 AM
Hii to all I have a requirement that,i have two tables (i)mainlog,present in sqlserver(ii)sparelog,present in MySql which is present in different server location.I need to write a trigger to copy a mainlog table data to sparelog table.when ever any DML operation is performed to mainlog table the trigger has to fire automatically and reflect the same to sparelog table present in different server location.I have already created a Linkedserver to Mysql from my local Sqlserver. I have tried by writing the following trigger and successfully copied the table data from mainlog table to sparelog table for only insert operation,and with in the same instance of sqlserver.
CREATE TRIGGER LogRecords ON mainlog FOR INSERT
AS BEGIN
SET NOCOUNT ON IF EXISTS(SELECT * FROM mainLog)
INSERT OPENQUERY(MySQL,'SELECT * FROM sparelog')
SELECT * FROM INSERTED
ELSE
INSERT OPENQUERY(MySQLLinkedSvr,'SELECT * FROM sparelog')
SELECT * FROM INSERTED
END

The above trigger working only when both the tables are present in same sqlserver.Can any one help me how to rectify my above trigger so that i can perform all the Insert,Update,Delete, operation with only single Trigger when both the tables present in different server location. Also iam finding the below error when iam trying to insert,update,delete, operation on a table present in different server location(MYSQl db)
ERROR:-OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[MySQL][ODBC 5.2(a) Driver]Optional feature not supported". Msg 7391, Level 16, State 2, Procedure MYSQLLogRecords, Line 12 The operation could not be performed because OLE DB provider "MSDASQL" for linked server "MYSQL" was unable to begin a distributed transaction.

I have already configured DTC,its status is running and Automatic

Thanks
Ramu
gunneyk
New Member
New Member

--
07 Mar 2013 06:08 PM
I am not sure if you can do this via a linked server to MySql or not but your probably better off going with a 3rd party solution that will replicate the data for you such as this one: http://www.symmetricds.org/
Ramu
New Member
New Member

--
07 Mar 2013 08:36 PM
Hello,thanks for ur reponse,can u guide me how should i work with SymmetricDS(sample examples)by providing any links,so that i can understand what exactly the SymmetricDS is, and how should it is helpful to my requirement,thanks in advance

Regards
Ramu
gunneyk
New Member
New Member

--
08 Mar 2013 04:53 AM
I have never used it but the web site has plenty of information as to what it is and what it can do. There is even a documentation tab.


Acceptable Use Policy
---