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;
===========================
常用语句：
SELECT a.*
from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'd:\aaa.xls';'admin';'', data)
   AS a
 以下语句能成功,上一天语句却不行:OLE DB 提供程序 'Microsoft.Jet.OLEDB.4.0' 报错。
[OLE/DB provider returned message: 不可识别的数据库格式 'd:\aaa.xls'。]
SELECT *
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
 'Data Source="d:\aaa.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...data$
SELECT *
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',  'D