Failed attempts to create linked server

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
Access-Denied.bmp
PeterFrbWeb development, Java scripting, Python TrainingAsked:
Who is Participating?
 
lcohanDatabase AnalystCommented:
This should give you all details all in one

How to import data from Excel to SQL Server
http://support.microsoft.com/kb/321686

How to use Excel with SQL Server linked servers and distributed queries
http://support.microsoft.com/kb/306397

openrowset excel 2007
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/23e7b80d-c7ec-4875-bd3a-aa137fd95dfb



--Use a Linked Server
--To simplify queries, you can configure an Excel workbook as a linked server in SQL Server. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
--Q306397 HOWTO: Use Excel with SQL Server Linked Servers and Distributed Queries
--The following code imports the data from the Customers worksheet on the Excel linked server "EXCELLINK" into a new SQL Server table named XLImport1:
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
--You can also execute the query against the source in a passthrough manner by using OPENQUERY as follows:
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
    'SELECT * FROM [Customers$]')


--Use Distributed Queries
--If you do not want to configure a persistent connection to the Excel workbook as a linked server, you can import data for a specific purpose by using the OPENDATASOURCE or the OPENROWSET function.
--The following code samples also import the data from the Excel Customers worksheet into new SQL Server tables:
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
0
 
Anthony PerkinsCommented:
If your SQL Server is 64-bit, you should know that there is no JET 64-bit driver.
0
 
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
The issue actually had to do with a service I needed to open, but I appreciate this feedback.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.