Solved

Failed attempts to create linked server

Posted on 2012-03-09
3
259 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Counting connections to SQL Server through C# 3 29
Need a starter for ETL protocol? 4 42
T-SQL: Nested CASE Statements 4 24
SQL Query Syntax Join 4 34
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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

910 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

20 Experts available now in Live!

Get 1:1 Help Now