?
Solved

Dropping an alias caused @@servername to return NULL

Posted on 2009-05-04
4
Medium Priority
?
1,573 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 2000 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

569 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