A. Here are a variety of examples for several different datasources. Note that you will have to change filenames, drives, regions etc. as necessary for your environment :-
1. Selecting from an Excel spreadsheet using OpenRowSet. Here, c:\ramsql7.xls is a spreadsheet (note we haven't specified the extension). sheet1 is a sheet within the spreadsheet - note the trailing $.
SELECT * FROM OpenRowSet
('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=c:\ramsql7', 'SELECT * FROM [sheet1$]')
as a
2. Selecting from an Access linked server database via Jet. The Access database is at c:\msd\invent97.mdb
print 'add Jet 4.0 Invent'
-- Clear up old entry
if exists(select * from sysservers where srvname = N'INV')
exec sp_dropserver N'INV', N'droplogins'
go
-- create linked server
exec sp_addlinkedserver @server = N'INV', @srvproduct = '', @provider =
N'Microsoft.Jet.OLEDB.4.0', @datasrc = N'c:\msd\invent97.mdb'
go
-- setup default admin login for Access
exec sp_addlinkedsrvlogin @rmtsrvname = N'INV', @useself = N'FALSE',
@locallogin = NULL, @rmtuser = N'admin', @rmtpassword = N''
go
-- Lists all tables in the linked server
exec sp_tables_ex N'INV'
go
-- Now select from a table in the Access db called INVENT
select * from INV...INVENT
go
3. DB/2 accessed via Star SQL Driver with SNA 4.0.
print 'add DB2 LinkedServer'
if exists(select * from sysservers where srvname = N'DB2')
exec sp_dropserver N'DB2', N'droplogins'
exec sp_addlinkedserver @server = 'DB2', @provider = 'MSDASQL', @srvproduct
= 'StarSQL 32',
@location = 'DBT1', @datasrc = 'DB2IBM'
exec sp_addlinkedsrvlogin @rmtsrvname = 'DB2', @locallogin = 'sa', @useself
= 'false',
@rmtuser = 'HDRUSER' ,@rmtpassword = 'SQL7'
go
-- test to see is catalog is accesible
sp_tables_ex N'DB2'
-- create view to see if select works
create view V007MUNI as select * from DB2..T1ADM007.V007MUNI
go
select * from V007MUNI
go
4. DBASE IV
print 'add DBase IV LinkedServer'
if exists(select * from sysservers where srvname = N'DBFs')
exec sp_dropserver N'DBFs', N'droplogins'
EXEC sp_addlinkedserver
'DBFs',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'F:\DBFs',
NULL,
'dBase IV'
GO
exec sp_addlinkedsrvlogin
@rmtsrvname = 'DBFs',
@useself = false,
@locallogin = NULL,
@rmtuser = NULL,
@rmtpassword = NULL
go
SELECT * FROM DBFs...test
go
5. Visual FoxPro. Using a FoxPro DBC file to group the DBF files. ODBC DSN pre-defined called FOX using the Microsoft Visual FoxPro Driver 6.01.8440.01
-- FOX using Visual FoxPro Database file .DBC
print 'add FOXSERVER'
if exists(select * from sysservers where srvname = N'FOXSERVER')
exec sp_dropserver N'FOXSERVER', N'droplogins'
exec sp_addlinkedserver @server=N'FOXSERVER',
@srvproduct ='',
@provider = N'MSDASQL',
@datasrc=N'FOX'
exec sp_addlinkedsrvlogin @rmtsrvname=N'FOXSERVER',
@useself = N'FALSE',
@locallogin = NULL,
@rmtuser = N'',
@rmtpassword =N''
exec sp_tables_ex N'FOXSERVER'
select * from [FOXSERVER].[D:\SQL\FOX\TESTDATA.DBC]..[customer]
6. FoxPro using plain DBF files in a directory. Using an ODBC system DSN (Called DBF) using the Microsoft Visual FoxPro Driver 6.01.8440.01
-- DBF using plain .DBF files
print 'add DBFSERVER'
if exists(select * from sysservers where srvname = N'DBFSERVER')
exec sp_dropserver N'DBFSERVER', N'droplogins'
exec sp_addlinkedserver @server=N'DBFSERVER',
@srvproduct ='',
@provider = N'MSDASQL',
@datasrc=N'DBF'
exec sp_addlinkedsrvlogin @rmtsrvname=N'DBFSERVER',
@useself = N'FALSE',
@locallogin = NULL,
@rmtuser = N'',
@rmtpassword =N''
exec sp_tables_ex N'DBFSERVER'
select * from [DBFSERVER].[D:\SQL\DBF]..[country]
7. FoxPro using installable Jet 3.51 ISAM drivers.
print 'add FOXDBC using Jet 3.51'
if exists(select * from sysservers where srvname = N'FOXDBC')
exec sp_dropserver N'FOXDBC', N'droplogins'
exec sp_addlinkedserver 'FOXDBC', 'Jet 3.51', 'Microsoft.Jet.OLEDB.3.51',
'c:\sql\fox', NULL, 'FoxPro 3.0'
exec sp_addlinkedsrvlogin @rmtsrvname = N'FOXDBC', @useself = N'FALSE',
@locallogin = NULL, @rmtuser = NULL, @rmtpassword = NULL
exec sp_helplinkedsrvlogin N'FOXDBC'
exec sp_tables_ex N'FOXDBC'