[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Linked Server table from Excel

Posted on 2004-10-22
6
Medium Priority
?
607 Views
Last Modified: 2012-06-21
If I don't have a range in my Excel spreadsheet I can't select that table (range) once I'm linked to the sql.  How can I name the range during the process of linking to the file from sql utilizing sp_addlinkedserver?
0
Comment
Question by:wordtool
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 

Author Comment

by:wordtool
ID: 12385103
Please note that I am actually linking to a .csv file.
0
 

Author Comment

by:wordtool
ID: 12385150
I'm using the following:

EXEC master..sp_addlinkedserver 'Excel',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
      '\\myserver\shared\Data_In\1371.xls',
   NULL,
   'Excel 5.0'
GO

Is there a different syntax I should be using for .csv files?
0
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 80 total points
ID: 12385797
The last parameter (for a CSV file) is not Excel 5.0.  It should be 'Text', from what I know of it.
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12387596
If you've saved the excel as a csv file, then there is no such thing as named ranges etc. a csv is simply a text file.

If you have saved it as an xls file, as you indicate in your linked server connection details, then, if you want to use named ranges, you need to create these within the Excel spreadsheet. This is not something that can be done easily from within SQLServer (I say easily because, ultimatley anything can be done, with extended stored procs, and command shell, you can virtually anything)

However, you don't necessarily need to use named ranges - you can use sheet references, as in

SELECT * FROM EXCEL...SHEET1$
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

649 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