Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 INTO sparelog
  SELECT * FROM inserted
 ELSE
  INSERT INTO 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.
Iam using sqlserver as my source server and MySql as Destination server...Thanks in advance
Posted 26-Feb-13 3:32am
Edited 26-Feb-13 21:27pm
v5
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You need to look into linked servers. This lets a SQL server instance talk to another instance (usally of SQL Server) including MySql. Here is a post that tells you how to create a linked server to MySQL:
 
www.sqlservercentral.com/Forums/Topic340912-146-1.aspx[^]
 
I've never done this to MySQL before so I can't help directly but the use of linked servers is well documented and understood.
  Permalink  
Comments
Ram7 from Hyderabad at 26-Feb-13 8:59am
   
Dear Reynolds thanx for ur responce, i have already created a linked server,and iam able to retrieve data,insert,update,delete with the help of distributed querys.And hear iam unable to copy a table data from sqlserver to mysql using a single trigger,can u please help me in thid..
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

You can try with OPENQUERY with help of Linked Server
CREATE TRIGGER LogRecords
ON mainlog
FOR  INSERT
AS
BEGIN
	SET NOCOUNT ON
	IF EXISTS(
	       SELECT *
	       FROM   mainLog
	)
	BEGIN
	    INSERT OPENQUERY(MySQLLinkedSvr,'SELECT * FROM Table')
	    SELECT * FROM   INSERTED
	END
	    
	 --------------Rest of your Code-----------------------------
END 
 
For more information have a loon on this link:
http://msdn.microsoft.com/en-us/library/ms188427.aspx[^]
  Permalink  
v2
Comments
Ram7 from Hyderabad at 27-Feb-13 7:05am
   
Dear Khorshed,thanks for ur responce, i have tried with ur above rectified trigger but iam getting an error like this:-
Procedure LogRecords, Line 9
MSDTC on server 'RAMU-IBX' is unavailable.
where 'RAMU-IBX'is my DSN name.Can u please guide me where iam doing mistake
Khorshed Alam, Dhaka at 27-Feb-13 23:15pm
   
Dear Ram7 sorry to answer you being late.It seems the RAMU-IBX server's Microsft Distributed Transaction Co-Ordinator is off. Please follow the link for the workaround.
http://blog.sqlauthority.com/2010/03/24/sql-server-fix-error-8501-msdtc-on-server-is-unavailable-changed-database-context-to-publisherdatabase/
Ram7 from Hyderabad at 27-Feb-13 23:53pm
   
Dear Khorshed,i have started Distributed Transaction Co-ordinator with help of ur above link,and again,iam getting the new error like below,please check the below error and guide me how to overcome this,thanks in advance..
Error:-"OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[MySQL][ODBC 5.1 Driver]Optional feature not supported".The operation could not be performed because OLE DB provider "MSDASQL" for linked server "MYSQL" was unable to begin a distributed transaction".
Khorshed Alam, Dhaka at 28-Feb-13 0:02am
   
I am not sure though, but ppl says it is a bug of MySQL. See the link
http://social.msdn.microsoft.com/Forums/is/sqldatabaseengine/thread/5c312706-5144-4a71-83b4-af63dec6810a
 
Can you test it out of the trigger? Create a stored procedure and test your work like
Create Procedure TestInsertMySQL
AS
 
INSERT OPENQUERY(MySQLLinkedSvr,'SELECT * FROM Table')
SELECT * FROM SomeTable
Ram7 from Hyderabad at 28-Feb-13 0:09am
   
Thanks Khorshed,i will try with the above link, or else i will go with procedure,
can u please tell me,how many possible ways of migrating data from one server database to another,so that i can try with those alternatives
Khorshed Alam, Dhaka at 28-Feb-13 0:22am
   
You are welcome!, to be honest, the way you are following to transfer data from completely different environment is not the most preferable way .Trigger based solution is preferable to work with the same environment. Better to export data in Flat file (.csv, or fixed length) from SQL Server via Export Import Wizard or SSIS packge. I am not an expert of MySQL, If there any facility of job scheduling then you can schedule a job to pull the data from the files. This solution will work if you do not need any real time in both places. In SQL server there are another feature called SSIS using this features you can expert SQL server data to another data source. There are also several ways but all are not top of my head right now. I will let you inform If I can think of any better solution. Hope you will get your job done soon!
Ram7 from Hyderabad at 28-Feb-13 0:36am
   
Thanks khorshed,thanks for all ur valuable suggestions........

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 289
1 DamithSL 225
2 OriginalGriff 175
3 Peter Leow 150
4 Afzaal Ahmad Zeeshan 95
0 OriginalGriff 7,355
1 DamithSL 5,254
2 Sergey Alexandrovich Kryukov 4,942
3 Maciej Los 4,906
4 Kornfeld Eliyahu Peter 4,514


Advertise | Privacy | Mobile
Web02 | 2.8.141223.1 | Last Updated 27 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100