How to Create SQL Server Linked Server? / Establish Connection between Oracle and SQL Server

Description:
I have used below steps to create Linked server. Using linked server we can access Oracle data from SQL Server.
Linked Server Name: TEST
Oracle Database: Oracle10g
Oracle Client Version: Oracle 9i
SQL Server Version: Microsoft SQL Server 2000
Step 1: Install Oracle 9i client (Runtime option is enough) on SQL Server machine. You can use 10g Client also.
Step 2: Check client server connectivity using tnsping utility from SQL Server machine,
cmd>tnsping Database_SID
Step 3: Log into SQL Server as “sa” and execute the below script
/****** Object: LinkedServer [TEST] Script Date: 11/23/2009 07:48:55 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'TEST', @srvproduct=N'TEST', @provider=N'MSDAORA', @datasrc=N'TEST', @provstr=N'Provider=MSDAORA.1;User ID=reporter;Data Source=TEST;Persist Security Info=False'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'dpub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'use remote collation', @optvalue=N'true'
Step 4: After Execution of the above script go to Properties of the “TEST” Linked Server.
Server Enterprise Manager -> Microsoft SQL Servers -> Security -> Linked Servers -> TEST
Step 5: Go to Security tab from “TEST” Linked Server. Enter the details for Local Login (use SQL Server Login Credential), Remote User and Remote Password (use Oracle Login Credentials)


Step 6: Enable Be made using this security context
Enter the Oracle Username and Password which you entered on Step 5.
Step 7: Enter “OK”
Note: Sometimes you may get Error 7399: OLE DB provider 'MSDAORA' reported an error. To solve this error reboot the SQL Server.