初次用SqlServer查看本地的Excel檔案時需要注意的地方

標籤：des   style   blog   http   color   使用   os   io   

 

 

日常用到通過SqlServer 讀取Excel檔案的案例 ，記錄下來

 

檔案路徑 ：C:\Users\Administrator\Desktop\icd10.xls

1.查詢語句：

SELECT  *
FROM    OPENDATASOURCE(‘Microsoft.Jet.OLEDB.4.0‘,
                       ‘Data Source="C:\Users\Administrator\Desktop\icd10.xls";User ID=Admin;Password=;Extended properties=Excel 5.0‘)...[sheet1$]  

首先會報以下錯誤

訊息 15281，層級 16，狀態 1，第 1 行
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  

GO  

exec sp_configure ‘Ad Hoc Distributed Queries‘,1  

reconfigure  

GO 

 

可以在查詢後可以再設定回去

exec sp_configure ‘show advanced options‘,0   

reconfigure  

GO  

 exec sp_configure ‘Ad Hoc Distributed Queries‘,0
 reconfigure 
 GO 

其次會報錯：

訊息 7399，層級 16，狀態 1，第 2 行
連結的伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 報錯。提供者未給出有關錯誤的任何資訊。
訊息 7303，層級 16，狀態 1，第 2 行
無法初始化連結的伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 的資料來源對象。

解決方案：

物件總管——>伺服器對象——>串連伺服器——>提供者——>Microsoft.Jet.OLEDB.4.0——>(右鍵屬性，介面選項全不啟用)

 

 

全部去掉√即可

問題：

訊息 7302，層級 16，狀態 1，第 1 行 無法建立連結的伺服器 "(null)" 的 OLE DB 提供者 "MSDASC" 的執行個體。

 

解決：

將SQL Server（MSSQLSERVER）和SQL Full-Filter Deamon Launcher（MSSQLSERVER）兩個服務的登入身份改為LocalSystem

 

問題：

連結的伺服器"(null)"的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 返回了訊息 "外部表格不是預期的格式。"。 訊息 7303，層級 16，狀態 1，第 1 行 無法初始化連結的伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 的資料來源對象。

解決：

Microsoft.Jet.OLEDB.4.0是Microsoft Jet引擎，這適用於2003版本。在2007中，微軟對其旗下 Access 與 Excel 的主要檔案格式進行修改，並且重新命名為 .accdb（Access 2007 資料庫檔案）與 .xlsx（Excel 2007 檔案），因此未被 Microsoft Jet 引擎所支援，不過微軟也很快的提出了Microsoft Office 2007 Desktop Drivers: Data Connectivity Components 來支援，目前的解決方案就是把連接字串中的資料提供者改為 Microsoft.ACE.OLEDB.12.0。

下載安裝： Microsoft Office 2007 Desktop Drivers: Data Connectivity Components   http://www.microsoft.com/zh-cn/download/details.aspx?id=23734

SELECT * FROM OpenDataSource( ‘Microsoft.ACE.OLEDB.12.0‘,‘Data Source="D:\KK.KK.xlsx";User ID=Admin;Password=;Extended properties=Excel 5.0‘)...[Sheet1$]  

 

問題：

訊息 7314，層級 16，狀態 1，第 1 行 連結的伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 不包含表 "Sheet1$"。該表不存在，或者目前使用者沒有訪問該表的許可權。

 

最終解決：

在網上搜尋一番，終於找到原因了！

原來我的excel列表名字不是Sheet1，而是 KK！ 所以上面的所有  Sheet1$ 都改為 KK$!!

完整代碼如下（兩中寫法）：

 

[sql] view plaincopyprint?

1. --  
2.   
3.   
4. exec sp_configure ‘show advanced options‘,1   
5. reconfigure  
6. GO  
7. exec sp_configure ‘Ad Hoc Distributed Queries‘,1  
8. reconfigure  
9. GO  
10.    
11.   
12. SELECT * FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0‘,‘Data Source="D:\KK.xls";User ID=Admin;Password=;Extended properties=Excel 5.0‘)...[KK$]    
13. SELECT * FROM OpenDataSource( ‘Microsoft.ACE.OLEDB.12.0‘,‘Data Source="D:\KK.xlsx";User ID=Admin;Password=;Extended properties=Excel 12.0‘)...[KK$]    
14.   
15. SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0‘, ‘Excel 8.0;Database=D:\KK.xls‘,‘SELECT * FROM [KK$]‘)  
16. select * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0‘,‘Excel 12.0;Database=D:\KK.xlsx;HDR=YES‘,‘SELECT * FROM [KK$]‘)    
17.   
18.   
19. exec sp_configure ‘Ad Hoc Distributed Queries‘,0  
20. reconfigure  
21. GO  
22. exec sp_configure ‘show advanced options‘,0  
23. reconfigure   
24. GO  
25.   
26.   
27. --  

--exec sp_configure ‘show advanced options‘,1 reconfigureGOexec sp_configure ‘Ad Hoc Distributed Queries‘,1reconfigureGO SELECT * FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0‘,‘Data Source="D:\KK.xls";User ID=Admin;Password=;Extended properties=Excel 5.0‘)...[KK$]  SELECT * FROM OpenDataSource( ‘Microsoft.ACE.OLEDB.12.0‘,‘Data Source="D:\KK.xlsx";User ID=Admin;Password=;Extended properties=Excel 12.0‘)...[KK$]  SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0‘, ‘Excel 8.0;Database=D:\KK.xls‘,‘SELECT * FROM [KK$]‘)select * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0‘,‘Excel 12.0;Database=D:\KK.xlsx;HDR=YES‘,‘SELECT * FROM [KK$]‘)  exec sp_configure ‘Ad Hoc Distributed Queries‘,0reconfigureGOexec sp_configure ‘show advanced options‘,0reconfigure GO--

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

 

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

成功之後，試回原來的操作：

 

1. 以下這個對Microsoft.Jet.OLEDB.4.0沒有影響,對 Microsoft.ACE.OLEDB.12.0 有影響(介面選項要全不啟用)：

物件總管——>伺服器對象——>串連伺服器——>提供者——>Microsoft.Jet.OLEDB.4.0——>(右鍵屬性，介面選項全不啟用)

 

 

2. 以下兩個服務身份由LocalSystem個改回 NetWord Service ，對Microsoft.Jet.OLEDB.4.0沒有影響,對 Microsoft.ACE.OLEDB.12.0 有影響

將SQL Server（MSSQLSERVER）和SQL Full-Filter Deamon Launcher（MSSQLSERVER）兩個服務的登入身份改為LocalSystem

 

SELECT * FROM OpenDataSource( ‘Microsoft.ACE.OLEDB.12.0‘,‘Data Source="D:\KK.xlsx";User ID=Admin;Password=;Extended properties=Excel 12.0‘)...[KK$]  

錯誤：

訊息 7302，層級 16，狀態 1，第 1 行 無法建立連結的伺服器 "(null)" 的 OLE DB 提供者 "MSDASC" 的執行個體。

 

select * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0‘,‘Excel 12.0;Database=D:\KK.xlsx;HDR=YES‘,‘SELECT * FROM [KK$]‘) 

錯誤：

訊息 7302，層級 16，狀態 1，第 1 行 無法建立連結的伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 的執行個體。