/*利用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的分布式查詢*/