@@ServerName returns wrong value in SQL SERVER 2008

Question

One of my production server configured as Mirror server. When i run @@ServerName query on Principal server i got right server name. but when i execute same query on mirror server it returns Principal server name. I have windows server 2008. I don't know why it happens.

---

Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

Answer 1

I found this link http://www.hagrin.com/332/fixing-sql-server-replication-requires-actual-server-name-make-connection-server-error which may be of help.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

blog is great but i have problem with mirror server. Principal server name is same as windows name but mirror server display principal name while when i connect database using SSMS on mirror server it shows different name on database connection.

---

Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

Answer 3

This is a byproduct of how your environment is set up, I don't believe it's specifically a SQL Server issue.  However, I confess I don't know if mirroring intentionally mimics the principal server's name or not... I didn't think so, but I don't know.

Here's code to show three different pieces of information:  In most situations and for most people, all three will have the same values, although I bet you have your @@ServerName different to the other two:

Select ServerProperty('machinename'), ServerProperty('ServerName'), @@ServerName

Here are two pieces of help text from SQL Server Online:  (In other words, somebody or something (the mirroring setup) somewhere ran sp_addserver to change the @@ServerName value at your site.)

"
SQL Server Setup sets the server name to the computer name during installation. To change the name of the server, use sp_addserver, and then restart SQL Server.
"

"
Although the @@SERVERNAME function and the SERVERNAME property of SERVERPROPERTY function may return strings with similar formats, the information can be different. The SERVERNAME property automatically reports changes in the network name of the computer.

In contrast, @@SERVERNAME does not report such changes. @@SERVERNAME reports changes made to the local server name using the sp_addserver or sp_dropserver stored procedure.
"

Answer 4

what is the your SQL Server version?

I remember having a case using Management Studio 2008 where the session window displayed one server name while @@servername reported something different (same as you). That happened when one of the involved servers was on remote location and the network link was highly used. I think it was fixed with SQL 2008 SP2

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 5

please check

1. http://support.microsoft.com/kb/972207
2. http://www.julian-kuiters.id.au/article.php/sqlserver2005-rename-server

Answer 6

thanks virat for your help. but i cannot change my server name now as it is production server.

---

Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

Answer 7

Thanks this query has shown the difference. I think its installation issue but i cann't change its name. Now i have to change all @@servername logic with Server Property('ServerName'). 

I have got useful info about this from msdn following link

http://msdn.microsoft.com/en-us/library/aa933172(v=sql.80).aspx

thanks for sharing your thoughts.

---

Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com