I am trying to run the following query to select data from an excel spreadsheet:
SELECT FT_ID_SOCIETE_FF2C,FT_NOM,FT_ADR1,FT_ADR2,FT_ADR3,FT_CP,FT_VILLE,FT_TEL,FT_FAX,F10 FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\SQLFTP\LISTEFF3CFTP.xls;HDR=YES','select * from [sheet1$]');
SSMS is sending back the following error:
Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
ProcessMonitor is telling me this :
Date & Time: 21/11/2011 18:41:55 Event Class: File System Operation: CreateFile Result: NOT A DIRECTORY Path: D:\SQLFTP\LISTEFF3CFTP.xls TID: 3496 Duration: 0.0000070 Desired Access: Execute/Traverse,Synchronize Disposition: Open Options: Directory, Synchronous IO Non-Alert Attributes: n/a ShareMode: Read, Write AllocationSize: n/a Impersonating: VM-CHALEUR-FIOU\Administrator
I have also installed the Office 2007 components and tried to use Microsoft.ACE.OLEDB.12.0 and got the same results from both SSMS and process monitor.
I am however able to import a CSV file from the same location so it is not a permissions issue.
I have tried running the same import from other SQL Server 2005 express servers and got the same result. The Ad Hoc distributed queries option is set to 1 in sp_configure.
Anyone know what I'm doing wrong?