SQL SERVER跨数据库创建视图 sqlserver跨服务器插数据

本文对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