不同伺服器資料庫之間的資料操作

不同伺服器資料庫之間的資料操作

--建立連結的伺服器
exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '遠程伺服器名或ip地址 '
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '使用者名稱 ', '密碼 '

--查詢樣本
select * from ITSV.資料庫名.dbo.表名

--匯入樣本
select * into 表 from ITSV.資料庫名.dbo.表名

--以後不再使用時刪除連結的伺服器
exec sp_dropserver  'ITSV ', 'droplogins '

--串連遠程/區域網路資料(openrowset/openquery/opendatasource)
--1、openrowset

--查詢樣本,補充插入，更新
select * from openrowset( 'SQLOLEDB ', 'sql伺服器名'; '使用者名稱'; '密碼',資料庫名.dbo.表名)

insert into openrowset( 'SQLOLEDB ', 'sql伺服器名'; '使用者名稱'; '密碼',資料庫名.dbo.表名) ( '欄位') values ( '值')

update  openrowset( 'SQLOLEDB ', 'sql伺服器名'; '使用者名稱'; '密碼',資料庫名.dbo.表名) set  '欄位'='3' where  '欄位'=5
--產生本地表
select * into 表 from openrowset( 'SQLOLEDB ', 'sql伺服器名 '; '使用者名稱 '; '密碼 ',資料庫名.dbo.表名)

--把本地表匯入遠端資料表
insert openrowset( 'SQLOLEDB ', 'sql伺服器名 '; '使用者名稱 '; '密碼 ',資料庫名.dbo.表名)
select *from 本地表

--更新本地表
update b
set b.列A=a.列A
from openrowset( 'SQLOLEDB ', 'sql伺服器名 '; '使用者名稱 '; '密碼 ',資料庫名.dbo.表名)as a inner join 本地表 b
on a.column1=b.column1

--openquery用法需要建立一個串連

--首先建立一個串連建立連結的伺服器
exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '遠程伺服器名或ip地址 '
--查詢
select *
FROM openquery(ITSV,  'SELECT *  FROM 資料庫.dbo.表名 ')
--把本地表匯入遠端資料表
insert openquery(ITSV,  'SELECT *  FROM 資料庫.dbo.表名 ')
select * from 本地表
--更新本地表
update b
set b.列B=a.列B
FROM openquery(ITSV,  'SELECT * FROM 資料庫.dbo.表名 ') as a 
inner join 本地表 b on a.列A=b.列A

--3、opendatasource/openrowset
SELECT   *
FROM   opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陸名;Password=密碼 ' ).test.dbo.roy_ta
--把本地表匯入遠端資料表
insert opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陸名;Password=密碼 ').資料庫.dbo.表名
select * from

 

 

註：

SQL Server 阻止了對組件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的訪問，因為此組件已作為此伺服器安全配置的一部分而被關閉。系統管理員可以通過使用 sp_configure 啟用 'Ad Hoc Distributed Queries'。有關啟用 'Ad Hoc Distributed Queries' 的詳細資料，請參閱 SQL Server 聯機叢書中的 "介面區配置器"。
--查詢相關資料，找到解決方案：
--
--    先啟用Ad Hoc Distributed Queries：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--    使用完成後，關閉Ad Hoc Distributed Queries：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

或者

 

 

另外

msdtc.exe是微軟分布式傳輸協調程式。該進程調用系統Microsoft Personal Web Server和Microsoft SQL Server。該服務用於管理多個伺服器

控制台－－管理工具－－服務－－找到Distributed Transaction Coordinator－－禁用
  MSDTC(分布式交易協調器)。協調跨多個資料庫、訊息佇列、檔案系統等資源管理員的事務。該服務的進程名為Msdtc.exe。