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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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