用openrowset連接遠程SQL或插入數據

--如果只是臨時訪問,可以直接用openrowset

--查詢示例

select * from openrowset('SQLOLEDB', 'sql服務器名'; '用戶名'; '密碼', 數據庫名.dbo.表名)

--導入示例

select * into 表 from openrowset('SQLOLEDB' ,'sql服務器名';'用戶名';'密碼' ,數據庫名.dbo.表名)

--創建鏈接服務器

exec sp_addlinkedserver?? 'srv_lnk', '', 'SQLOLEDB','遠程服務器名或ip地址'

exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'用戶名','密碼'

go

--查詢示例

select * from srv_lnk.數據庫名.dbo.表名

--導入示例

select * into 表 from srv_lnk.數據庫名.dbo.表名

--以后不再使用時刪除鏈接服務器

exec sp_dropserver 'srv_lnk','droplogins'

go

--下面的示例訪問來自某個表的數據，該表在 SQL Server 的另一個實例中。

SELECT *FROM OPENDATASOURCE('SQLOLEDB','Data Source=ServerName;User ID=MyUID;Password=MyPass').Northwind.dbo.Categories

下面是個查詢的示例，它通過用于 Jet 的 OLE DB 提供程序查詢 Excel 電子表格。

SELECT *FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:Financeaccount.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

由于項目需要,在開發過程遇上跨數據庫服務器訪問數據的問題.

在網絡上一搜,資料確實不少,不過解決自己的問題也花了大半天的時候,在這里花些時間總結一下.

1.確立問題

由于需要進行跨數據庫服務器的查詢操作,所以我個人把其定位于分布式查詢數據的問題.

2.解決方案

第一個概念.鏈接服務器.

鏈接服務器一般用來處理分布式查詢。當客戶端應用程序通過鏈接服務器執行分布式查詢時，SQL Server 將分析該命令，并向 OLE DB 發送行集請求。行集請求的形式可以是對提供程序執行查詢或從提供程序打開基表。

分布式查詢可以訪問來自多種異類數據源的數據，而這些數據可存儲在相同或不同的計算機上。Microsoft? SQL Server? 2000 通過使用 OLE DB（Microsoft 通用數據訪問應用程序接口 (API) 規范）支持分布式查詢。

鏈接服務器配置允許 Microsoft? SQL Server? 對其它服務器上的 OLE DB 數據源執行命令。鏈接服務器具有以下優點：

遠程服務器訪問。

對整個企業內的異類數據源執行分布式查詢、更新、命令和事務的能力。

能夠以相似的方式確定不同的數據源。

鏈接服務器組件

鏈接服務器的定義指定了 OLE DB 提供程序和 OLE DB 數據源。

OLE DB 提供程序是管理特定數據源和與特定數據源進行交互的動態鏈接庫 (DLL)。OLE DB 數據源標識可通過 OLE DB 訪問的特定數據庫。盡管通過鏈接服務器的定義所查詢的數據源通常是數據庫，但也存在適用于多種文件和文件格式的 OLE DB 提供程序，包括文本文件、電子表格數據和全文內容檢索結果。下表說明了最常用于 SQL Server 的 OLE DB 提供程序和數據源示例。

OLE DB提供程序???????????????????????????????? OLE DB 數據源

用于 SQL Server 的 Microsoft OLE DB 提供程序???? SQL Server 實例

用于 Jet 的 Microsoft OLE DB 提供程序???????????? mdb 數據庫文件的路徑名

用于 ODBC 的 Microsoft OLE DB 提供程序?????????? 指向某個具體數據庫的 ODBC 數據源名稱

用于 oracle 的 Microsoft OLE DB 提供程序???????? 指向 oracle 數據庫的 SQL*Net 別名

用于索引服務的 Microsoft OLE DB 提供程序???????? 能夠對其執行屬性搜索或全文檢索的內容文件

說明?? SQL Server 只針對分別用于 SQL Server、Jet、Oracle、索引服務和 ODBC 的 Microsoft OLE DB 提供程序進行了測試。然而，SQL Server 分布式查詢旨在與任何實現了必需的 OLE DB 接口的 OLE DB 提供程序一起使用。

為了使數據源能夠通過鏈接服務器返回數據，那個數據源的 OLE DB 提供程序 (DLL) 必須位于 SQL Server 所在的服務器上。

鏈接服務器一般用來處理分布式查詢。當客戶端應用程序通過鏈接服務器執行分布式查詢時，SQL Server 將分析該命令，并向 OLE DB 發送行集請求。行集請求的形式可以是對提供程序執行查詢或從提供程序打開基表。

管理鏈接服務器的定義

設置鏈接服務器時，請注冊 SQL Server 的連接信息和數據源信息。完成注冊后，該數據源總可以用單個邏輯名稱(即實例名)引用。

可以使用存儲過程或 SQL Server 企業管理器來創建或刪除鏈接服務器的定義。

使用存儲過程：

使用 sp_addlinkedserver 創建鏈接服務器的定義。若要查看有關給定的 SQL Server 實例中定義的鏈接服務器的信息，請使用 sp_linkedservers。有關更多信息，請參見 sp_addlinkedserver 和 sp_linkedservers。

使用 sp_dropserver 刪除鏈接服務器的定義。還可以使用此存儲過程刪除遠程服務器。

使用 SQL Server 企業管理器：

使用 SQL Server 企業管理器控制臺樹和"鏈接服務器"節點（在"安全性"文件夾內）來創建鏈接服務器的定義。為鏈接服務器定義名稱、提供程序屬性、服務器選項和安全選項。有關各種為不同的 OLE DB 數據源設置鏈接服務器的方式以及要使用的參數值的更多信息，請參見 sp_addlinkedserver。

通過右擊鏈接服務器并單擊"屬性"命令，可編輯鏈接服務器的定義。

通過右擊鏈接服務器并單擊"刪除"命令，可刪除鏈接服務器的定義。

當對鏈接服務器執行分布式查詢時，請對每個要查詢的數據源指定完全合法的、由四部分組成的表名。這個由四部分組成的名稱的格式應是：linked_server_name.catalog.schema.object_name。

?? 眾所周知，在大型的數據庫系統設計中，為了提升效率，不可避免的要將不用的業務放在不同的數據實例上，因此我們使用到了鏈接服務器，鏈接服務器為大家在不同的服務之間進行分布式數據操作提供了便利。本例子基于windows 2003操作系統，Sql server 2000(sp4)數據庫。

首先要打開你本地服務器和遠程服務器的MSDTC：

開始-->Microsoft SQL Server-->服務管理器-->選擇服務中的 Distributed Transaction Coordinator（此即DTC），點擊“啟動”按鈕，啟動MSDTC.

打開以后，在本地服務器上注冊遠程服務器的信息，創建linkedServer，操作代碼如下：

exec?? sp_addlinkedserver???? 'Far_MDB','','SQLOLEDB','YOIIO005','','','MDB'

然后使用sp_linkedservers，查看此服務器是否已經成功注冊為鏈接服務器，如果已經注冊成功，則使用

sp_addlinkedsrvlogin?? 'Far_MDB','false',null,'sa','****'，注冊login帳號，然后你就可以執行一個Sql語句，試試看看是否可以從鏈接服務器中操作數據了，

例如：

SELECT *

FROM OPENQUERY(Far_MDB, 'SELECT * FROM MDB_Member')

這就是從我的鏈接服務器當中去查詢MDB_Member表的數據

備注：

1、關于鏈接服務器的詳細情況請參閱聯機叢書中的sp_addlinkedserver、 sp_addlinkedsrvlogin、sp_addserver、sp_dropserver、sp_serveroption、sp_linkedservers等系統級存儲過程。

2、關于此鏈接服務器的創建及其使用在不同環境下（全部內網服務器、一內網一公網服務器、全公網服務器）測試，均告通過。

3、有時候在存儲過程中訪問鏈接服務器的時候可能會碰到如下的提示，必須要對ANSI warning 和ANSI nulls進行設置，這時候按照如下的方法進行設置：

設置本地數據庫的屬性-->連接-->選上ANSI warning 和 ANSI nulls

如何訪問SQL Server數據庫

無論是從桌面Windows電腦上還是在Pocket PC設備上訪問SQL Server數據庫，首先都需要建立數據庫連接。使用Visual Studio 2005開發桌面Windows應用時，開發人員可以從服務器資源管理器中拖拉數據表到窗體上，但是.NET Compact Framework不支持數據設計器。這意味著開發人員需要編程實現建立數據庫連接。.NET Compact Framework的System.Data.SqlClient命名空間同樣不支持跨服務器的事務和連接池。設計移動應用解決方案時，應該將數據庫事務限制發生在服務器上的單獨數據庫中。

因為.NET Compact Framework的System.Data.SqlClient命名空間不支持數據庫連接池，所以在Pocket PC設備上建立SQL Server數據庫連接時會產生一定的延時。在桌面Windows編程中，要求盡可能縮短保持與數據庫連接的時間以及減少數據庫連接次數。而在開發Pocket PC上的數據庫應用程序時，最佳策略是提前創建數據庫連接，并盡量在整個應用程序生命周期內保持數據庫連接來減少連接次數。

下面通過設計開發一個用途廣泛的Pocket PC數據庫應用程序，介紹如何訪問服務器端數據。這個Pocket PC應用程序需要引用System.Data.Common、System.Xml和System.Data.SqlClient命名空間。

如何用sql語句進行跨庫查詢

在機器B上的數據庫database2中創建一個臨時表#tmp，

內容就是機器A上的數據庫database1里面的Table1。

如何用sql語句完成？（不借用dts等）

SQL code --創建鏈接服務器

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.表名)

--生成本地表

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 本地表

用 鏈接服務器

OPENROWSET

OPENDATASOURCE

都可以

鏈接服務器.database1.dbo.table1

鏈接服務器.database1.dbo.table1

SQL code

select * into #

from openrowset('sqloledb','ip';'sa';'','select * from pubs.dbo.jobs')

如果只是偶爾使用 就用opendatasource/openrowset 固定的頻繁使用建linked server

?

轉自: http://www.cnblogs.com/railgunman/archive/2010/12/25/1916780.html