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,054 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 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Adventure works database .msi 4 60
How to extract a "coded date" from a string field? 4 52
SQl server restarts itself 6 32
Need help with a query 6 67
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 …
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

914 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now