How to add oracle as a linked server in SQL server

In this post, I will guide you through the steps to add a new Oracle linked server in SQL Server.

/****** Linked Server Creation for [PAYROLL] ******/
EXEC master.dbo.sp_addlinkedserver @server = N'PAYROLL'
   ,@srvproduct = N'MSDAORA'
   ,@provider = N'MSDAORA'
   ,@datasrc = N'pay4win20'  -- pay4win20 is the service name, payroll is the server name, and MSDAORA is the Oracle driver name

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'PAYROLL'
   ,@useself = N'False'
   ,@locallogin = NULL
   ,@rmtuser = N'system'
   ,@rmtpassword = '########'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'collation compatible'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'data access'
   ,@optvalue = N'true'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'dist'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'pub'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'rpc'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'rpc out'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'sub'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'connect timeout'
   ,@optvalue = N'0'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'collation name'
   ,@optvalue = NULL
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'lazy schema validation'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'query timeout'
   ,@optvalue = N'0'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'use remote collation'
   ,@optvalue = N'true'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'remote proc transaction promotion'
   ,@optvalue = N'true'
GO

Additional References:

DB2 as a Linked Server

Linked Server with a Different Name