Changing linked server pswd on multiple servers

Chuck

unread,

Dec 22, 2009, 3:51:01 PM12/22/09

to

I have about 15 servers which have a link to OracleServerA.

Is there a way to script changing the linked server password.

I tried doing
EXEC Srv01.master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'OracleServerA', @locallogin = NULL , @useself = N'False',
@rmtuser = N'system6', @rmtpassword = @NewSystemPassword

EXEC Srv02.master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'OracleServerA', @locallogin = NULL , @useself = N'False',
@rmtuser = N'system6', @rmtpassword = @NewSystemPassword

EXEC Srv02\Binstance.master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'OracleServerA', @locallogin = NULL , @useself = N'False',
@rmtuser = N'system6', @rmtpassword = @NewSystemPassword

But I get error
Msg 7202, Level 11, State 2, Line 8
Could not find server 'Server01' in sys.servers. Verify that the correct
server name was specified. If necessary, execute the stored procedure
sp_addlinkedserver to add the server to sys.servers.

If I run the script on a different server sometimes I get
same message but with Server02 in the error message.

Thanks,

John Bell

unread,

Dec 22, 2009, 4:46:55 PM12/22/09

to

"Chuck" <nosp...@nospam.nospam> wrote in message
news:4195F191-6681-4FBD...@microsoft.com...

Hi

How about using SQLCMD and changing the server\instance that you log into/
Then the script would be the same.

John

Erland Sommarskog

unread,

Dec 22, 2009, 6:17:06 PM12/22/09

to

Chuck (nosp...@nospam.nospam) writes:
> I have about 15 servers which have a link to OracleServerA.
>
> Is there a way to script changing the linked server password.
>
> I tried doing
> EXEC Srv01.master.dbo.sp_addlinkedsrvlogin
> @rmtsrvname = N'OracleServerA', @locallogin = NULL , @useself = N'False',
> @rmtuser = N'system6', @rmtpassword = @NewSystemPassword
>
> EXEC Srv02.master.dbo.sp_addlinkedsrvlogin
> @rmtsrvname = N'OracleServerA', @locallogin = NULL , @useself = N'False',
> @rmtuser = N'system6', @rmtpassword = @NewSystemPassword
>
> EXEC Srv02\Binstance.master.dbo.sp_addlinkedsrvlogin
> @rmtsrvname = N'OracleServerA', @locallogin = NULL , @useself = N'False',
> @rmtuser = N'system6', @rmtpassword = @NewSystemPassword
>
>
> But I get error
> Msg 7202, Level 11, State 2, Line 8
> Could not find server 'Server01' in sys.servers. Verify that the correct
> server name was specified. If necessary, execute the stored procedure
> sp_addlinkedserver to add the server to sys.servers.

Well, on the server you run this script, have you set up linked
servers to the other servers?

As John says, it probably better to do this from a BAT file that
run SQLCMD.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Charles Wang [MSFT]

unread,

Dec 23, 2009, 12:45:37 AM12/23/09

to

Hi Chunk,
If you executed the following script on a SQL Server instance which is not
Srv01:

EXEC Srv01.master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'OracleServerA', @locallogin = NULL , @useself = N'False',
@rmtuser = N'system6', @rmtpassword = @NewSystemPassword

You must first create a linked server to Srv01 on the current SQL Server
instance. Otherwise you will encounter that error message. You can use the
stored procedures sp_addlinkedserver and sp_addlinkedsrvlogin to create the
linked server first.

For more information, please refer to:
sp_addlinkedserver
http://msdn.microsoft.com/en-us/library/ms190479.aspx

Best regards,
Charles Wang

Chuck

unread,

Dec 23, 2009, 9:55:01 AM12/23/09

to

thanks,
I wasn't aware you needed to have a link to a server that you want to
execute a rpc on.

""Charles Wang [MSFT]"" wrote:

> .
>

Chuck

unread,

Dec 23, 2009, 5:48:01 PM12/23/09

to

thanks,
I ended up using sqlcmd

"John Bell" wrote:

> .
>