I've setup a linked server to an Excel 2007 spreadsheet using the following:
sp_addlinkedserver 'Products', 'ACE 12.0', 'Microsoft.ACE.OLEDB.12.0', 'C:\Visit\Thales\ProductList.xlsx', 'Excel 12.0; HDR=Yes'
Both commands execute successfully and I can see the linked server in SQL server when I run:
select * from master.sys.servers where is_linked = 1
When I try and query the linked server using:
select * from openquery(Products, 'select * from [Products$]')
I get the following message:
OLE DB provider "Microsoft.ACE.OLEDB.12.0"
for linked server "Products" returned message "Unrecognized database format 'C:\ProductList.xlsx'.".
I have installed the Microsoft package for connecting to Office 2007, so I don't think that is the cause. Any other reason for this error, or is there something wrong with the way the server is being linked?