解決在Sql Server查詢分析器中讀取Excel表出現的一些問題

在sql2005查詢分析器中讀取excel時遇到的幾個問題,收集了網上所有資源，最後問題終於解決了，網上很多答案都是粘貼、複製形式的，所以在實際的環境中並不能用，相信有很多朋友和我遇到過一樣的錯誤，為了避免後人犯同樣的錯誤，現總結分享給大家：
環境：
      系統：      windows7，
      資料庫： sqlserver2005，
      office：     office2003

SQL讀取Excel常用的方式有：
A.通過使用 OpenRowSet 和 OpenDataSource 函數
B.通過使用連結的伺服器查詢 Excel

select * from OpenRowSet
         ('Microsoft.Jet.OLEDB.4.0',
          'Excel 8.0;HDR=Yes;IMEX=1;Database=E:\bb.xls',
           [Sheet1$]
          )
一般報錯：
訊息 7399，層級 16，狀態 1，第 1 行
連結的伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 報錯。提供者未給出有關錯誤的任何資訊。
訊息 7303，層級 16，狀態 1，第 1 行
無法初始化連結的伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 的資料來源對象。

select * from OpenDataSource
         ('Microsoft.Jet.OLEDB.4.0',
          'Data Source=E:\bb.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"'
          )...[Sheet1$]  
一般報錯：
訊息 7399，層級 16，狀態 1，第 1 行
連結的伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 報錯。提供者未給出有關錯誤的任何資訊。
訊息 7303，層級 16，狀態 1，第 1 行
無法初始化連結的伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 的資料來源對象。

SELECT * FROM OpenDataSource(
                              'Microsoft.Jet.OLEDB.4.0',
                              'Data Source=E:\bb.xls;User ID=Admin;Password=;Extended properties=Excel 5.0'
                            )...[Sheet1$]
一般報錯：
訊息 7308，層級 16，狀態 1，第 1 行
因為 OLE DB 提供者 'Microsoft.Jet.OLEDB.4.0' 配置為在單一執行緒 Apartment模式下運行，所以該提供者無法用於分散式查詢。

declare @rc int
declare @server nvarchar(128)
declare @srvproduct nvarchar(128)
declare @provider nvarchar(128)
declare @datasrc nvarchar(4000)
declare @location nvarchar(4000)
declare @provstr nvarchar(4000)
declare @catalog nvarchar(128)

set @server = 'exceltosql'
set @srvproduct = 'excel'
set @provider = 'microsoft.jet.oledb.4.0'
set @datasrc = 'e:\bb.xls'
set @provstr = 'excel 8.0'
exec @rc = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,
@datasrc, @location, @provstr, @catalog

查詢：
select * from exceltosql...sheet1$

報錯：
連結的伺服器"exceltosql"的 OLE DB 提供者 "microsoft.jet.oledb.4.0" 返回了訊息 "無法啟動應用程式。工作群組資訊檔案丟失，或是已被其它使用者以獨佔方式開啟。"。
訊息 7399，層級 16，狀態 1，第 1 行
連結的伺服器 "exceltosql" 的 OLE DB 提供者 "microsoft.jet.oledb.4.0" 報錯。身分識別驗證失敗。
訊息 7303，層級 16，狀態 1，第 1 行
無法初始化連結的伺服器 "exceltosql" 的 OLE DB 提供者 "microsoft.jet.oledb.4.0" 的資料來源對象。

下面總結解決方式：
1、在SQL Server 介面區配置器中啟用 OpenRowSet 和 OpenDataSource函數
2、執行以上sql語句的資料庫必須是本機資料庫，如果為遠端資料庫就會報上面的錯誤
3、連結字串 Extended Properties屬性的內容要以分號間隔並用雙引號括起來，sheet1$ 在括弧外

4、注意office的版本4.0是office2003，12.0是office2007的版本，看看是否裝了驅動。

5、最為關鍵的是要看sql server 版本號碼，是32位的還是64位的。x64位的sql server很多的office的驅動是不支援的。

所以如果搞不成的話，不妨放到32位的sqlserver ，會有不少的收穫

原部落格地址：http://wep2008.blogbus.com/logs/35390970.html

64位下OPENROWSET運行出現錯誤

在SSMS中執行：SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'D:\nwind.mdb';
      'admin';'',Customers)

訊息7308，層級16，狀態1，第1 行
因為OLE DB 提供者'Microsoft.Jet.OLEDB.4.0' 配置為在單一執行緒 Apartment模式下運行，所以該提供者無法用於分散式查詢。
  我的環境SQL Server 2008（64位)+windows2008r2(64位)

解決辦法：

http://social.msdn.microsoft.com/Forums/en/vbgeneral/thread/58c4c61e-fa86-4809-bf7d-21bacb055d3e/

下載最新的驅動

原因是：在64SQL Engine中已經不提供jet.oledb.4.0的驅動了
解決方案：下載一個ACE.Oledb.12.0 for X64位的驅動，並把連接字串Microsoft.jet.Oledb.4.0 更改為 Microsoft.ACE.OLEDB.12.0