In this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926 I gave a suggestion how to get a more detailed error message when working with OPENROWSET.

But the same thinking is applicable for LINKED SERVER and other "outer referenced" data retreival methods.

The OPENROWSET syntax with Jet provider does not give you full error description when problem occurs, such as permission errors.

-- Using this code for a file with no appropriate permissions throws a general error
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'Excel 5.0;HDR=No;IMEX=0;Database=\\datastorage\excel\book2.xls', 'select * from [Sheet1$a1:q50]')

If you have some error and you don't understand why, try using MSDASQL provider temporarily.

SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\datastorage\excel\book2.xls', 
    'SELECT * FROM [Sheet1$]')

Now most error desriptions are output. When you have resolved the error, switch back to Jet again.

For Excel 2007 and later, the OPENROWSET contructor would look something like