SQL Server and MySQL data synchronization issues

Due to the needs of the SMS project, the table in SQL Server needs to be synchronized to MySQL in real-time , then use the trigger to complete the function, the Insert operation is monitored, when SQL Server inserts data into MySQL while the insert Operation is in progress .

1. installation Sqlserver2008 Mysql

2. build mysql link server.

3. install Mysql drive. Mysql-connector-odbc-5.1.6-win32.msi.

4. after installation, in the computer Control Panel - admin tool -odbc< Span style= "font-family: ' The song Body '; > data source - system Dsn- add - complete.

5. Enter link parameters, click Test, save, finish. After success:

650) this.width=650; "Width=" 498 "height=" 361 "src=" http://s3.51cto.com/wyfs02/M01/4C/31/ Wkiol1q4lbscxrbyaahcdtoeaj4794.jpg "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" Spacer.gif "title=" A.png "/>

1. open SQL Server Management Studio, Click Server Object - linked server , right-click Linked server - new linked server- Enter the connection server name, server type selection - Other data source, provider Select "Microsoft OLE DB Provider for odbcdrivers", The data source chooses the GLB that was just built.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4C/2F/wKiom1Q4k92iY2oPAAEbq-SeZEI651.jpg "title=" B.png " alt= "Wkiom1q4k92iy2opaaebq-sezei651.jpg"/>

1. Test statement

SELECT * from OPENQUERY (MYSQL, "SELECT * from table")

Insert openquery (MYSQL, "SELECT * FROM table") (Id,name,password) VALUES (' 8 ', ' Qidanei ', ' 9 ')

1. Write triggers

Create trigger Tr_insert_user on [Test_trigger]. [dbo] . [user]

For insert

As

Begin

Insert openquery (MYSQL, "SELECT * from User") SELECT * frominserted

End

execute this trigger, when the user table is added,SQL Server will error, said that does not support distributed transactions, looked up a lot of information, said that SQL Server does not support this operation, and then a different way, By establishing a loopback, a stored procedure is invoked in the trigger.

1. Build loopback

-- establish LOOPBACK Server link

exec Span style= "font-size:13px;font-family: ' Courier New ';" > sp_addlinkedserver @server = n ' loopback ' @srvproduct = n " , @provider = n ' SQLNCLI ' @datasrc = @ @SERVERNAME

-- set server link options to block SQL Server promote a local transaction to a distributed transaction (focus) because of a far process call

EXEC sp_serveroptionloopback, N ' rpc out ' , ' TRUE '

EXEC sp_serveroptionloopback, N ' remote proc transactionpromotion ' , ' FALSE '

1. overriding triggers

Create trigger tr_insert_user on [Test_trigger]. [dbo] . [user]

for Insert

As

declare @username nchar ( 100 @userpwd nchar ( 100

Select @username=user_name,@userpwd= user_pwd from inserted;

Begin

Print @username

Print @userpwd

exec loopback. Test_trigger . dbo . sp_test @username , @userpwd

End

1. Stored Procedures

Create PROCEDURE sp_test(@user_name nchar(+), @user_pwd nchar (+))

As

BEGIN

--SET NOCOUNT on added to prevent extra result sets from

--interfering with SELECT statements.

Print ' sp_test: ' + @user_name

Print ' sp_test: ' + @user_pwd

SET NOCOUNT on;

--Insert statements for procedure here

Insert openquery(MYSQL, ' select * from user ') (user_name ,user_pwd)values(@user_name, @user_pwd )

END

Note the point: SQL Server The input parameters in the stored procedure are specified in length, otherwise the parameters passed in the trigger are intercepted.

SQL Server and MySQL data synchronization issues