Home » SQL ServerRSS

How to link Excel 2010 .XLSX file to SQL server 2008R2 - a method that actually works?

I have been trying unsuccessfully to link my excel file to SQL server like I used to with SQL 2005.

In SQL 2008R2 I have installed the 64-bit data access components from Office 2010 to get the  provider 'Microsoft.ACE.OLEDB.12.0' avilable.

When I try this command:

SELECT

 

*FROMOPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source="E:\ADUpdates\Employee List1.xlsx"; Extended properties=Excel 12.0')...[Sheet1$]

I get this error:

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

The Access Denied error seems to be form the worksheet and not the file itself. Evidence is that if I enter a bogus sheet name it tells me the sheet doesn't exist and after this error the file is locked open and can't be saved again from Excel until the query windows is closed. The sheet is not protected in Excel so I don't know how access to it can be denied.

Does anyone actually have this working in SQL 2008R2?

 

1 Answer Found

 

Answer 1

Some minor progress on this issue by running SQL Server service under a domain account with Administrator priv on the SQL server.

This query now works in a query window:

SELECT * FROM OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0', 'Data Source="E:\ADUpdates\Employee List.xlsx"; Extended properties=Excel 12.0')...[EmployeeData];

However, creating a Linked server object for the same spreadsheet still does not work:

EXEC master.dbo.sp_addlinkedserver @server = N'TEST', @srvproduct=N'Excel 12.0', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'E:\ADUpdates\Employee List.xlsx'

 

 

The test connection to the linked server failed.

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TEST".
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TEST" returned message "Unrecognized database format 'E:\ADUpdates\Employee List.xlsx'.". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

Naturally, the help link provided only confirms the contempt microsoft holds for their customers by not providing any information at all.

(appologies for the weird colour and fonts, this is a microsoft product, just have to live with obvious defects).

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter