asked on

the use of SQL link server

Dear all expertist,

Right now we plan to create link server for MS SQL server and we run this script:

/****** Object: LinkedServer [TWDB] Script Date: 03/13/2012 14:14:10 ******/
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'TWDB')EXEC master.dbo.sp_dropserver @server=N'TWDB', @droplogins='droplogins'
GO

/****** Object: LinkedServer [TWDB] Script Date: 03/13/2012 14:14:11 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'TWDB', @srvproduct=N'SQLCL10', @provider=N'SQLNCLI10', @datasrc=N'<IP address>,<port number>', @catalog=N'TRACKWORKDB'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TWDB',@useself=N'False',@locallogin=NULL,@rmtuser=N'capdiadmin',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

-------------------------------------------------------------------------------------------------------

So the question is, anyway to make link server connect using server name instead of IP address?

DBA100.

marrowyung

ASKER

is it say that, if I script the link server out in SQL script and change the IP address of Sql name, we have to type in the password again?

DBA100.

marrowyung

ASKER

any way to script put the password also ? or we just, once script the link server object out, manually input login name and password in order to make it work?

ASKER CERTIFIED SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marrowyung

ASKER

jogos,

Yes, you are right and I am saying the same thing about that to my users, however, it only create one time for testing only and I should prepare for this kind of script for setup purpose.

So there are no way except manually do it ourselves?

DBA100.