Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Ok well I found the solution or at least its one or both of the two

  1. Instead of running express with the Network Service account, I created a regular user. Then I gave that user rights to the folder with the excel spreadsheets.

  2. I had already installed AccessDatabaseEngine.exe but I hadn't restarted the computer. This time I restarted it.

This is the OPENROWSET SELECT statement I used

SELECT FT_ID_SOCIETE_FF2C,FT_NOM,FT_ADR1,FT_ADR2,FT_CP,FT_VILLE,FT_TEL,FT_FAXFROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\SQLFTP\LISTEFF3CFTP.xls;HDR=YES', 'select * from [Requêtechauffage fioul$]');

So maybe it was a permissions problem? Weird since I could import csv files. Maybe it was just having to restart the computer after having installed AccessDatabaseEngine.exe.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.