Error reading a linked Excel spreadsheet

This problem just started happening (though the process had been working
without a problem for several months):
SQL to create link:
--if the link exists drop it
IF EXISTS (SELECT srvname FROM master.dbo.sysservers srv WHERE srv.srvid !=
0 AND srv.srvname = N'Excel')
EXEC master.dbo.sp_dropserver @.server=N'Excel', @.droplogins='droplogins'
--create the link
EXEC sp_addlinkedserver 'Excel', 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
@.FileName, NULL, 'Excel 5.0'
--add the login
EXEC sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL
--SQL to read linked data (which throws below error):
EXEC sp_tables_ex Excel
--(this also throws same error)
select *
INTO ExcelData
from Excel...[' + @.SheetName + ']
The link to the excel spreadsheet can be made but when you try to read the
data you get:
[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: ].
I have tried the following which are suggestions from searching the internet:
1. Making sure the registry entries are there.
2. Renaming Msexcl40.dll and the opening Access and running Detect and
Repair, which placed a new Msexcl40.dll in the system32 directory
3. Restarting the SQL Server Service and restarting the server
4. I tried different excel files
I have linked to the same files used above from my local SQL server and had
no problem reading the data.
I don’t know what else to do. Can someone help me with this problem?
Harolds
Hello Harolds,
From the error message, it seems jet driver has issues. You may want to
reinstall Jet SP8 to test:
829558.KB.EN-US Information About Jet 4.0 Service Pack 8
http://support.microsoft.com/default...B;EN-US;829558
239114: How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0
http://support.microsoft.com/default...b;en-us;239114
If the issue persists, please try to reinstall MDAC to see if it helps:
1. Find the file C:\windows\inf\mdac.inf (%SYSTEMROOT%\inf\mdac.inf)
(the INF folder is hidden so you will have to make in viewable:
click on Tools, Folder options..., View, click Show hidden files and
folders).
2. Right click on the file and choose install.
3. When prompted to get file, on the Locate File dialog box that results,
click Browse. You may want to direct to Win2003 SP1 setup CD or folders
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.