I am trying to access SQL Server database which is on windows server, using People Code. For People Soft we are using Oracle 11g database on Unix box. Is there an option to connect to the SQL Server and get the data from the record ?
There is an option. You'll need to install a Unix ODBC driver and create a database link in Oracle. Using this database link you can select data from records in the SQL server database. I'm not sure which driver is best for that, but Microsoft provide one and unixODBC:
Thank you Michel for your reply, do you mean creating database link between Oracle Database server on UNIX box to SQL Server database on Windows Box?
How can we authenticate the user id from database level for Windows Authentication for SQL Server?
Yes that is exactly what I meant. You specify a user id and password when setting up the ODBC connection to the SQL server. You can create a new user id on the SQL server specifically for that goal. You can then give that specific user id access rights to several tables.
Thank you Michel, have you done or been involved with similar issues previously, because in some forums it is stated that we cannot create a database link from Oracle to SQL Server we need to have a third party software to make it work, however we can create a link from SQL Server to Oracle database to get the data.
If you have done it in the past did you use any third party software or its a direct link?
The third party software you are referring to are probably the drivers I have mentioned in a previous post. With the drivers you should be able to setup an ODBC connection to SQL server from an Oracle database. Personally I've never been involved in setting up such a thing, but I have seen it work this way.
Check out the following blog:
This should be comprehensive enough to help you with setting it up.
An alternative to ODBC configuration is JDBC. You can find several posts on using JDBC with PeopleSoft to access different databases on my blog here: http://jjmpsj.blogspot.com/search/label/JDBC. My examples are Oracle specific. This example is JDBC generic: http://psst0101.wordpress.com/2008/07/29/writing-to-access-databases/