[MSSQL] 透過OPENROWSET讀取EXCEL

2013-04-09

3028
0
2013-04-10

...

STP1. 開啟Server>Facets>SurfaceAreaConfiguration>AdHocRemoteQueriesEnabled, 若未開啟錯誤訊息如下,

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.


sp_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries',1;
GO
RECONFIGURE;
GO

STP2. MyComputer>Manage>Services>SQLServer(MSSQL)>Properties>LogOn, 以管理員帳號密碼登入MSSQL, 重啟Service, 否則會出現拒絕存取的錯誤

STP3. 若是32 bit WIN OS + 32 bit Office 2003, 確認Microsoft.Jet.OLEDB.4.0存在Server>ServerObjects>LinkedServers>Providers, OPENROWSET讀取EXCEL語法如下,


SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\Book1.xls','SELECT * FROM [Sheet1$]')

STP3. 若是64 bit WIN OS + 64 bit Office 2010, 確認Microsoft.ACE.OLEDB.12.0存在Server>ServerObjects>LinkedServers>Providers, OPENROWSET讀取EXCEL語法如下,


SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\Book1.xls', 'SELECT * FROM [Sheet1$]');

TS1. 64 bit OS不支援Microsoft.Jet.OLEDB.4.0, 錯誤訊息如下,

Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

MSSQL

回首頁