SELECT @@ServerName returns Null after renaming a Server

Question

Post reply

SELECT @@ServerName returns Null after renaming a Server

Welsh Corgi

SSC Guru

Points: 116520
More actions
August 29, 2012 at 11:28 am

#266779

sp_dropserver <old_name>;
GO
sp_addserver <new_name>, local;
GO
Restart the instance of SQL Server.
Execute the Following Command:
SELECT @@ServerName
It returns Null
Execute the following Command:
SELECT *
FROM sys.servers
It returns the Server Name as well as the addtional Columns.
Any ideas on what could be causing this?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

I noticed that the Server_ID had a value of 1.

I did some research and found that when this happend you should drop the Server.

Then all it with a local argument:

sp_addserver 'MyServerName', 'local';

Then restart the Server.

Then SELECT@@ServerName returns the Server Name.

I wonder if I may encounter any issues in the future if that is the case I uninstall & reinstall SQL Server?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Yeah, I've had this happen to me too on customer sites. It was really annoying because it would NULL-Propogate across all of my email and dynamic SQL strings anywhere that I had not anticipated it (which was pretty much everywhere). Ultimately, I had to rewrite tons of my collection, monitoring and alerting procedures to use SERVERPROPERTY(servername) instead.

Grrr... :angry:

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

The problem seemed to be as a result of a server_id with a value of one as opposed to zero in the sys.servers Table.

There may have been some Null Values in the the sys.servers Table as well.

Should I be safe and reinstall?

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

No. If it's returning correct now it's fine.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass