Recently I implemented a supply chain solution (Data Collection Advantage - If you need more information on this product, go this website ). In this project, since the client was a public traded company here in the US, they had strict auditing rules for the data movement/transactions and had to adhere to SOX (Sarabanes Oxley rules). Their ERP was PeopleSoft. I used the following script to link the Oracle 9.2 database to SQL Server 2005:
This code can also be downloaded from this google document site.
Tested on SQL Server 2005.
/****** Object: LinkedServer [PeopleSoft] Script Date: 10/02/2007 16:14:37 ******/
/****************************************************************************
HJ Consultant Srinivasan Prasanna
Client Company ABC
Proc Name usp_linked_server
Purpose This SP will be used to create linked server
Date Created 5/14/2007
Version 1.0
Date Changed
******************************************************************************/
EXEC master.dbo.sp_addlinkedserver @server = N'PeopleSoft', @srvproduct=N'Oracle 9.2', @provider=N'MSDAORA', @datasrc=N'Test8'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'use remote collation', @optvalue=N'true'
/****** Object: LinkedServer [PeopleSoft] Script Date: 10/02/2007 16:14:37 ******/
Please feel free to comment on this code.
Subscribe to:
Post Comments (Atom)

No comments:
Post a Comment