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.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

marrowyungSenior Technical architecture (Data)Author Commented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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?
0
jogosCommented:
Think about it. It wouldn't be a great security when scripting out a user the password is scripted with it automaticly.

When you put the password in the script file, see password is replaced by '####' again after use so it's not open readable on the filesystem (or trashbin).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.