sql sever跨資料庫複寫資料的方法【轉】

標籤：das   sqlserver   schema   ted   記錄   語句   ever   query   命令   

1，用Opendatasource系統函數

詳細的用法已經注釋在sql代碼中了。這個是在sqlserver到sqlserver之間的倒資料。2005，2008，2012應該都是適用的。

--從遠程伺服器192.168.66.154上查詢100條資料，然後匯入到dbo.dquestiondata中insert into dbo.dquestiondataselect top 100 * fromopendatasource(‘sqloledb‘,‘data source=192.168.6.154;user id=sa;password=xxxxxx‘).Answer.dbo.DQuestionData--opendatasource 是一個系統函數，第一個參數是Provider Name，第二個參數是Oledb連結字串，--注意連接字串裡沒有指定資料庫；資料庫名稱，Schema，表名在opendatasource函數後面書寫。--執行上面的語句會報如下的錯，原因是沒有開啟遠程查詢支援    --SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource‘ of component ‘Ad Hoc Distributed Queries‘     --because this component is turned off as part of the security configuration for this server.     --A system administrator can enable the use of ‘Ad Hoc Distributed Queries‘ by using sp_configure.     --For more information about enabling ‘Ad Hoc Distributed Queries‘, see "Surface Area Configuration" in SQL Server Books Online.--解決辦法1：在圖形介面中，SQL Server 安全配置-->機能的安全配置-->開啟遠程查詢Openrowset和opendatasource的支援--解決辦法2：用sp_confing系統預存程序，以命令列的方式開啟--首先必須開啟‘show advanced options’進階選項，因為‘Ad Hoc Distributed Queries’屬於進階選項exec sp_configure ‘show advanced options‘ ,1reconfigure--下面這步可以省略，不帶參數的sp_configure，表示查看所有可用的選項--記住上面一定要加reconfigure語句，目的是使選項立即生效，不然我們--還是看不到‘Ad Hoc Distributed Queries‘進階選項exec sp_configure--開啟‘Ad Hoc Distributed Queries‘選項exec sp_configure ‘Ad Hoc Distributed Queries‘,1reconfigure--記得用完選項後，關閉這些選項exec sp_configure ‘Ad Hoc Distributed Queries‘,0reconfigureexec sp_configure ‘show advanced options‘,0reconfigure--查看一下是不是關閉了exec sp_configure

 2，用openrowset系統函數

--1,Microsoft OLE DB Provider for SQL Server--注意第二部分連結字串的寫法很是奇怪Server，user，passwrod是用“；”串連的。select top 100 * fromopenrowset(‘sqloledb‘,‘192.168.6.154‘;‘sa‘;‘xxxxx‘,Answer.dbo.DQuestionData) --2,ODBC資料來源的方式：Microsoft OLE DB Provider for ODBC Driversselect  top 100 * fromopenrowset(‘MSDASQL‘,‘DRIVER={SQL Server};SERVER=192.168.6.154;UID=sa;PWD=xxxxx‘,Answer.dbo.DQuestionData) 

 上面的兩種方法都會用到oledb供應商的名稱，下面的這個系統預存程序可以查看oledb提供者的名稱

--用於查看oledb提供者名稱EXEC master..xp_enum_oledb_providers 

3，用連結的伺服器

如果要多次用到遠程查詢，每次都寫那麼長的連結字串有點麻煩。可以考慮重複利用。

用連結的伺服器可以很好的解決這個問題

-- 建立連結的伺服器exec sp_addlinkedserver ‘svr_link‘,‘‘,‘sqloledb‘,‘192.168.6.154‘-- 建立登入資訊exec sp_addlinkedsrvlogin ‘svr_link‘,‘false‘,null,‘sa‘,‘xxxx‘--查詢 格式為：連結的伺服器.資料庫名.架構名.表名select top 100 * from svr_link.Answer.dbo.DQuestionData-- 刪除連結的伺服器exec sp_dropserver ‘svr_link‘,‘droplogins‘

需要注意的幾點：1，雖然上面的例子都是從遠程伺服器select資料到本地，但我們同樣可以將本地的資料導向遠程伺服器

-- 往遠端資料庫192.168.6.154的Answer資料庫的test表插入兩條資料insert into svr_link.Answer.dbo.testselect 1,‘zhang‘ union allselect 2,‘wang‘

2，有了連結的伺服器，就可以用openquery系統函數了

--檢索資料select * from openquery(svr_link,‘select  * from Answer.dbo.test‘)-- 插入資料insert into openquery(svr_link,‘select  * from Answer.dbo.test‘)select 3,‘li‘

3，opendatasource和連結的伺服器都是只返回Server部分，要查詢表需要進一步指定資料庫，架構和表名。而openrowset和openquery返回的是記錄集

sql sever跨資料庫複寫資料的方法【轉】