SQL SERVER中openrowset与opendatasource的区别

开启OpenRowset/OpenDatasource的办法

    为安全考虑默认阻止了OpenRowset和OpenDatasource。如果未被开启会有如下的错误提示：
    SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。

   启用：

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

    关闭：

exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

 

openrowset

查询:ok

select * from openrowset('Microsoft.Jet.OLEDB.4.0', 'Excel 5.0;Database=c:/test.xls;User ID=;Password=;', 'select * from [sheet1$]')

或者:ok

select * from openrowset('Microsoft.Jet.OLEDB.4.0', 'Excel 5.0;Database=c:/test.xls;User ID=;Password=;', [sheet1$])

插入:ok

insert openrowset('Microsoft.Jet.OLEDB.4.0', 'Excel 5.0;Database=c:/test.xls;User ID=;Password=;', 'select * from [sheet1$]') select '109','ccc','202'

更新:ok

update openrowset('Microsoft.Jet.OLEDB.4.0', 'Excel 5.0;Database=c:/test.xls;User ID=;Password=;', 'select * from [sheet1$]') set  a2='bbb',a3=345 where a1= 101

删除:

不支持

opendatasource

查询:ok

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="c:/test.xls";Extended Properties="Excel 8.0;HDR=yes;IMEX=2;"')...[sheet1$]

插入:ok

insert  OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=c:/test.xls; Extended Properties="Excel 8.0;HDR=yes;IMEX=2;"')...[sheet1$] select '1','2','3'

更新:ok

update  OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=c:/test.xls;Extended Properties="Excel 8.0;HDR=yes;IMEX=2;"')...[sheet1$] set a2='111',a3='222' where a2='333'

对数字字段不支持

删除:

不支持

1.查询结果与EXCEL单元格的格式有很大关系；

2.路径中的"/"和"\"意义相同;

3.对于Excel里含有诸如'-'之类的特殊字符的工作表,在OPENDATASOURCE中无论如何都无法支持,而openrowset则可解决这个问题

特别注意:

Extended Properties='Excel 8.0;HDR=yes;IMEX=1'

A： HDR ( HeaDer Row )设置

若指定值为Yes，代表 Excel 档中的工作表第一行是栏位名称

若指定值為 No，代表 Excel 档中的工作表第一行就是資料了，沒有栏位名称

B：IMEX ( IMport EXport mode )设置

IMEX 有三种模式，各自引起的读写行为也不同，容後再述：

0 is Export mode  为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。

1 is Import mode  为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。

2 is Linked mode (full update capabilities) 为“连結模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。