Hi there! I have a question about utilizing a linked server. I am in a pretty controlled environment and there is a linked server that exists already on our production server. The server connects to an Oracle database. I would like to automate some data pulls from the oracle database into our SQL Server but it requires me to have two different linked servers. There are 2 Oracle logins that I need to use but, from my knowledge, I can only set up 1 username and password in the Linked Server security property.
There are different tables in the oracle database for each login that I need to use... the only way I can do it now is to edit the linked server each time I need it and put the different credentials in it before I run the queries.
Is there a way to either pass credentials to the linked server on executing openquery statements or to set up the linked server to have 2 different username and passwords and then I can tell it which one I want to use?
Thanks in advance. BTW - the actual database is running in 2000 but I'm using 2005 to connect to it.