一：跨庫查詢

Openrowset/opendatasource() is an ad-hoc method to access remote server's data. So, if you only need to access the remote server's data once and you do not to persist the connection info, this would be the right choice. Otherwise, you should create a linked 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

SQL如下：

select * from [User] a
? left join OPENDATASOURCE(
???????? 'SQLOLEDB',
???????? 'Data Source=192.168.1.20;User ID=sa;Password=yhbj'
???????? ).Kitty2.dbo.Question b on a.Id = b.UserId

結果：

?

二：鏈接服務器

Linked server is a persistent registration for the remote server. This allows you to define the connection property once and be able to use the server "alias" over and over again.

STEP1:

STEP2:

如果是在同一個局域網內，不妨直接選中“SQLSERVER”鏈接，

STEP3:

STEP4:

?

三：OpenDataSource VS Linking Server

有這樣一個實例：

using linked server
SELECT * FROM mylinkedserver.[mydatabase].[dbo].[mytable]
requires 10s
but when using
SELECT * FROM OPENDATASOURCE('SQLNCLI','Data Source=myserver;User ID=sa;Password=xxxxxx').[mydatabase].[dbo].[mytable]
it lasts for 10 mins and still continue

其它基于LINK SERVER的效率問題的一些描述：

http://www.sql-server-performance.com/2007/linked-server/

?

四：代碼實現

static void Main(string[] args)
{
??? var x = Query("select * from [User] a left join [192.168.1.20].Kitty2.dbo.Question b on a.Id = b.UserId ");
??? Console.WriteLine(x.Tables[0].Rows.Count);

??? var y = Query("select * from [User] a"
????????????????? + " left join OPENDATASOURCE("
????????????????? + " 'SQLOLEDB','Data Source=192.168.1.20;User ID=sa;Password=yhbj' "
????????????????? + " ).Kitty2.dbo.Question b on a.Id = b.UserId ");
??? Console.WriteLine(y.Tables[0].Rows.Count);
}

private static string connectionString = @"Data Source=192.168.1.19;Initial Catalog=Kitty1;Integrated Security=False;User ID=sa;Password=yhbj;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";

public static DataSet Query(string SQLString)
{
??? using (SqlConnection connection = new SqlConnection(connectionString))
??? {
??????? connection.Open();
??????? SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
??????? DataSet ds = new DataSet();
??????? command.Fill(ds, "ds");
??????? return ds;
??? }
}

參考：

http://www.cnblogs.com/EasonWu/archive/2012/09/26/2704018.html

http://msdn.microsoft.com/en-us/library/ms188279.aspx

http://msdn.microsoft.com/en-us/library/ms188721.aspx

http://msdn.microsoft.com/en-us/library/ms188477.aspx

http://msdn.microsoft.com/en-us/library/aa259589%28v=sql.80%29.aspx

http://msdn.microsoft.com/en-us/library/aa933295%28v=sql.80%29.aspx

http://msdn.microsoft.com/en-us/library/aa259581%28v=sql.80%29.aspx

http://www.codeproject.com/Articles/35943/How-to-Config-Linked-Servers-in-a-Minute#

轉載于:https://www.cnblogs.com/luminji/p/3445407.html

總結

以上是生活随笔為你收集整理的跨库查询（OpenDataSource）与链接服务器（Linking Server）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。