笨小孩做开发

  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理
---安装安装mysqlconnector
/*
配置mysqlconnector

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

*/


--新建链接服务器
exec sp_addlinkedserver
@server='MySqll_Aggregation',--ODBC里面data source name
@srvproduct='MySql',--自己随便
@provider='MSDASQL',--固定这个
@datasrc=NULL,
@location=NULL,
@provstr='DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=172.17.29.33;DATABASE=bi;UID=zhaowenzhong;PORT=3306;',
@catalog=NULL

---创建连接mysql数据库的账号及密码
exec sp_addlinkedsrvlogin
@rmtsrvname='MySqll_Aggregation',----ODBC里面data source name
@useself='false',
@rmtuser='zhaowenzhong',---mysql账号
@rmtpassword='mysqldba@2015';--mysql账号其密码





---测试是否可以访问mysql数据库中的表
select from openquery(MySqll_Aggregation,'SELECT * FROM tb; ')
-----建立允许远程访问连接操作
USE [master]
GO
EXE Cmaster.dbo.sp_serveroption@server=N'MySqll_Aggregation',@optname=N'rpc out',@optvalue=N'TRUE'
GO
EXEC master.dbo.sp_serveroption@server=N'MySqll_Aggregation',@optname=N'remote proc transaction promotion',@optvalue=N'false'
GO
  
--建立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
---编写触发器和存储过程
--insert
CREATE TRIGGER TR_INSERT_TB ON  DB_TY2015.DBO.TB
FOR INSERT
AS
DECLARE@ID INT,
                                @QTY INT
                                SELECT@ID=ID,@QTY=QTY FROM INSERTED;
                                BEGIN
                                                EXEC loopback.db_ty2015.dbo.sp_insert@id,@qty;
                                END
CREATE PROCEDURE SP_INSERT
@ID INT,
@QTY INT
AS
BEGIN
                SET NOCOUNT ON
                                INSERT OPENQUERY(db_ty2015,'select * from tb')(id,qtyvalues (@id,@qty);
                SET NOCOUNT OFF
END

---update
CREATE TRIGGERT R_UPDATE_TB ON DB_TY2015.DBO.TB
FOR UPDATE
AS
DECLARE @ID INT,@QTY INT
SELECT@ID=ID,@QTY=QTY FROM INSERTED;
BEGIN
                EXEC loopback.db_ty2015.dbo.sp_update@id,@qty;
END

CREATE PROCEDURE SP_UPDATE
@ID INT,
@QTY INT
AS
BEGIN
                SET NOCOUNT ON
                                UPDATE OPENQUERY(db_ty2015,'select * from tb'SET qty=@qty WHERE id=@id
                SET NOCOUNT OFF
END

--delete
CREATE TRIGGER TR_DELETE_TB ON DB_TY2015.DBO.TB
FOR DELETE
AS

                DECLARE @ID INT
                SELECT@ID=ID FROM DELETED;
BEGIN
                EXEC loopback.db_ty2015.dbo.sp_DELETE@id;
END

CREATE PROCEDURE SP_DELETE
@ID INT
AS
BEGIN
                SET NOCOUNT ON
                                DELETE OPENQUERY(db_ty2015,'select * from tb'WHERE id=@id
                SET NOCOUNT OFF
END

-------初始化数据     表已存在的情况
insert  into  penquery(MySqll_Aggregation,'select * from bi.tb') select*from[FSLogin].[dbo].[tb]with(nolock)


---从mysql同步表结构及数据到sqlserver上
select into  [SqlServerDBName].dbo.MYTest  from   openquery(localmysql,'select * from mysqldbname.weibosession')
posted on 2018-05-12 11:55  笨小孩做开发  阅读(5096)  评论(2编辑  收藏  举报