DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to link to an Oracle database server?

On the create new linked server panel in SSMS Object Explorer for Provider you have to select:

Microsoft OLE DB provider for Oracle

You also have to provide the appropriate connection information in a separate command.

-- ORACLE Linked Server setup

EXEC sp_addlinkedserver

      @server = 'BERLINProd',

      @srvproduct = 'Oracle',

      @provider = 'MSDAORA',

      @datasrc = 'Inventory'

------------

 

-- Setup remote SQL Server as Linked Server with login info

USE [master]

GO

EXEC master.dbo.sp_addlinkedserver @server = N'SRVRNAME\SQL2000', @srvproduct=N'SQL Server'

GO

EXEC master.dbo.sp_serveroption @server=N'SRVRNAME\SQL2000', @optname=N'rpc', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'SRVRNAME\SQL2000', @optname=N'rpc out', @optvalue=N'true'

GO

-- Login/password information

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SRVRNAME\SQL2000',

         @locallogin = NULL , @useself = N'False', @rmtuser = N'sqlReport', @rmtpassword = N'007London'

GO

------------

 

-- Test linked server with OPENQUERY

SELECT * FROM OPENQUERY([SRVRNAME\SQL2000], 'SELECT * FROM Northwind.dbo.Products')

-- (77 row(s) affected)

------------

Related articles:

Linked Severs on MS SQL Part 4, Oracle

How do you setup a linked server to an Oracle database on SQL 2000/2005?

http://www.sqlusa.com/bestpractices2005/linkedservertooracle/

How to return information about SQL Server linked servers

sp_tables_ex (Transact-SQL)

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE