Can someone explain the magic of Opendatasource/Openrowset?

Question

When pulling information from an excel file (or ms-access DB) I commonly use something like this:

SELECT *
 FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
 'Data Source=C:\test.xls;Extended Properties=''EXCEL 12.0;HDR=NO;IMEX=1'' ')...[Sheet1$]

Sometimes it works. Sometimes it doesn't.

Does anyone know a guide to setting this up?

I know about the temp folder, I know about downloading the correct drivers, I know about the extended properties, I know about not having the file open. Sometimes though, I still get the -

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

I ran into this problem yesterday. I restarted my machine - didn't work. Then restarted my instance again and P00f! Magically it worked.

So that's my question - When getting the awesome "Unspecified error", what do you need to check to make sure all the stars are in line for this to work.

Answer 1

Check Windows Event Viewer. Look in the application logs, security logs, and system logs. If you've nailed down all of the obvious stuff (and it sounds like you have), then it could be anything. The file's folder could have Windows domain security on it, and your machine might not be able to authenticate against the DC, for example.