Solved

Failed attempts to create linked server

Posted on 2012-03-09
3
257 Views
Last Modified: 2012-03-12
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
0
Comment
Question by:PeterFrb
3 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 37702926
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37707221
If your SQL Server is 64-bit, you should know that there is no JET 64-bit driver.
0
 

Author Closing Comment

by:PeterFrb
ID: 37711726
The issue actually had to do with a service I needed to open, but I appreciate this feedback.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now