SQL SERVER 2008查詢其他資料庫

標籤：style   blog   color   io   使用   sp   資料   div   on   

1、訪問本地的其他資料庫

--啟用Ad Hoc Distributed Queries--exec sp_configure ‘show advanced options‘,1reconfigureexec sp_configure ‘Ad Hoc Distributed Queries‘,1reconfigure-- 使用完成後,關閉Ad Hoc Distributed Queries--exec sp_configure ‘Ad Hoc Distributed Queries‘,0reconfigureexec sp_configure ‘show advanced options‘,0reconfigure--SELECT * FROM   opendatasource(‘SQLOLEDB ‘,‘Data Source=ip(或ServerName);User ID=登陸名;Password=密碼 ‘).資料庫.dbo.表名（或視圖）SELECT * FROM          opendatasource(          ‘SQLOLEDB ‘,            ‘Data Source=ITVS;              User ID=sa;Password=sa‘)               .ANSVSP.dbo.serv

 

2、訪問其他機器上的資料庫

 (1)將遠程機器上的sql server遠程開啟

 (2)將遠程機器上的防火牆關掉

SELECT * FROM          opendatasource(          ‘SQLOLEDB‘,            ‘Data Source=192.168.1.26;              User ID=sa;Password=sadan ‘)               .ANSVSP.dbo.serv

 內聯

SELECT a.bah,a.mz,b.* FROM          opendatasource(          ‘SQLOLEDB‘,            ‘Data Source=192.168.1.26;              User ID=sa;Password=sadan ‘)               .ANSVSP.dbo.serv as a inner join db_local_table b on a.bah=b.id collate Chinese_PRC_90_CI_AI;--collate Chinese_PRC_90_CI_AI 保持等號兩邊的定序一致即可

 

SQL SERVER 2008查詢其他資料庫