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'
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'
You must be a registered user or online subscriber to comment on this article. Please log on before posting a comment. Are you a new visitor?
Register now
An often irreverent look at some of the week's other news, including some more Windows 7 sales momentum, some Sophos stupidity, Microsoft's cloud computing self-loathing, more whining from the browser makers, Zoho's "Fake Office," and much, much more ...
Let Your Users Reset Their Own Passwords: Free Download Try a 30 day free trial of Desktop Authority Password Self-Service – it provides an easy-to-use, robust system for allowing users to reset their own forgotten passwords or locked accounts.
Get Windows IT Pro & Mark Minasi’s Favorite Power Tools Guide Order Windows IT Pro now and get "More of Mark Minasi's Favorite Power Tools"--a in-depth guide to the most useful Windows commands --FREE with your paid order! Subscribe today, and save 58% off the cover price!
Deep Dive into VMware vSphere, eLearning Series Join John Savill to explore the major functionality capabilities of the vSphere virtualization platform, including identification of the changes from ESX 3.5.