Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

Question

Hi Team,

I am trying to Export data from SQL Sever Table to Exel sheet by using the below query

INSERT

INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=D:\New Folder\New Microsoft Office Excel Worksheet.xlsx;','SELECT * FROM [Test$]')SELECT * FROM [TableName]

the above code is working in my local but when I execute the same in server I am getting the below error message.

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)".

 

Server Configuration Details are Windows7

can you help me on how to resolve the above

Answer 1

This is because the excel file is located on your local machine & its looking on server at the path you've mentioned above.

Provide shared path by providing your machine name.

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 2

Another option is that the driver is not installed on your server or running on 64 bit. I assume the same path and file is available on your server.

J.

---

There are 10 type of people. Those who understand binary and those who do not.

Answer 3

HI nmitta !

Try to run and set these setting;

sp_configure 'show advanced options', 1;
RECONFIGURE;
Go
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

Please let me know if this doesn’t work for you. Hope I have answered you correctly.
 
Thanks, Hasham

Answer 4

Check this blog post - may be of help

Dealing with Microsoft.Ace and OPENROWSET Errors

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog