Import excel data with OPENROWSET on production server - by Riaz Bashir

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 284113 Comments
Status Closed Workarounds
Type Bug Repros 17
Opened 6/25/2007 5:39:46 AM
Access Restriction Public


I want to import data from a spreadsheet using OPENROWSET on a production server. This is my query:

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

I get the following error message (mine is in italian, here is the english version):

OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified 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)".

After several experiments, I found out that the only way out would be a sql server restart. 
Without changing ANYTHING, after restart the query succeeds.
On a production server it's not an acceptable solution.
On development server (which isn't up 24X7) it always works fine (in RTM too).
Obviously, the excel file is not open while trying to import data (as supposed by the first comment).

Is there an explanation? And a possible solution?

Sign in to post a comment.
Posted by Anand.jhawar on 2/9/2012 at 10:46 PM
Even I had faced the same issue, but when I restarted the SQL server then it worked.

Enjoy :)
Posted by Daniel J. Costa on 11/16/2011 at 3:23 PM
Hello, I Find the Solution and post it on my Blog. Read:

Have Fun,

Daniel Costa
Posted by AnbuSoft on 1/7/2011 at 12:18 AM

Just Close that Excel Sheet "dataSource.xls" and then execute the query

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\dataSource.xls;HDR=YES',
'SELECT * FROM [Foglio1$]')"

Posted by NilGems21101982 on 10/27/2010 at 1:53 AM
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=E:\Test_Projects\NewAdminWebSite\Masters\Format\BSC_KRA_Departmental_Directory.xlsx''')...[Sheet1$]

When I fired above query on sql server management studio,the following error shows.

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)".

Could you suggest me,the solution? Thanks.
Posted by StephenGB on 12/23/2009 at 11:29 AM
I received the same error message and resolved it by removing all spaces in the openRowSet statement as well as ensuring there were no spaces in the Excel sheet name and using the dollar sign on the sheet name.

OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\FC\Projects\....\F09HSGQE.XLS',[F09HSGQE$])
Posted by Joachim [MSFT] on 9/3/2009 at 10:28 AM
Dear Customer,

Thanks again for your feedback on OPENROWSET. Since you indicated to us that you cannot repro the problem on your servers at the moment and since we are lacking specifici repor details, we will close this issue for now.
However, if the problem re-surfaces, please contact us through this channel with any repro information you may have.


Joachim Hammer
Program Manager
SQL Server
Posted by D-Squared on 2/3/2009 at 12:10 PM
I have experienced similar issues, even on a DEV machine. I restarted multiple times as well. One thing I did try was to stop everything, shutdown all open XLS files, then start, then try the import. It worked for me after that. I don't know if it randomly worked after the restart or if changing the number of open XLS files did it. Good luck!
Posted by mje113 on 9/9/2008 at 2:16 AM
I am also having this problem just after a system restart. If I wait several minutes then restart SQLServer, sometimes more than once, the problem goes away. Is there a fix for this bug yet?
Posted by Riaz Bashir on 5/12/2008 at 3:48 AM
I enabled Ole Automation Procedures and it didn't work immediately, but it didn't work after restart either. I'm afraid this workaround is not a general solution (btw, when working, openrowset behaves fine even without ole automation enabled)
Posted by RayNovak on 4/14/2008 at 4:57 AM
enabling ole automation fixed the problem for me - it has been working for about a month with no failures and no restarts needed
Posted by RayNovak on 2/18/2008 at 7:58 AM
Received a possible solution from a forum post. The suggestion was to enable ole automation (with surface area config or sp_configure). The poster said this solved the problem for him. It did not immediately work for me - I wlll restart the server when I can and see if the problem is resolved.
Posted by RayNovak on 2/13/2008 at 3:29 PM
Has there been any progress on this? I am getting weekly complaints from my customer who is affected by this problem, and I have a few more customers pending who will also be affected. So, can I depend on OpenRowSet for importing data from a csv file, or do I need to start over?
Posted by Joachim [MSFT] on 1/18/2008 at 11:43 AM
Dear Customer,

Thanks for your feedback regarding the problem using OPENROWSET with 'Microsoft.Jet.OLEDB.4.0' when importing data from a spreadsheet. We are currently investigating the problem to find out where the error is coming from.


Joachim Hammer

Program Manager
SQL Server
Posted by RayNovak on 12/11/2007 at 1:07 PM
I have the same problem accessing csv files, whether I use "Microsoft.Jet.OLEDB.4.0" or "MSDASQL" as the provider. After restarting, it works. Kind of hard to tell the customer "wait until I can restart the server tonight and then try again."

Any action on this item? I have seen it reported in several different forums with no real solutions found.
Posted by AaronBertrand on 6/25/2007 at 1:18 PM
My vote is that the error message should be better. Surely something along the pipeline understands that the file couldn't be queried because it's already open?