透過 Opendatasource 存取 Excel 2007 有問題

例如:

select f1 * into #tmp
from opendatasource(‘Microsoft.ACE.OLEDB.12.0’,’data source=F:publicEPASIPDataChiayiCountyV3.xlsx;Extended Properties="Excel 12.0;HDR=NO;IMEX=1″‘)…[第一頁$] –記得要補上最後的 $ 符號,漏了,不會取出該頁籤的內容

傳回錯誤:

訊息 7399,層級 16,狀態 1,行 3
The OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)" reported an error. Access denied.
訊息 7301,層級 16,狀態 2,行 3
Cannot obtain the required interface (“IID_IDBCreateCommand") from OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)".

可能原因是 SQL Server 的帳號不足以啟動 ACE 的 COM+ 元件,參考:http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/a525b569-77ca-41da-96e1-2ba1938dd32d

可透過元件服務賦予權限

image

設定完畢後,電腦要重新啟動才會生效

另外,可能要賦予

C:\Users\MSSQLSERVER\AppData\Local\Temp

目錄權限,若以 Windows 帳號登入時,SQL Server 會 Impersonate 該帳號存取這個目錄,建立暫存資料。但有可能權限不足

 

另外,Excel 2003(Microsoft.OLEDB.Jet.4.0)以前,預設以前 8 筆紀錄判讀欄位的資料類型和長度,這可以改

Registry HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcelTypeGuessRows

設為 0 以讀完全部的紀錄再決定資料類型和長度。

若是 Windows 64 位元,須改:

HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftJet4.0EnginesExcelTypeGuessRows

但 Excel 2008(Microsoft.OLEDB.ACE.12.0) 會自動決定資料類型,卻無法改變資料長度,造成 255 字元(510位元組)後就 truncate 該筆欄位資料,除非該欄的前八筆紀錄內,有長度超過 255 字元的紀錄,否則每有超過長度的紀錄,將逐筆傳回類似如下的錯誤訊息:

OLE DB provider ‘Microsoft.ACE.OLEDB.12.0’ for linked server ‘(null)’ returned truncated data for column ‘[Microsoft.ACE.OLEDB.12.0].F5’. The actual data length is 1082 and truncated data length is 510.

這不曉得要如何補救

 

select * into VersionControl from OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘Excel 12.0; HDR=YES; Database=C:\DataFile.xlsx’ ,’SELECT * FROM [Version Control$A2:BZ]’) T

可以從第二列開始讀取,且工作表有空白或欄位名稱空白開頭皆可,若採用 OpenDataSource 則不行。

廣告

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com 標誌

您的留言將使用 WordPress.com 帳號。 登出 /  變更 )

Google+ photo

您的留言將使用 Google+ 帳號。 登出 /  變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 /  變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 /  變更 )

w

連結到 %s

%d 位部落客按了讚: