Access EXCEL using OPENROWSET

Question

Post reply

Access EXCEL using OPENROWSET

sql server developer

SSCarpal Tunnel

Points: 4746
More actions
May 22, 2014 at 9:18 am

#396800

I'm trying to access Excel file from SQL Server management studio using OPENROWSET using the below query but getting the error listed below. Any suggestions on this would be appreciated
Query:
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=E:\TestFiles\ExcelFile.xlsx',
'SELECT * FROM [Sheet1$]'
)
Error:
Msg 7415, Level 16, State 1, Line 2
Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

sql server developer SSCarpal Tunnel Points: 4746 More actions · Answer 1

After getting sysadmin rights on the server i'm getting the below error:

Msg 7399, Level 16, State 1, Line 2

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

MMartin1 One Orange Chip Points: 27795 More actions · Answer 2

Have a look at this:

http://msdn.microsoft.com/en-us/library/ms187569(v=sql.105).aspx

----------------------------------------------------

sql server developer SSCarpal Tunnel Points: 4746 More actions · Answer 3

Hi All,

This is working for me when I run the query in the remote desktop connection of that server but it is not working when I run it on my local machine when connected to that server in management studio. Any suggestion plz??

amzadali16 SSC Enthusiast Points: 125 More actions · Answer 4

Hi

Go to on windows menu go SQL Server management studio, right click, you get list of commands, and click on Run as administrator.

I guess it should work.

It just need you're Admin permission

sql server developer SSCarpal Tunnel Points: 4746 More actions · Answer 5

Thanks for your reply. I tried but lo luck. Any other suggestions!

Thanks in advance!

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

shot in the dark

SELECT * FROM

OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=E:\TestFiles\ExcelFile.xlsx';

Extended properties=Excel 12.0', [Sheet1$])

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

MMartin1 One Orange Chip Points: 27795 More actions · Answer 7

Have a look at this:
http://msdn.microsoft.com/en-us/library/ms187569(v=sql.105).aspx

Have you had a chance to configure distributed ad hoc queries to see if this helps?

----------------------------------------------------

sql server developer SSCarpal Tunnel Points: 4746 More actions · Answer 8

yes, i've already configured distributed ad hoc queries. Like I said before it is working when I run the query on the SQL server through remote desktop connection of the SQL server but it is not working when I run it on my local machine connected to that SQL server in management studio.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 9

have you tried this suggestion

http://visakhm.blogspot.co.uk/2013/12/how-to-solve-microsoftaceoledb120-error.html

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day