SQL中訪問(wèn)遠(yuǎn)程數(shù)據(jù)庫(kù)(MSSQL)

MSSQL訪問(wèn)遠(yuǎn)程數(shù)據(jù)庫(kù)可以使用三種方式(openrowset/opendatasource/openquery):

前提:啟用Ad Hoc Distributed Queries
1、啟用Ad Hoc Distributed Queries服務(wù)（這個(gè)服務(wù)不安全，SqlServer默認(rèn)是關(guān)閉）
2、啟用和關(guān)閉Ad Hoc Distributed Queries的方法：
　若沒(méi)有啟用，會(huì)出現(xiàn)以下錯(cuò)誤提示:

 SQL Server 阻止了對(duì)組件 'Ad Hoc Distributed Queries' 的STATEMENT'OpenRowset/OpenDatasource'的訪問(wèn)，因?yàn)榇私M件已作為此服務(wù)器安全配置的一部分而被關(guān)閉。系統(tǒng)管理員可以通過(guò)使用sp_configure 啟用 'Ad Hoc Distributed Queries'。有關(guān)啟用 'Ad Hoc Distributed Queries' 的詳細(xì)信息，請(qǐng)參閱 SQL Server 聯(lián)機(jī)叢書中的 "外圍應(yīng)用配置器"。

使用以下語(yǔ)句開啟：

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

使用完畢使用以下語(yǔ)句關(guān)閉(這是一個(gè)安全隱患):

exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure 

示例:
1、使用創(chuàng)建鏈接，再使用鏈接操作的方式/openquery:

--創(chuàng)建鏈接服務(wù)器 
exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '遠(yuǎn)程服務(wù)器名或ip地址 ' 
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用戶名 ', '密碼 ' 
--查詢 
select * from ITSV.數(shù)據(jù)庫(kù)名.dbo.表名 
select * FROM openquery(ITSV,  'SELECT *  FROM 數(shù)據(jù)庫(kù).dbo.表名 ') 
--導(dǎo)入 
select * into 表 from ITSV.數(shù)據(jù)庫(kù)名.dbo.表名 
insert openquery(ITSV,  'SELECT *  FROM 數(shù)據(jù)庫(kù).dbo.表名 ') select * from 本地表    
update b set b.列B=a.列B FROM openquery(ITSV,  'SELECT * FROM 數(shù)據(jù)庫(kù).dbo.表名 ') as a 
    inner join 本地表 b on a.列A=b.列A     --本地表導(dǎo)入遠(yuǎn)程表
    delete openquery(ITSVA,'select name from 數(shù)據(jù)庫(kù).dbo.表名where id=1')   --刪除
exec sp_dropserver  'ITSV ', 'droplogins '    --以后不再使用時(shí)刪除鏈接服務(wù)器 

2、使用openrowset 連接：

select * from openrowset( 'SQLOLEDB ', 'sql服務(wù)器名 '; '用戶名 '; '密碼 ',數(shù)據(jù)庫(kù)名.dbo.表名)    --查詢

select * into 本地新表 from openrowset( 'SQLOLEDB ', 'sql服務(wù)器名 '; '用戶名 '; '密碼 ',數(shù)據(jù)庫(kù)名.dbo.表名)   --遠(yuǎn)程數(shù)據(jù)表導(dǎo)入到本地新表(本地新表不存在)

insert into 本地舊表 from  openrowset( 'SQLOLEDB ', 'sql服務(wù)器名 '; '用戶名 '; '密碼 ',數(shù)據(jù)庫(kù)名.dbo.表名)   --從遠(yuǎn)程數(shù)據(jù)表導(dǎo)入到本地舊表(本地舊表已經(jīng)存在)

insert openrowset( 'SQLOLEDB ', 'sql服務(wù)器名 '; '用戶名 '; '密碼 ',數(shù)據(jù)庫(kù)名.dbo.表名) 
select *from 本地表    --把本地表數(shù)據(jù)導(dǎo)入到遠(yuǎn)程數(shù)據(jù)庫(kù)表

update b set b.列A=a.列A 
    from openrowset( 'SQLOLEDB ', 'sql服務(wù)器名 '; '用戶名 '; '密碼 ',數(shù)據(jù)庫(kù)名.dbo.表名)as a 
        inner join 本地表 b on a.column1=b.column1    --更新本地表數(shù)據(jù)，設(shè)置本地表數(shù)據(jù)=遠(yuǎn)程表數(shù)據(jù)  

使用OLEDB:

select * from openrowset('SQLOLEDB','Server=(local);PWD=***;UID=sa;','select * from TB.dbo.school') as t
select * from openrowset('SQLOLEDB','Server=(local);PWD=***;UID=sa;',TB.dbo.school) as t
select * from openrowset('SQLOLEDB','Server=(local);Trusted_Connection=yes;',TB.dbo.school) as t
select * from openrowset('SQLOLEDB','(local)';'sa';'***','select * from TB.dbo.school') as t
select * from openrowset('SQLOLEDB','(local)';'sa';'***',TB.dbo.school) as t
select * from openrowset('SQLOLEDB','(local)';'sa';'***','select school.id as id1,people.id as id2 from TB.dbo.school inner join TB.dbo.people on school.id=people.id') as t

使用SQLNCLI:

select * from openrowset('SQLNCLI','(local)';'sa';'***','select * from TB.dbo.school') as t
select * from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select * from 數(shù)據(jù)庫(kù).dbo.表名') as t
select * from openrowset('SQLNCLI','Server=(local);UID=sa;PWD=***;','select * from 數(shù)據(jù)庫(kù).dbo.表名') as t
select * from openrowset('SQLNCLI','Server=(local);UID=sa;PWD=***;',數(shù)據(jù)庫(kù).dbo.表名) as t
select * from openrowset('SQLNCLI','Server=(local);UID=sa;PWD=***;DataBase=數(shù)據(jù)庫(kù)','select * from dbo.表名') as t

insert openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select name from 數(shù)據(jù)庫(kù).dbo.表名 where id=1') values('ghjkl')/*要不要where都一樣，插入一行*/
update openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select name from 數(shù)據(jù)庫(kù).dbo.表名 where id=1') set name='kkkkkk'
delete from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select name from 數(shù)據(jù)庫(kù).dbo.表名 where id=1')

3、使用opendatasource:
使用SQLNCLI:

select * from opendatasource('SQLNCLI','Server=(local);UID=sa;PWD=***;').數(shù)據(jù)庫(kù).dbo.表名 as t
select * from opendatasource('SQLNCLI','Server=(local);UID=sa;PWD=***;DataBase=數(shù)據(jù)庫(kù)').數(shù)據(jù)庫(kù).dbo.表名 as t
insert opendatasource('SQLNCLI','Server=(local);Trusted_Connection=yes;').TB.dbo.school(name) values('ghjkl')/*要不要where都一樣，插入一行*/
update opendatasource('SQLNCLI','Server=(local);Trusted_Connection=yes;').TB.dbo.school set name='kkkkkk'
delete from opendatasource('SQLNCLI','Server=(local);Trusted_Connection=yes;').TB.dbo.school where id=1

使用OLEDB:

    select * from opendatasource('SQLOLEDB','Server=(local);Trusted_Connection=yes;').數(shù)據(jù)庫(kù).dbo.表名 as t
    select * from OpenDataSource('SQLOLEDB','Data Source=服務(wù)器;User ID=sa;Password=******').數(shù)據(jù)庫(kù).dbo.表名 as T