跨库查询(OpenDataSource)与链接服务器(Linking Server)_.NET_编程开发_程序员俱乐部

中国优秀的程序员网站程序员频道CXYCLUB技术地图
热搜:
更多>>
 
您所在的位置: 程序员俱乐部 > 编程开发 > .NET > 跨库查询(OpenDataSource)与链接服务器(Linking Server)

跨库查询(OpenDataSource)与链接服务器(Linking Server)

 2013/11/27 15:31:57  Luminji  博客园  我要评论(0)
  • 摘要:一:跨库查询Openrowset/opendatasource()isanad-hocmethodtoaccessremoteserver'sdata.So,ifyouonlyneedtoaccesstheremoteserver'sdataonceandyoudonottopersisttheconnectioninfo,thiswouldbetherightchoice.Otherwise,youshouldcreatealinkedserver
  • 标签: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

结果:

image

 

二:链接服务器

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:

image

STEP2:

如果是在同一个局域网内,不妨直接选中“SQLSERVER”链接,

image

STEP3:

image

STEP4:

image

 

三: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#
发表评论
用户名: 匿名