SQL Server连接远程数据源

SQL Server连接远程数据源的基本方法有下面三种：
OPENDATASOURCE
: The OPENDATASOURCE function is used to
specify connection information for a remote data source by specifying
the OLE DB provider and an initialization string. OPENDATASOURCE can be
used directly within a SELECT, INSERT, UPDATE, or DELETE statement.
 
OPENROWSET
: The OPENROWSET function is used to specify
connection information for a remote data source and the name of an
object that will return a result set (such as a stored procedure) or a
query that will return a result set. Like OPENDATASOURCE, OPENROWSET
can be used directly within a SELECT, INSERT, UPDATE, or DELETE
statement.
 
Linked servers
: A linked server is an object within SQL
Server that defines the connection properties of another SQL Server.
When defined, queries can connect to the remote server using a
four-part name, such as
SQLSrv1.AdventureWorks.person.Contact
The four-part name identifies the server (SQLSrv1), the database
(AdventureWorks), the schema (Person), and the object (Contact table).
Linked servers are explored in more depth in the final section of this
chapter.
 
其中OPENDATASOURCE和OPENROWSET方法一般用来做临时查询（ad hoc query），如果需要经常的查询远程数据，则建议创建linked servers。但是，默认情况ad hoc query 是禁用的，需要手动启动：
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad hoc Distributed Queries’, 1;
GO
RECONFIGURE;
 
然后，就可以使用OPENDATASOURCE 查询远程数据库了，OPENDATASOURCE 基本语法如下：OPENDATASOURCE ( provider_name, init_string )。示例代码如下：
SELECT *
from OPENDATASOURCE(‘SQLNCLI’,
‘Data Source=SQL08;Integrated Security=SSPI’)
.Sales.dbo.Customers
上述代码从SQL08服务器上读取Sales数据库的Customers表的内容。
 
也可以通过OPENROWSET方法查询远程数据库，OPENROWSET和OPENDATASOURCE方法相似，基本的差异是
OPENROWSET总是返回结果集，而OPENDATASOURCE方法除了可以返回结果