|
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,
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',@optvalue=N'TRUE'
GO
EXEC master.dbo.sp_serveroption @server=N'loopback',@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,@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 remark=@remark where orderno=@orderno
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 orderno=@orderno
END
go
?
---delete数据测试
use suzhou go delete from sz where orderno='a001' go
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
