SQL Server 跨服务器连接的多种方法

本文对SQL Server跨服务器连接的方式进行总结。

1、OPENDATASOURCE

在SQL文中直接用此语句打开数据库示例:

OPENDATASOURCE(
'SQLOLEDB','Data Source=TQDBSV001
;User ID=fish;Password=2312').RackDB.dbo.CS

此方式较为简单，但存在弊端就是速度很慢。

2、OPENROWSET

包括从 OLE DB 资料来源存取远端资料需要的所有连线资讯。这个方法是在连结伺服器存取资料表的替代方法，而且是使用 OLE DB 连线与存取远端资料的一次、特定的方法。OPENROWSET 函数可以在查询的 FROM 子句中当作资料表名称来参考。根据 OLE DB Provider 的能力，OPENROWSET 函数也可以当作 Insert、Update 或 Delete 陈述式的目标资料表来参考。虽然查询可能会传回多个结果集，OPENROWSET 只传回第一个结果集。语法：

OPENROWSET ( 'provider_name'
    , { 'datasource' ; 'user_id' ; 'password'
        | 'provider_string' }
    , { [ catalog.] [ schema.] object
        | 'query' } 
    )

参数：

'provider_name'：代表登录中指定的 OLE DB Provider 的亲和名称之字元字串。

'provider_name' 没有预设值。

'datasource'：是对应到特殊 OLE DB 资料来源的字串常数。

'datasource' 是要传送到提供者 IDBProperties 介面以初始化提供者的 DBPROP_INIT_DATASOURCE 属性。一般而言，此字串包括资料库档案名称、资料库伺服器名称或提供者了解并用以寻找资料库的名称。

'user_id'：是要传送到指定的 OLE DB Provider 的使用者名称字串常数。user_id 指定连线的安全性内容且以 DBPROP_AUTH_USERID 属性传送以初始化提供者。

'password'：是要传送到 OLE DB Provider 的使用者密码字串常数。初始化提供者时，password 以 DBPROP_AUTH_PASSWORD 属性传送。

'provider_string'：是以 DBPROP_INIT_PROVIDERSTRING 属性传送以初始化 OLE DB Provider 的特定提供者连线字串。provider_string 一般会包含所有初始化提供者时需要的所有连线资讯。

catalog：是有指定物件的资料库目录或资料库名称。

schema：是指定物件的结构描述或物件拥有者名称。

object：唯一指定要操作的物件之物件名称。

'query'：传送到提供者并由提供者执行的字串常数。MicrosoftR SQL Server? 不处理此查询，但处理由提供者传回的查询结果 (传递查询)。传递查询用在不经由资料表名称而只经由命令语言显露其表格资料的提供者时，非常有用。只要查询提供者支援 OLE DB Command 物件及其强制介面，远端伺服器就支援传递查询。如需详细资讯，请参阅 SQL Server OLE DB Programmer's Reference。

备注：

如果 OLE DB Provider 支援指定的资料来源中的多个目录与结构描述，就需要资料库目录与结构描述名称。如果 OLE DB Provider 不支援，可以省略 catalog 与 schema 的值。

如果提供者只支援结构描述名称，必须指定 schema.object 格式之两个部份的名称。如果提供者只支援资料库目录名称，必须指定 catalog.schema.object 格式之三个部份的名称。

OPENROWSET 不接受变数作为其引数。

权限：

OPENROWSET 权限由传送到 OLE DB Provider 的使用者名称的权限来决定。

范例：

A. 使用有 Select 的OPENROWSET 与 Microsoft OLE DB Provider for SQL Server

以下范例使用 Microsoft OLE DB Provider for SQL Server，以存取命名为 seattle1 的远端伺服器上 pubs 资料库的 authors 资料表。从 datasource、user_id 与 password 初始化提供者，且使用 Select 来定义传回的资料列集。

格式：

Select a.*
FROM OPENROWSET('SQLOLEDB’,'ServerName';'LoginUser';'Password',
   'Select * FROM [DatabaseName].dbo.TableName orDER BY ColName1, ColName12') AS a

说明：查询所提供的驱动程序(SQLOLEDB是查询SQL Server)，查询SQL服务器ServerName下的Databasename中的数据TableName表中的数据(SQL语句)，其实用户权限是LoginUser。

例子：

USE pubs
GO
Select a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
   'Select * FROM pubs.dbo.authors orDER BY au_lname, au_fname') AS a
 
GO

B. 使用有物件的 OPENROWSET 与 OLE DB Provider for ODBC。

以下范例使用 OLE DB Provider for ODBC 与 SQL Server ODBC 驱动程式，来存取命名为 seattle1 的远端伺服器上 pubs 资料库的 authors 资料表。以 ODBC 提供者使用的 ODBC 语法指定之 provider_string 来初始化提供者，并使用 catalog.schema.object 语法来定义传回的资料列集。

格式：这连接方式是ODBC数据的驱动程序:

Select a.*
FROM OPENROWSET('MSDASQL',
   'DRIVER={SQL Server};SERVER=ServerName;UID=LoginUser;PWD=Password,
    [DatabaseName].dbo.TableName) AS a
orDER BY ColName1, ColName12
 
USE pubs
GO
Select a.*
FROM OPENROWSET('MSDASQL',
   'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',
   pubs.dbo.authors) AS a
orDER BY a.au_lname, a.au_fname
GO

C. 使用 Microsoft OLE DB Provider for Jet。

以下范例藉由Microsoft OLE DB Provider for Jet 存取 Microsoft Access Northwind 资料库中的 orders 资料表。

a、以下范例假设已经安装 Access。

1、USE pubs
GO
Select a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'c:\MSOffice\Access\Samplesorthwind.mdb';'admin';'mypwd', orders) 
   AS a
GO
2、
select * from openrowset('Microsoft.Jet.OLEDB.4.0', 
'E:\Study\Access\test.mdb';'admin';'','select * from student')
或
Select a.*
FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0', 
   'E:\Study\Access\test.mdb';'admin';'', student) 
  AS a

b、以Excel为例，必须安装了Excel。

select * 
from 
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel5.0;HDR=YES;
DATABASE=G:\WorkEveryDay\DayDo\OrderList',OrderList$)

DATABASE=G:\WorkEveryDay\DayDo\OrderList'是Excel的表名及路径,OrderList$是工作区的名字MICROSOFT.JET.OLEDB.4.0是Excel的驱动程序，也可以用MICROSOFT.JET.OLEDB.5.0,MICROSOFT.JET.OLEDB.8.0,测试所用的window 2003及SQL Server 2000其中OrderList$所面的$不能少，要不能报错，$是代表是工作区3.以VFP(DBF文件名)。

select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;
SourceDB=D:\','select * from [temp.DBF]')

说明：SourceType是数据源类型,SourceDB是数据源,后面是操作DBF的SQL查询语句。

D. 使用 OPENROWSET 与 INNER JOIN 中的其他资料表。

以下范例选取储存在相同电脑上 SQL Server Northwind 资料库的 customers 资料表的所有资料，以及 Access Northwind 资料库的 orders 资料表的所有资料。

附注 以下范例假设已经安装 Access。

USE pubs
GO
Select c.*, o.*
FROM Northwind.dbo.Customers AS c INNER JOIN 
   OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'c:\MSOffice\Access\Samplesorthwind.mdb';
'admin';'mypwd', orders) 
   AS o
   ON c.CustomerID = o.CustomerID 
GO

E.将存储过程的记录集插入到虚拟表中(执行的存储不策有全局虚拟表)。

如：

Select * into #t
FROM OPENQUERY([192.168.42.43], 'exec [Order].dbo.Or_Select_BackListDetail ''BK0607190001''')

如下：就会报错，必须先建表create table #t，而且此虚拟表的参数一定要与存储过程的参数一样。

Insert into #t

exec [Order].dbo.Or_Select_BackListDetail 'BK0607190001'

F：将打开的相应的文件（dbf,exec等等），从SQL里插入数据进行。

insert into    
--select * from 
openrowset('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=d:\',
'select * from temp.DBF') 
select top 100 orderno,shipto from ordermaster

注：

1、两数字段结构一样(长度、类型)

2、保证导出表没有为null或空的字段

3、将你上面的语句改为下面的select * from tmp.DBF，就是不要那个[]

3、建立链接服务器

if   exists   (select   1   from   master..sysservers   
where   srvname   =   'ls_Source')   
exec   sp_dropserver   'ls_Source','droplogins'   
go   exec   sp_addlinkedserver   
 'ls_Source', 'ms','SQLOLEDB','TAODBSV001' 
go
exec sp_addlinkedsrvlogin 
 'ls_Source','false','sa','read',''