允許一般使用者使用openrowset

如果要匯入外部檔案資料會使用 openrowset方式，匯入excel(xls、xlsx)、csv、txt都可以，使用前先安裝AccessDatabaseEngine.exe (64位元SQL SERVER請下載64bit)

再執行以下TSQL指令

USE [master]
GO
 
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0' , N'DynamicParameters' , 1
GO

現有個特殊需求是，建一個唯讀使用者後，執行openrowset有如下訊息

Msg 7415, Level 16, State 1, Line 2
Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.16.0' has been denied. You must access this provider through a linked server. 

select  * from openrowset('Microsoft.ACE.OLEDB.16.0','Text; HDR=NO; CharacterSet=65001;Database=d:\SQLFiles\',
'select * from [test1.txt]')

 

若要開放一般使用者使用，則再執行以下指令(以SQL2019 DB為例)

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0' , N'DisallowAdHocAccess' , 1
GO  

再使用regedit到機碼修改DisallowAdHocAccess=0

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\Providers\Microsoft.ACE.OLEDB.16.0]

 
如果讀取的txt、csv delimiter 非逗點相隔，則需在來源檔案同目錄下定義Schema.ini檔，內容如下，例如來源檔名為test1.csv，分隔符號為 !，則Schema.ini需定義如下，若有多個檔名需各別定義在.ini中。

[test1.csv]
ColNameHeader=False
Format=Delimited(!)

[test2.csv]
ColNameHeader=False
Format=Delimited(!)

[test3.csv]
ColNameHeader=False
Format=Delimited(!)