SQL Server 2000 & OpenRowset

[MitelRob]

I'm having an issue with using the OpenRowset function in T-SQL to read Excel workbooks.

I use the query in the following format:
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Temp\file.xls;HDR=No;IMEX=1',
'SELECT * FROM [Sheet1$]')

Initially the query works fine, but if I return to it some days later (haven't managed to pinpoint exactly how long)
the query returns:
[OLE/DB provider returned message: Could not find installable ISAM.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

From research it seems that this error occurs when the provider cannot be found when it's been uninstalled, when
the source workbook can't be accessed/located or when the worksheet is named incorrectly.

The problem I have is that MS Excel 2003 is installed on the server, the same file is located in the same place as
it was when it worked previously and permissions have not been changed, nor has the query (I've simply loaded it from a saved file!).

I have found that if I reboot the server it all springs back into life and is fine for a few days, but then it reverts to the error
shown above.

I've noticed that we have errors logged in the SQL Server Logs:
Query Memory Manager: Grants=9 Waiting=0 Maximum=101546 Available=58858
Procedure Cache: TotalProcs=10681 TotalPages=96211 InUsePages=57910
Dynamic Memory Manager: Stolen=104162 OS Reserved=9096
Global Memory Objects: Resource=3715 Locks=130
Buffer Counts: Commited=847616 Target=847616 Hashed=693763
Buffer Distribution: Stolen=49024 Free=8628 Procedures=96211
WARNING: Failed to reserve contiguous memory of Size= 65536.

Which might suggest that there isn't enough space available for the OLE/DB provider to load into memory?

The bit that makes this most awkward is that the server in question is a LIVE server that I have a 30 minute down-time
window every 24 hours.

Has anyone got any suggestions on how I can troubleshoot this?

 

[JayKusch]

May want to peruse this link for any helpful hints and/or info.

http://support.microsoft.com/?kbid=321686

Thanks

J. Kusch

 

[MitelRob]

Thanks Jay, I have looked at that KB before when I was verifying the syntax of the original query.
The query itself works fine, I've tested it on another SQL server we have (DR replica) with the same data & XLS file and it runs no problem, it also runs fine on the LIVE server we have.... up until the point that it doesn't (?!) if you see what I mean?
At the point I get the "Could not find installable ISAM" error I reboot the server in my 30 minute evening timeslot and all is fine again, query runs no problem, then a few days later it's giving me the error again.