EXEC sp_addlinkedserver

[OhioSteve]

I am trying to add a linked server. It is an Oracle server. I went to QA and executed this:

EXEC sp_addlinkedserver
@server = '[server name]',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = '[db name]'


I got the error "the server '[server name]' already exists".

Well, I know that we have an old connection to that server. I want to overwrite the existing connection.

Here are my questions.

1) How do I force it to overwrite the old entry?
2) How do I add the Oracle user ID and pw? It seems like they should be parameters.

 

[Catadmin]

You don't overwrite the old entry. You drop the SQL Server Registration and then re-register the Oracle server as if it were a new one.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Jamfool]

Have look at the following on your BOL (Books Online)


sp_dropserver
Removes a server from the list of known remote and linked servers on the local Microsoft® SQL Server™.

Syntax
sp_dropserver [ @server = ] 'server'
[ , [ @droplogins = ] { 'droplogins' | NULL} ]



sp_addlinkedsrvlogin
Creates or updates a mapping between logins on the local instance of Microsoft® SQL Server™ and remote logins on the linked server.

Syntax
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'useself' ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]