利用MS SQL实现异构数据库的分布式查询的t-sql代码

分布式|数据|数据库

        /*利用MS SQL实现异构数据库的分布式查询的t-sql代码*/
exec sp_addlinkedserver  'MS_SQL','','SQLOLEDB','172.18.9.20'
exec sp_addlinkedsrvlogin 'MS_SQL','false',null,'Sa','capec' /*创建一个sqlserver对sqlserver的数据库远程链接*/

exec sp_addlinkedserver 'ora', 'Oracle', 'MSDAORA', 'hj'
exec sp_addlinkedsrvlogin 'ora', false, 'sa', 'ccense', '******' /*创建一个sqlserver对Oracle的数据库远程链接*/

SELECT V_REC.CUSTOMERID,REC.OPDT,REC.COLLECTDT,REC.UPLOAD,rec.opcount rec_opcount,v_rec.opcount as v_rec_opcount
FROM ora..CCENSE.REC_CUST_ACC V_REC INNER JOIN
      REC_CUST_ACC REC ON V_REC.CUSTOMERID = REC.CUSTOMERID AND
      V_REC.OPCOUNT = REC.OPCOUNT
WHERE (REC.UPLOAD = 0) /*MS SQL本机与Oracle的分布式查询*/

SELECT V_REC.CUSTOMERID,REC.OPDT,REC.COLLECTDT,REC.UPLOAD,rec.opcount rec_opcount,v_rec.opcount as v_rec_opcount
FROM ora..CCENSE.REC_CUST_ACC V_REC INNER JOIN
      MS_SQL.localcost.dbo.rec_cust_acc REC ON V_REC.CUSTOMERID = REC.CUSTOMERID AND
      V_REC.OPCOUNT = REC.OPCOUNT
WHERE (REC.UPLOAD = 0) /*远程MS SQL与Oracle的分布式查询*/