The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.

Question

Hi,

I am importing Excel 2007 file into SqlServer 2005 (Sqlexpress). The office 2007 is not installed in the machine. I have downloaded and installed "AccessDatabaseEngine". When I execute following statement

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

The following error is comming:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Can any one suggest what should I do to resolve?

Thanks,
Parmeshthee.

---

Learning .Net 3.5

Answer 1

What account was the sql server serivce configured to run under? Does the account has permission to "D:\"?

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

Thanks for the quick response, Ricky.

The sql server service is configured with dwSql account and the machine login account is different. I have given the full permission on D drive to dwSql account.

But When I execute ( SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\File1.xlsx', 'SELECT * FROM [Sheet1$]'); using sa account , it is giving error:

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

But  using windows authentication then it gives

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".


Any other configuration/Idea to resolve it?

Thanks,
Parmeshthee

---

Learning .Net 3.5

Answer 3

Hi 

Is this by any chance a SQL Server 2005 Express X64 edition ?  SQL Server X64 will not be able to load the ACE OLEDB 32 bit provider. And we do not have a 64 bit provider for Microsoft ACE OLEDB ?

---

Bindesh V[MSFT]-SQL Developer Support

Answer 4

Hi

I am also getting the same error on my 32 bit machine

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

However it works with the provider "Microsoft.Jet.OLEDB.4.0".

How to resolve it?

Answer 5

Hi

After reading registry entries for other providers, I have added a new entry to registry and it's working fine now Windows 7 32 bit. It seems that Microsoft does not want to populate ACE OLEDB provider.

Create a .reg file with text below and replace MSSQL10 (10 = SQL Server 2008) with your MSSQL version and SQLADV with SQL Server instance name. Save the file and run. It'll add this entry to registry and you can use OPENROWSET with ACE OLEDB providers.



Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLADV\Providers\Microsoft.ACE.OLEDB.12.0]
"AllowInProcess"=dword:00000001

Answer 6

Try run this:

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

 Then try your query. I have met this issue before and this should works.

Answer 7

I have a very similar error linking to Office 2007 Excel spreadsheet from SQL 2005

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "eelist" reported an error. Access denied.

Msg 7301, Level 16, State 2, Line 1

Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "eelist".

AFter changes above I get this error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "EELIST" reported an error. The provider reported an unexpected catastrophic failure.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "EELIST". (Microsoft SQL Server, Error: 7399)

 I have everyone full access to the excel file linked to.

Answer 8

 

Thanks, THis helped me too but gets me to a new error:

 

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Answer 9

I was getting "The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" on 64-bit machine while importing data from MS Access to SQL Server 2005

Execution of the following commands solved the problem:

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

Make sure:

1.MS Access 2007 Installed

2. Microsoft Access database engine 2010 installed.

 

thanks

Answer 10

Quick question; trying to clear an issue with Msg 7308:

This whole registry key adding thread, here:

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb?prof=required

As well as here:

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/8514b4bb-945a-423b-98fe-a4ec4d7366ea

Along with the image at:

http://picasaweb.google.com/pandaatms/MSDN?feat=directlink#5317293760877787762

does not solve the 7308 error report.

But I concede I might be missing something:

I see this image (images as helpers in forums are good, generally) and I compare it my Linked Servers tree and am baffled by the fact that I don't have either "Microsoft.Ace.OLEDB.12.0" or "Microsoft.Jet.OLEDB.4.0" as Provider, much less an option to pull up a panel and warm my tootsies by the "Allow inprocess" tickbox fire.

End there. If there's no Provider showing up in the interface of MSSMSE, then I probably don't have the right version, right? Often there are TYPE IIs in ssmse ("executed successfully") when in fact nothing happens. Is this the case with sp_mmset_oledb_prop?

Can't find anything anywhere on this.

~~~~~~~~~~~

WindowsServer2008R2 (OS-6.1.7600)
SQLServer2008Express Advanced Services (10.0.2531.0)
MDAC(6.1.7600.16385)

64-bit

~~~~~~~~~~~~

1. Could someone tell me where these system files exist so I can check the version?

2. Since the error is outright "cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode" is there a similar stored procedure (as in the above citations) to "RECONFIGURE" this setting?

Related:

http://social.answers.microsoft.com/Forums/en-US/w7programs/thread/40de1d9d-98f2-4526-8e6a-a4e611b92760

So, the question might be appended again:

If I as developer can easily overcome an issue with changing the Advance Settings in the compiler to output the x86 executable so that it successfully accesses the Microsoft.Jet.OLEDB.4.0 driver, how come my Office 2007 installation hasn't been updated in support service pack form to access the same driver?

Isn't this the same access issue? As stated above, the image provided woud appear to me to be one found on a 32-bit box. Not 64-bit. So in conclusion, this information is not relevant to Windows Server 2008. At least!

~~~~~~~~~~~~~~~~~~~~~~

100926:

Four days have passed and there's been no activity on this thread.

Searching for more information regarding the advise given above and the discovery that the image provided is for a 32-bit operating system NOT a 64-bit OS, leads to the conclusion that the fix provided should be reported as abuse.

 

Answer 11

UNMARKED AS ANSWER:

See below ...

Answer 12

Have a look this article...... Its gave detail abt Microsoft.ACE.OLEDB.12.0' reported an error. Access denied.

http://www.ashishblog.com/blog/importexport-excel-xlsx-or-xls-file-into-sql-server/

Answer 13

Another alternative is to use SSIS Import/Export Wizard:

http://www.sqlusa.com/bestpractices/ssis-wizard/

Launch: SSMS --> Object Explorer --> Right Click Database --> Tasks --> Import Data

Wizard video: http://www.youtube.com/watch?v=9Wmdhnx1niU

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 14

Out of curiosity... how are you connecting to SQL box? locally or using another machine?

I find that when using locally to execute the following:

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=\\ServerMachine\Access.mdb')...[Table]

Will work fine.

 

However using another machine's SSMS connected to that SQL box will error with:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine cannot open or write to the file '\\ServerMachine\Access.mdb'. It is already opened exclusively by another user, or you need permission to view and write its data.".

So again... how are you executing the statement? via local SQL box or remote?

Answer 15

This might help someone else out - after trying every solution to trying and fix this error on SQL 64..

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

..I found an article here...

http://sqlserverpedia.com/blog/sql-server-bloggers/too-many-bits/

..which suggested I give Everyone full permission on this folder..

C:\Users\<SQL Service account name>\AppData\Local\Temp

And hey presto!  My query suddenly burst into life.  I punched the air in delight.

Edwaldo

Answer 16

Indeed.. It worked for me as well. Thanks Edwaldo for sharing it.

Answer 17

after registering the provider, add xml and the script will like this:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 xml;Database=D:\File1.xlsx', 'SELECT * FROM [Sheet1$]');
remeber to close the excel before you execute it. good luck.

Answer 18

Hi All,

I have to Open Excel by Using T-SQL:

I used Below queries:

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

This query is keep running.

select * FROM OPENROWSET(
  'Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;Database=C:\Test.xls;HDR=YES',
  'SELECT * FROM [Sheet $]')

OR

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=C:\Test.xls', 'Select * from FROM [Sheet$]')

This query is giving below error:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I tried this also but still displaying same error;

USE [master] 

GO 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 1 

GO 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 1 

GO

Anyone can tell me what could be problem & What could be solution

http://sqlyoga.com/2009/12/sql-server-how-to-read-excel-file-by.html

http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx

---

Thanks Shiven:) If Answer is Helpful, Please Vote

Answer 19

Create a .reg file with text below and replace MSSQL10 (10 = SQL Server 2008) with your MSSQL version and SQLADV with SQL Server instance name. Save the file and run. It'll add this entry to registry and you can use OPENROWSET with ACE OLEDB providers.

gavrav,

i have couple of questions  on above statement
1. Create .reg file (create where ?? and then what)
2. replace MSSQL10  (replace this WHERE, i mean where will i find MSSQL10(10=Sql server 2008) statement  for replacement)
3. save the file (whcih file)

Thanks in Advance

Parixit