sqlserver与mysql数据同步问题

由于短信项目的需求，需要将sqlserver当中的表实时同步到mysql当中，于是采用触发器完成该功能，对insert操作进行监控，当sqlserver进行插入操作时，同时将数据插入到mysql当中。

1. 安装sqlserver2008以及mysql。

2. 建立mysql链接服务器。

3. 在本电脑上安装mysql驱动。Mysql-connector-odbc-5.1.6-win32.msi.

4. 安装完毕后，在电脑控制面板-管理工具-odbc数据源-系统dsn-添加-选择mysql odbc driver-完成。

5. 输入链接参数，点击测试，保存，完成。成功之后的截图：

1. 打开sql server management studio,点击服务器对象-链接服务器,右键点击链接服务器-新建链接服务器-输入连接服务器名称，服务器类型选择-其他数据源，访问接口选择“Microsoft OLE DB Provider for ODBCDrivers”，数据源就选择刚才建好的glb。

 

1. 测试语句

Select * from OPENQUERY(MYSQL,”select * from table”)

Insert openquery(MYSQL,”select * from table”)(id,name,password)values(‘8’,’qidanei’,’9’)

1. 写触发器

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

For insert

As

Begin

Insert openquery(MYSQL,”select * from user”)select * frominserted

End

 

执行此触发器，当对user表进行添加的时候，sqlserver会报错，说不支持分布式事务，查了很多资料，都说sqlserver不支持此操作，于是换了一种方式，通过建立回环，在触发器中调用存储过程来实现。

1. 建立回环

 --建立LOOPBACK 服务器链接

EXEC sp_addlinkedserver@server = N'loopback' , @srvproduct = N' ' , @provider = N'SQLNCLI', @datasrc = @@SERVERNAME

--设置服务器链接选项，阻止SQL Server 由于远过程调用而将本地事务提升为分布事务（重点）

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

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

1. 重写触发器

    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. 存储过程

create PROCEDURE sp_test(@user_name nchar(100),@user_pwd nchar(100))

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

 

注意点：sqlserver中存储过程中的输入参数要指定长度，不然在触发器中传入的参数会被截取。