1.---安装mysqlconnector
http://www.mysql.com/products/connector/
2.---配置mysqlconnector
ODBC数据管理器->系统DSN->添加->mysql ODBC 8.0 ANSI driver->填入data source name如jt,mysql的ip、用户名、密码即可
3.--新建链接服务器
sp_addlinkedserver
@server='jt',--Connector ODBC里面填的data source name
@srvproduct='MySql',--自己随便
@provider='MSDASQL',--固定这个
@datasrc='jt', ----ODBC里面data source name
@location=NULL,
@catalog=NULL
4.---创建连接mysql数据库的账号及密码
sp_addlinkedsrvlogin
@rmtsrvname='jt',----ODBC里面data source name
@useself='false',
@rmtuser='root',---mysql账号
@rmtpassword='root';--mysql账号密码
刷新对象资源管理器中的链接服务器,能看到的Connector ODBC里面填的data source name内容,目录下对应的是mysql里的数据库
5.---测试是否可以访问mysql数据库中的表
select * from openquery(jt,'SELECT * FROM t_attendance ; ')
6.-----建立允许远程访问连接操作
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
7.--建立LOOPBACK 服务器链接
EXEC sp_addlinkedserver @server = N'loopback' , @srvproduct = N' ' , @provider = N'SQLNCLI',
@datasrc = @@SERVERNAME
go
8.--设置服务器链接选项,阻止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
9.---编写触发器和存储过程
--insert
--新建触发器(如果已经创建的则修改触发器,将create换成alter,下面同理)
use zjdhd_ice
go
create TRIGGER TR_INSERT_S_KaoQin ON zjdhd_ice.dbo.S_KaoQin
FOR INSERT
AS
DECLARE @KaoQin_Code varchar(50),
@KaoQin_Latitude varchar(50)
SELECT @KaoQin_Code =KaoQin_Code, @KaoQin_Latitude=KaoQin_Latitude FROM INSERTED;
BEGIN
EXEC loopback.zjdhd_ice.dbo.pro_insert_kaoqin @KaoQin_Code, @KaoQin_Latitude;
END
go
--创建存储过程(如果已经创建的则修改存储过程,将create换成alter,下面同理)
use zjdhd_ice
go
create PROCEDURE pro_insert_kaoqin
@KaoQin_Code varchar(50),
@KaoQin_Latitude varchar(50)
AS
BEGIN
SET NOCOUNT ON
INSERT OPENQUERY (jt, 'select * from t_attendance')(attendance_dnum,lat)values(@KaoQin_Code ,@KaoQin_Latitude)
END
go
--update
--新建触发器
use zjdhd_ice
go
create TRIGGER TR_update_S_KaoQin ON zjdhd_ice.dbo.S_KaoQin
FOR update
AS
DECLARE @KaoQin_Code varchar(50),
@KaoQin_Latitude varchar(50)
SELECT @KaoQin_Code =KaoQin_Code, @KaoQin_Latitude=KaoQin_Latitude FROM INSERTED;
BEGIN
EXEC loopback.zjdhd_ice. dbo.pro_update_kaoqin @KaoQin_Code, @KaoQin_Latitude;
END
go
--新建存储过程
use zjdhd_ice
go
create PROCEDURE pro_update_kaoqin
@KaoQin_Code varchar(50) ,
@KaoQin_Latitude varchar(50)
AS
BEGIN
SET NOCOUNT ON
UPDATE OPENQUERY (jt, 'select * from t_attendance') SET lat=@KaoQin_Latitude where attendance_dnum=@KaoQin_Code
SET NOCOUNT OFF
END
go
--delete
新建触发器
use zjdhd_ice
go
create trigger tr_delete_S_KaoQin on zjdhd_ice.dbo.S_KaoQin
for delete
as
declare @KaoQin_Code varchar(50),
@KaoQin_Latitude varchar(50)
select @KaoQin_Code =KaoQin_Code, @KaoQin_Latitude=KaoQin_Latitude from deleted;
begin
exec loopback.zjdhd_ice.dbo.sp_delete_kaoqin @KaoQin_Code
end
go
新建存储过程
use zjdhd_ice
go
create PROCEDURE sp_delete_kaoqin(
@KaoQin_Code varchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
delete openquery(jt, 'select * from t_attendance') where attendance_dnum=@KaoQin_Code
END
go
完工!