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.