I want to import data from a spreadsheet using OPENROWSET on a production server. This is my query:
'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?