Solved

Dropping an alias caused @@servername to return NULL

Posted on 2009-05-04
4
1,452 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 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

15 Experts available now in Live!

Get 1:1 Help Now