No tables in Linked Excel Server view

This allowed me to create a linked server, but when I try and view the
tables, it does not show any tables.
I assumed the tables would be the sheet names, no?
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'H:\IT\Shared\tblInItem.xls',
NULL,
'Excel 5.0'
GO
--
-Tobias Mazzei
Wraith SystemsNot really - see example F:
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
You need to name the cell range. I think I saw a VBA procedure some where
(online) that automates range naming - provided there's only a single table
per worksheet.
ML|||Okay, I found the skinny on this. I hope my pain helps others on this.
Apparently I goofed in setting up the linked servers to begin with.
The standard procedure for setting these up is to
1) Create the Excel file(s)
2) Name the columns using the range name function, these names will show up
in your "tables" list once you establish the link
3) Make sure you have symin AND setupadmin rights first
4) Use Enterprise Mgr to right click on linked servers and create new link
-or-
Use Sql Query Analyzer and build a statement like:
DECLARE @.RC int
DECLARE @.server nvarchar(128)
DECLARE @.srvproduct nvarchar(128)
DECLARE @.provider nvarchar(128)
DECLARE @.datasrc nvarchar(4000)
DECLARE @.location nvarchar(4000)
DECLARE @.provstr nvarchar(4000)
DECLARE @.catalog nvarchar(128)
-- Set parameter values
-- Remote Server Name
SET @.server = 'xcltblInItemUom'
-- Program you are importing from
SET @.srvproduct = 'Excel'
-- Type of Data Link Engine
SET @.provider = 'Microsoft.Jet.OLEDB.4.0'
-- Important, Use full domain namespace
SET @.datasrc = '\\svrsbs3hyc\Company\IT\Shared\tblInIte
mUom.xls'
SET @.provstr = 'Excel 8.0'
-- Execute command to link server to Master.DBO
EXEC @.RC = [master].[dbo].[sp_addlinkedserver]
@.server, @.srvproduct, @.provider,
@.datasrc, @.location, @.provstr, @.catalog
Note that if you are not programming directly ON the SQL server you MUST use
the UNC of the location of the excel files. (most of us dont use the server
to program on, so this is important)
5) Depending on your security policies, choose link server properties after
link, and under security, select Be made without using a security context
6) when referencing the query, the sheet is designated by a '$' after the na
me
7) Rinse, repeat for each file you need to link.
This operation works, although I am sure that there are more glamourous ways
of doing this.
For those beginners out there that didn't get this stuff in MCDBA courses,
good luck!!
-Tobias Mazzei
Wraith Systems
"ML" wrote:

> Not really - see example F:
> http://msdn.microsoft.com/library/d...>
dda_8gqa.asp
> You need to name the cell range. I think I saw a VBA procedure some where
> (online) that automates range naming - provided there's only a single tabl
e
> per worksheet.
>
> ML