?
Solved

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information

Posted on 2011-03-14
4
Medium Priority
?
2,304 Views
Last Modified: 2012-05-11
I have been running this query and it worked successfully:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Matrix Data Feed\AccMaterialHierarchySource.xls', 'SELECT * FROM [MaterialGroup$]')

However, for the last few days, it is erroring out and gives me the following error message:
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)".

Any clues?
0
Comment
Question by:skaleem1
  • 2
  • 2
4 Comments
 
LVL 53

Expert Comment

by:Mark Wills
ID: 35135411
Thatis normally the error you get when it cannot open the spreadsheet. Or maybe you already have it open in excel. It does want exclusive access.

So, first check the worksheet name = MaterialGroup
Then check the physical location and access / security (it is the SQL Server service account that needs access)

Then copy the spreadsheet to c:\test.xls  then try :

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test.xls', 'SELECT * FROM [MaterialGroup$]')

then maybe check the actual version. could it be a more recent creation ?

if so, then you can use the upgraded office drivers :

Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\test.xls', 'SELECT * FROM [MaterialGroup$]')

So, lets run a few tests...

0
 
LVL 1

Author Comment

by:skaleem1
ID: 35140639
mark_wills:

I had already tried all these options but nothing worked. Finally I had it resolved by rebooting the SQL Server. Somehow, there is some setting that makes this stop working and only restarting the server resolved this issue however this is not always be a plausible solution in the production environment. Is there a better solution in which case I do not have to restart the server?
0
 
LVL 53

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 35142570
Agree, not a good method to fix. But it does fix it, so there must be a wayward process somewhere.

I would still take a methodical approach. If it does happen again, maybe just stop and start the SQL Server Service rather than a full reboot.

If that also fixes the issue we know it has to do with SQL Server services and we can consider the next port of call.

Could also go into SSMS server objects, linked servers, providers and change the properties on the provider and see if SQL Server complains / throws an error (because it is no longer talking properly), then again, doing that might even fix, in which case we narrow down even further.

Then if there are still errors, then we can start looking outside of SQL Server.

The *interesting* thing here is you tried one provider and it didnt work, then you tried another provider and it didnt work. Strange that SQL Server had issues with the two different drivers. You could even create an ODBC driver and try that as an alternative. If all three have problems then I would start to look outside of SQL Server and look into the physical, security and permissions layers.

There are other methods to reset reload the Jet engine, but that too is a bit awkward and want to find the actual problem. So first up always check the logs - both Windows logs and SQL Server logs - hopefully there is a more detailed error than that nefarious / generic thing that SQL provides.

If you can narrow down what does and doesnt work (isolate the root cause) then it can be dealt with. Anyway, good to hear you have a fix albeit not one that you want to live with, and hope the above gives some additional ideas and approaches if it happens again.
0
 
LVL 1

Author Closing Comment

by:skaleem1
ID: 35215307
Thanks a lot. I will try your suggestions next tinme it fails.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question