Well it worked like a charm on my development machine Windows XP, latest SP etc. I'm now trying to get it to work on windows VISTA machine latest SP etc.. I've installed the AccessDatabaseEngine but when trying the following query in SSMS.
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 XML;Database=c:\rmfintrv.xlsx;IMEX=1;HDR=Yes;', 'SELECT * FROM [rmfintrv$]')
I get the following error
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" 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 "(null)".
Interestingly it does manage to determine the columns, if I look in the results tab I can see the column names, which proves that it can read the file.
Has anyone had any simmilar issues?