server name and network server name not the same

Question

Post reply

server name and network server name not the same

Snargables

SSCrazy Eights

Points: 8855
More actions
April 3, 2017 at 8:08 am

#322741

I'm trying to setup replication on Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
    Jun 9 2015 12:06:16
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
It's failing to setup because the servername is different that the network name. After researching it appears i need to do the below. My issue is that this is a production server and if i have apps connection the servername in the sp_dropserver command then will they now longer to connect? Are there any other concerns?
-- Use the Master database
USE master
GO
-- Declare local variables
DECLARE @serverproperty_servername varchar(100),
@servername varchar(100)
-- Get the value returned by the SERVERPROPERTY system function
SELECT @serverproperty_servername = CONVERT(varchar(100), SERVERPROPERTY('ServerName'))
-- Get the value returned by @@SERVERNAME global variable
SELECT @servername = CONVERT(varchar(100), @@SERVERNAME)
select @servername
-- Drop the server with incorrect name
EXEC sp_dropserver @server=@servername
-- Add the correct server as a local server
EXEC sp_addserver @server=@serverproperty_servername, @local='local'

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Snargables SSCrazy Eights Points: 8855 More actions · Answer 1

Snargables

SSCrazy Eights

Points: 8855

April 3, 2017 at 8:48 am

#1936308

this is the error i get

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

how far have you got, is replication enabled, more detail please.

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Snargables SSCrazy Eights Points: 8855 More actions · Answer 3

I haven't done anything yet. I was planning on doing the drop server this weekend for the 100dw01-new. I don't think it should hurt anything because there is no alias for it so u cant u connect to it. I think it's residue from when they move the environment to a new box. The new box was called 100dw01-new initially. Once they did the move they renamed it to 100dw01. I suspect after the move and first reboot it loaded those values. Then they renamed it and did another reboot and it left some orphaned data in there

It bothers me that I have to drop it. I was hoping someone would advise to just update all the data from 100dw01-new to 100dw01

Snargables SSCrazy Eights Points: 8855 More actions · Answer 4

closing the loop here. i ended up doing the following

-- Use the Master database
USE master
GO

-- Declare local variables
DECLARE @serverproperty_servername varchar(100),
@servername varchar(100)

-- Get the value returned by the SERVERPROPERTY system function
SELECT @serverproperty_servername = CONVERT(varchar(100), SERVERPROPERTY('ServerName'))

-- Get the value returned by @@SERVERNAME global variable
SELECT @servername = CONVERT(varchar(100), @@SERVERNAME)
select @servername
-- Drop the server with incorrect name
EXEC sp_dropserver @server=@servername
--The good servername was already created as a remote server so i had to remove it's linked server before adding it as the local
EXEC master.dbo.sp_dropserver @server=N'XXXXXX', @droplogins='droplogins'
-- Add the correct server as a local server
EXEC sp_addserver @server=@serverproperty_servername, @local='local'

exec sp_serveroption 'XXXXX','Data Access','true'

Restart sql service. You may need to reboot however for whatever reason i didn't have to. Some articles said it required a reboot if your using reporting services. I'm using reporting services and it wasn't affected.