OPENROWSET函数使用

 

OPENROWSET

select * from openrowset('MSDASQL',

'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=d:/',

'select * from 表名 ')

-------------------------------------------------------------------------------- -----------------------------

包括從 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 資料表。

1. 附註   以下範例假設已經安裝 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

2. 是以 Excel 为例，必须安装了 Excel

select * 

from

OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.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 ，就是不要那个 []