1.安装安装mysqlconnector

2.配置mysqlconnector

ODBC数据管理器->系统DSN->添加->mysql ODBC 5.3 ANSI driver->填入data source name如jt,mysql的ip、用户名、密码即可

3.新建链接服务器

exec sp_addlinkedserver

@server=’jt’,       –ODBC里面data source name

@srvproduct=’mysql’,    –自己随便

@provider=’MSDASQL’,    –固定这个

@datasrc=NULL,

@location=NULL,

@provstr=’DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=192.168.5.188;DATABASE=suzhou;UID=root;PORT=3306;’,

@catalog = NULL

exec sp_addlinkedsrvlogin

@rmtsrvname=’jt’,

@useself=’false’,

@rmtuser=’root’,

@rmtpassword=’password’;

select * from openquery(jt,’SELECT * FROM sz ; ‘)

GO

USE [master]

GO

EXEC master.dbo.sp_serveroption @server=N’jt’, @optname=N’rpc out’, @optvalue=N’TRUE’

GO

EXEC master.dbo.sp_serveroption @server=N’jt’, @optname=N’remote proc transaction promotion’, @optvalue=N’false’

GO

—4.sqlserver和mysql新建库和表

 

create database suzhou;

create table sz(

id int not null  identity(1,1) primary key,

orderno char(20) not null,

ordertime datetime not null default getdate(),

remark varchar(200)

)

go

create table sz(

id int(11) not null ,

orderno char(20) not null,

ordertime datetime(6) not null ,

remark varchar(200),

primary key (id)

) engine=innodb  default charset=utf8;

 

—5.建立回环

–建立LOOPBACK 服务器链接

EXEC sp_addlinkedserver @server = N’loopback’ , @srvproduct = N’ ‘ , @provider = N’SQLNCLI’,

@datasrc = @@SERVERNAME

go

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

USE [master]

GO

EXEC master.dbo.sp_serveroption @server=N’loopback’, @optname=N’rpc out’, @optvalue=N’TRUE’

GO

EXEC master.dbo.sp_serveroption @server=N’loopback’, @optname=N’remote proc transaction promotion’, @optvalue=N’false’

GO

—-6.编写触发器和存储过程

—-6.1 insert

–重写触发器

use suzhou

go

alter trigger tr_insert_sz on suzhou.dbo.sz

for insert

as

declare  @id   int, @orderno  char(20),@ordertime datetime,@remark  varchar(200)

select @id=id,@orderno=orderno,@ordertime=ordertime,@remark =remark from inserted;

begin

print @id

print @orderno

print @ordertime

print @remark

exec loopback.suzhou.dbo.sp_insert @id,@orderno,@ordertime,@remark

end

go

 

 

–存储过程

use suzhou

go

create PROCEDURE sp_insert(

@id   int,

@orderno  char(20),

@ordertime datetime,

@remark  varchar(200)

)

AS

BEGIN

SET NOCOUNT ON;

Insert openquery(jt, ‘select * from sz’)(id,orderno,ordertime,remark)values(@id,@orderno,@ordertime,@remark)

END

go

—-6.2 update

–重写触发器

use suzhou

go

create trigger tr_update_sz on suzhou.dbo.sz

for update

as

declare  @orderno  char(20),@remark  varchar(200)

select @orderno=orderno,@remark =remark from inserted;

begin

exec loopback.suzhou.dbo.sp_update @orderno,@remark

end

go

 

 

–存储过程

use suzhou

go

create PROCEDURE sp_update(

@orderno  char(20),

@remark  varchar(200)

)

AS

BEGIN

SET NOCOUNT ON;

update openquery(jt, ‘select * from sz’) set  where 

END

go

 

—update数据测试

use suzhou

go

update sz set remark=’ocpyang’ where orderno=’a001′

go

—-6.3 delete

–重写触发器

use suzhou

go

create trigger tr_delete_sz on suzhou.dbo.sz

for delete

as

declare  @orderno  char(20)

select @orderno=orderno from deleted;

begin

exec loopback.suzhou.dbo.sp_delete @orderno

end

go

 

 

–存储过程

use suzhou

go

create PROCEDURE sp_delete(

@orderno  char(20)

)

AS

BEGIN

SET NOCOUNT ON;

delete openquery(jt, ‘select * from sz’)  where 

END

go

 

—delete数据测试

use suzhou

go

delete from sz where orderno=’a001′

go