OPENROWSET problem on Microsoft.ACE.OLEDB.12.0

參考引用:OPENROWSET problem on Microsoft.ACE.OLEDB.12.0
Microsoft Access Database Engine 2010 可轉散發套件
--

PROBLEM: I am trying to use OPENROWSET with an Excel 2007 file. It seems to be partially working. Here is what I am doing:

select * FROM OPENROWSET(
  'Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=C:\Test.xlsx;HDR=YES',
  'SELECT * FROM [DataLoad$]')

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Note that I do get back a results set with the correct field names from the Excel spreadsheet, but no rows!!

As an experiment I also saved the file in Excel 2003 format and the following query ran just fine, returning all rows:
select * FROM OPENROWSET(
  'Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;Database=C:\Test.xls;HDR=YES',
  'SELECT * FROM [DataLoad$]')

This is not really a good long term solution though, as I was not planning to switch back to Excel 2003 anytime soon.

So, any ideas on how to get this to work in Excel 2007?? I messed around with the query and the data, changing the queried ranges etc., but the results were the same every time.

Are there new extended properties I can fool around with? What else could be causing this?

this worked. I scripted the actions of setting these properties as follows:

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


I did not need to restart the server after making the changes.

I searched the registry afterwards, and found that it created the following entries, although I would recommend anyone else with this problem use the T-SQL commands above:

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL10.EXP2008\Providers\Microsoft.ACE.OLEDB.12.0]
"AllowInProcess"=dword:00000001
"DynamicParameters"=dword:00000001
..........................................
First,  download and install  2007 Office System Driver: Data Connectivity  Components .
Now  you can query Excel 2007 files using the following T-SQL:
select  * FROM OPENROWSET (
   'Microsoft.ACE.OLEDB.12.0' ,
   'Excel  12.0;Database=C:\Test2.xlsx;HDR=YES' ,
   'SELECT * FROM [Sheet1$]' )

If you run into Error 7330 (and just the headers come back, no records), which might be either a 64-bit  OS or a Vista problem (I'm not sure which), then you can workaround the issue by running the  following T-SQL commands to configure how SQL will use the ACE  driver:
USE  [master]
GO
EXEC  master . dbo. sp_MSset_oledb_prop   N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC  master . dbo. sp_MSset_oledb_prop   N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

Now try again... this solved it for me.
...................................
PROBLEM: I have tried all the Scripts above and not worked for  me.
Server is -2008 64-bit,
SQL - 2008 64-bit,
no Office 2010 installed, but installed AcessDatabaseEngine_x64, Microsoft Access Runtime 2010.

and query:
SELECT * --INTO dbo.BulkLead
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 14.0;HDR=YES;IMEX=1;Database=C:\LeadTemplate.xls',
    'SELECT * FROM [Sheet1$]');

    but Error:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Please help.

I Also had various problems, like 7303 and 7399 errors with de ace.oledb driver in the following configuration
Win7 professional , 32 bit
SQL Server 2008 R2, Developer edition.
Access Database engine version 2010
Test query for xlsx:
Select * From Openrowset('Microsoft.ACE.OLEDb12.0','Excel12.0 Xml;HDR=YES;Database=C:\Test\MijnTest.Xlsx','SELECT * FROM [Blad1$]')

Only Jet.Oledb.4.0 worked, although the ACE.OLEDB was visible in SQL as linked server provider

Everything on my system seemed ok, checked all versions, settings, rights etc.

I tried all suggestions but noting helped.

Finally I deinstalled the Access Database Engine 2010 and replaced it with the 2007 version and voila...
After that I deinstalled the 2007 version and installed the 2010 version again and it kept working.
Go figure........

The only thing is that the JET.OLEDB does not work anymore. But that's not a problem, I can use ALE.OLEDB instead.