Importing Excel Using Linked Server And Passthrough Query

What: I am trying to import data from spreadsheets to SQL Server.

Where: Windows Vista, SQL Server 2005 Express, Office 2007.

How: Using linked servers following KB306397.


In SQL Management Studio Express, I created a new Linked Server as follows, with everything else at default:


Linked server: XLSX

Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider

Product name: XLSX

Data source: D:XLSX.xlsx

Provider string: Excel 12.0
The linked server was created ok.

I then followed KB321686 and ran this:



select * from OPENQUERY(XLSX, 'Select * From [Sheet1$]')

and got this:


Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" 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 "XLSX".

But if I ran: select * from OPENQUERY(XLSX, 'Select * From [nonexistent$]')

the error is:



Msg 7357, Level 16, State 2, Line 1

Cannot process the object "Select * From [nonexistent$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" indicates that either the object has no columns or the current user does not have permissions on that object.



It seems that there is an access rights problem if I get the sheet name correct. May I know what I must do to get this to work. I have already given read/write rights to the spreadsheet to NETWORK SERVICE and SQLServer2005MSSQLUser$servername$SQLEXPRESS.

From the same KB article, I also tried:



select * from XLSX...[Sheet1$]

but got this:



Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX".

Help help.

Thanks.