Avatar of marrowyung
marrowyung
 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.
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon
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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61