Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Adding Linked Server to Oracle

Hi, I'm aware of the stored procedure to run to add a linked server... however, what would the syntax be to add a link to an oracle server?  I cannot figure out how to word the driver for oracle to get the link to work.

Thanks.
0
Roxanne25
Asked:
Roxanne25
  • 5
  • 4
  • 4
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi Roxanne25,
from BOL

C. Use the Microsoft OLE DB Provider for Oracle
This example creates a linked server named LONDON Mktg that uses the Microsoft OLE DB Provider for Oracle and assumes that the SQL*Net alias for the Oracle database is MyServer.

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
   @server = 'LONDON Mktg',
   @srvproduct = 'Oracle',
   @provider = 'MSDAORA',
   @datasrc = 'MyServer'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
   'LONDON Mktg',
   'Oracle',
   'MSDAORA',
   'MyServer'
GO



Cheers!
0
 
imran_fastCommented:
Practically  before the above you have to install
oracle client on your sql server.


Regards

Imran
0
 
imran_fastCommented:

In other words You cannot create link server to oracle if you don't have oracle client install on your sql server machine.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Roxanne25Author Commented:
What about the username and the password?
0
 
imran_fastCommented:
that will be oracles user name and password.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
    [ , [ @useself = ] 'useself' ]
    [ , [ @locallogin = ] 'locallogin' ]
    [ , [ @rmtuser = ] 'rmtuser' ]
    [ , [ @rmtpassword = ] 'rmtpassword' ]

refer BOL for examples
0
 
imran_fastCommented:
once you install oracle client you will see new provider in the list of adding link server you have to select the appropriate provider and then use oracle user id and password.
0
 
Roxanne25Author Commented:
Aneeshattingal,

So, if there are two different stored procedures for connecting and then one for login... do I have to run the login procedure each time before I run a query or will it store the information?  I'm used to doing this through the wizard but the interface I have now I can only use code to it, so forgive the questions.  I know when I use the wizard to link the connection/login stuff is all done automatically once its set up.  
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Roxanne25,
>  do I have to run the login procedure each time before I run a query
> or will it store the information?  

Once your sql login is mapped with the oracle login, it will stay for ever untill u explicitly removes it
0
 
Roxanne25Author Commented:
Cool, thank you very much. :)
0
 
Roxanne25Author Commented:
I linked the server successfully but I got an error when I tried to do a login:

exec sp_addlinkedsrvlogin
    [ @rmtsrvname = ] 'PRDEPM'
    [ , [ @useself = ] 'FALSE' ]
    [ , [ @rmtuser = ] 'username' ]
    [ , [ @rmtpassword = ] 'password' ]

It said incorrect syntax near PRDEPM.
0
 
Roxanne25Author Commented:
Nevermind I did it like this:
EXEC sp_addlinkedsrvlogin 'Accounts', 'false', NULL, 'SQLUser', 'Password'

And it worked ;)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
great  :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now