【转】在sqlserver下增加MYSQL的链接服务器,实现分布式数据库开发第一步
首先要在SQLserver上服务器上这装ODBC对mysql的支持,我下载了mysql-connector-odbc-5.1.5-win32.rar,安装后在ODBC中有了DRIVER={MySQL ODBC 5.1 Driver}
第二步,按下面的SQL脚本来执行
-- 查询已有的链接服务器 exec sp_linkedservers; -- 脚本方式 EXEC sp_addlinkedserver @server='MySQLTest', @srvproduct = 'zerolone', @provider = 'MSDASQL', @datasrc = NULL, @location = null, @provstr = 'DRIVER={MySQL ODBC 5.1 Driver};SERVER=127.0.0.1;DATABASE=zerolone;UID=root;PORT=3306;', @catalog = NULL go EXEC sp_addlinkedsrvlogin @rmtsrvname='MySqlTest', @useself='false', @rmtuser='root', @rmtpassword='123456'; go SELECT * FROM OPENQUERY (PCTMYSQL2 ,'select * from monolithpro_cate' ) INSERT OPENQUERY (PCTMYSQL2, 'SELECT id,parentid,level,title FROM monolithpro_cate') VALUES ('','0','0','zhangzongqi'); UPDATE OPENQUERY (PCTMYSQL2, 'SELECT parentid,level,title FROM monolithpro_cate WHERE id = 320') SET parentid = '2',LEVEL=3, title=title+'pct'; DELETE OPENQUERY (PCTMYSQL2, 'SELECT id FROM monolithpro_cate WHERE id = 316');
最后,好了可以实现在SQLserver下,添删改MYSQL里的数据了。