跨库查询（OpenDataSource）与链接服务器（Linking Server）

一：跨库查询

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./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./Articles/35943/How-to-Config-Linked-Servers-in-a-Minute#

本文基于Creative Commons Attribution 2.5 China Mainland License发布，欢迎转载，演绎或用于商业目的，但是必须保留本文的署名http://www.cnblogs.com/luminji（包含链接）。如您有任何疑问或者授权方面的协商，请给我留言。