Solved

Dropping an alias caused @@servername to return NULL

Posted on 2009-05-04
4
1,458 Views
Last Modified: 2012-06-21
I was working on adding a replication distribution for a SQL 2005 server.  It mentioned an old alias as the server name.  I ran sp_deleteserver for the alias, but it was set to the id of 0 in sp_helpserver.  Now when I try to set up replication, there is no server name assigned.  the correct name has an id of 2. attempts at using sp_dropserver 'correct_name' cause an error

Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 56
There are still remote logins or linked logins for the server

i've tried to use sp_addserver 'correct_name','LOCAL','duplicate_ok' to set id 0, but it only changed the value of correct_server to 1 in "select * from sys.servers" .  select @@servername is still NULL.

how can I safely change the name of the server to the entry listed at ID 1 of sp_helpserver without causing any loss of login information in order to keep the name as well as enable @@servername so i can turn on replication
0
Comment
Question by:RankenIS
  • 2
  • 2
4 Comments
 
LVL 3

Expert Comment

by:mfreuden
ID: 24304383
I've had a similar problem with @@servername returning a null and not being able to use replication.

You may be able to use the sp_dropserver command successfully by shutting down "SQL Server Agent", and then trying the sp_drop server command (This will close all of the replication processes).

When I had  this problem, I had to adjust the sysservers table manually... You'll have to use the "dedicated admin connection" program to adjust the table manually (from the server).

C:\Documents and Settings\paul>sqlcmd /A

1> USE master
2> GO
1> update sysservers set srvname='name' where srvid=1
2> GO


You'll need to stop and restart sqlserver.

0
 

Author Comment

by:RankenIS
ID: 24304988
I was reading online that SQL Server pulls the @@servername variable from the
row in master..sysservers with srvid=0.  presently there is no row with srvid=0.  
the value at srvid=1 is the name i want to use for the servername.  if I stop the agent and then
exec sp_dropserver
on the value at srvid 1 to be followed by
exec sp_addserver 'name' 'local'
do I run the risk of breaking the instance, security, or anything else?  
BTW, I only have two entries in master..sysservers.  srvid 1 is the correct srvname.  srvid2 is ADSI which I was working with a while back on trying to modify some AD accounts via sql.  
0
 

Author Comment

by:RankenIS
ID: 24308230
i found an article at http://support.microsoft.com/kb/217395 that looked like the method to fix it, but was wondering if
sp_dropserver local_server_name, droplogins
removes logon accounts or just kicks users out of their sessions.  Also, does sp_dropserver/addserver retain the configuration information for the local instance or would i have to reset that info also?
0
 
LVL 3

Accepted Solution

by:
mfreuden earned 500 total points
ID: 24308827
The droplogins command will remove all remote logins with the server (not local user logins/acconts),
the logins that will be removed are in master..sysremotelogins with the srvid of the server you're removing.

I don't think you'll be able to drop a server it's being used as an existing subscriber in replication though.

You will probably have to manually create a record in syslogins with a srvid of 0 to get your repliation working again without losing any data that is in the queue.


0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now