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',  'Data Source="c:/account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...abc$

 

insert into OPENDATASOURCE('sqloledb','Data Source=mrp-server;User ID=sa;Password=12345').voutmmstdata.dbo.zcctest (cust_no,cust_name)
SELECT cust_no,cust_name
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:/111.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...getbom1$

 

 

select * from  OPENDATASOURCE('SQLOLEDB', 'Data Source=mrp-server;User ID=sa;Password=12345').voutmmstdata.dbo.zcctest

 

delete from opendatasource('sqloledb','Data Source=mrp-server;User ID=sa;Password=12345').voutmmstdata.dbo.zcctest

这条语句在ACCESS数据库不带密码的时候也能成功运行,加上寄密码之后按照以下写法却不行:其中abc密码.

 SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'd:/demo.dat';'admin';'abc', temp)
   AS a

=================

使用Linked Server

如果你计划多次查询外部或远程数据源，建议创建linked server。尽管创建linked server需要一点时间，但是一旦创建后，你可以非常方便地使用four-part 名称来访问远程数据源。

创建和配置linked server 一般需要两步：

第一步是创建linked server。

可以通过sp_addlinkserver系统存储过程来创建linked server，基本语法如下：

sp_addlinkedserver [ @server= ] ‘server’ [ , [@srvproduct= ] ‘product_name’ ]

[ , [ @provider= ] ‘provider_name’ ]

[ , [ @datasrc= ] ‘data_source’ ]

[ , [ @location= ] ‘location’ ]

[ , [ @provstr= ] ‘provider_string’ ]

[ , [ @catalog= ] ‘catalog’ ]

示例脚本如下所示：

EXEC sp_addlinkedserver @server = 'SQLSRV3', @srvProduct = N'SQL Server'

也可以通过SQL Server的管理界面，来创建linked server，如下图所示：

右键点击上述节点，选择“新建链接服务器…”，在弹出的窗口，就可以创建linked server了。

第二步是创建linked server的登录帐号。

在linked server创建后，下一步是创建远程服务器的登录帐号。通过本地SQL Server的登录帐号来映射到linked server的登录帐号。

创建linked server的登录帐号也有两种方法，一种是通过系统存储过程 sp_addlinkedsrvlogin，sp_addlinkedsrvlogin的基本语法如下：

sp_addlinkedsrvlogin [ @rmtsrvname = ] ‘rmtsrvname’

[ , [ @useself = ] ‘TRUE’ | ‘FALSE’ | ‘NULL’]

[ , [ @locallogin = ] ‘locallogin’ ]

[ , [ @rmtuser = ] ‘rmtuser’ ]

[ , [ @rmtpassword = ] ‘rmtpassword’ ]

sp_addlinkedsrvlogin创建远程服务器登录帐号示例脚本如下：

EXEC sp_addlinkedsrvlogin @rmtsrvname = N'SQLSRV3' , @useself = N'False' , @locallogin = N'sa' , @rmtuser = N'remoteuser' , @rmtpassword = 'passowrd'