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
2,080 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 51

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 51

Accepted Solution

by:
Mark Wills earned 500 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Set the max value for a column 7 43
sql query help 2 68
Need help debbuging stored procedure 21 50
Clone table from one server.database to another server.database 24 61
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

751 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