Oracle and SQL Server

[Myqu]

Hi ,

I am trying to connect to Oracle from Sql server as I need to use a few tables
from it on an ongoing
basis. How do I go about connecting to Oracle from Sql server.

Thanks much
Myqu

[Neil Pike]

Myqu - assuming it's SQL 7.0 then lookup "linked servers" in the SQL 7.0
books-online

No Oracle example below, but you get the idea...
#
> I am trying to connect to Oracle from Sql server as I need to use a few tables
> from it on an ongoing
> basis. How do I go about connecting to Oracle from Sql server.

Q. Are there any examples of heterogeneous data queries from SQL 7 to other
sources?

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'

===

v1.04 1999.04.27
Applies to SQL Server versions : 7.0, 2000
FAQ Categories : Application Design and Programming
Related FAQ articles : n/a
Related Microsoft Kb articles : n/a
Other related information : n/a
Authors : Neil Pike
Neil Pike MVP/MCSE. Protech Computing Ltd
(Please reply only to newsgroups)
SQL FAQ (428 entries) see
forumsb.compuserve.com/vlforums/UK/default.asp?SRV=MSDevApps (sqlfaq.zip - L7 -
SQL Public)
or www.ntfaq.com/sql.html
or www.sql-server.co.uk
or www.mssqlserver.com/faq