I have tried two methods for creating a linked server to an Excel 2003 source, and both have failed. I have a stored procedure that I used before, and I had to rebuild my links. The stored procedure that worked for me previously was as follows, with Server and File set as parameters:
EXEC sp_addlinkedserver @ServerName, 'Jet 5.1', 'Microsoft.Jet.OLEDB.4.0',
@FileName, NULL, 'Excel 5.0'
My second attempt was using an ODBC connection, that I've established works as a connection:
EXEC sp_addlinkedserver @Servername, @DSN, N'MSDASQL', @DSN
In both cases, the process creates the Linked Server, and when I write click on the server and select "Test Connection", it indicates success. They fail when I try to access the default tables. I would love to get someone's feedback on this.
Thanks, ~Peter Ferber