Solved

Dropping an alias caused @@servername to return NULL

Posted on 2009-05-04
4
1,487 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…

734 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