Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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,141 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 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

705 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