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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

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 …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

624 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