Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Dropping an alias caused @@servername to return NULL

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

721 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