<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
@@servername and serverproperty('servername')...
@@servername and serverproperty('servername') gives different values
Rate Topic
Display Mode
Topic Options
Author
Message
Mohammed Imran Ali
Mohammed Imran Ali
Posted Thursday, August 21, 2008 6:03 AM
Valued Member
Group: General Forum Members
Last Login: Today @ 6:33 AM
Points: 65,
Visits: 202
Hi,
While doing a regular maintenance work, I bumped into the following problem.
The following commands shows different values for the same server.
select serverproperty('servername')
Result => SQL2K5SWPROD
select @@servername
Result => SQL2K5SWSRV
The hostname command gives the result as 'SQL2K5SWPROD'. This is the correct name.
The '
OriginalMachineName
' value in the registry location 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Machines' shows SQL2K5SWSRV. This is the incorrect value.
I am not sure if these are related to each other. Also, if the value is changed, will there be any impact on the server. I don't want to meddle with this production server.
Thanks in advance for your suggestion and direction.
Cheers,
Imran.
Post #556439
Khwaja Arshaduddin
Khwaja Arshaduddin
Posted Thursday, August 21, 2008 6:50 AM
SSC Journeyman
Group: General Forum Members
Last Login: Wednesday, December 08, 2010 3:57 AM
Points: 75,
Visits: 100
It looks like this is Instance name.Did you reinstalled the server?
The ServerName property provides the Windows NT server and instance name that together make up the unique server instance. @@SERVERNAME provides the currently configured local server name.
ServerName property and @@SERVERNAME return the same information if the default server name at the time of installation has not been changed.
Post #556495
Wilfred van Dijk
Wilfred van Dijk
Posted Thursday, August 21, 2008 6:51 AM
SSC Eights!
Group: General Forum Members
Last Login: Wednesday, March 06, 2013 12:56 AM
Points: 898,
Visits: 1,045
Found this answer in BOL:
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.
Wilfred
The best things in life are the simple things
Post #556498
Mohammed Imran Ali
Mohammed Imran Ali
Posted Thursday, August 21, 2008 7:15 AM
Valued Member
Group: General Forum Members
Last Login: Today @ 6:33 AM
Points: 65,
Visits: 202
The networkname was changed to 'SQL2K5SWPROD'. But SQL Server was installed after the name change happened. So I reckon this should show the new name for @@SERVERNAME as well.
I tried
sp_dropserver 'SQL2K5SWSRV', null
Command(s) completed successfully.
sp_helpserver
Msg 15205, Level 16, State 1, Procedure sp_helpserver, Line 17
There are no servers defined.
sp_addserver 'SQL2K5SWPROD','LOCAL'
Command(s) completed successfully.
sp_helpserver
name network_name
-------------- --------------
SQL2K5SWPROD SQL2K5SWPROD
After executing the commands the @@SERVERNAME should be returning 'SQL2K5SWPROD', but the it still returns the incorrect value ['SQL2K5SWSRV']
Any suggestion to get this corrected without a downtime would would be of much help.
Cheers,
Imran.
Post #556530
Wilfred van Dijk
Wilfred van Dijk
Posted Thursday, August 21, 2008 7:24 AM
SSC Eights!
Group: General Forum Members
Last Login: Wednesday, March 06, 2013 12:56 AM
Points: 898,
Visits: 1,045
AFAIK, you need to restart SQL in order to make these changes for @@SERVERNAME
Wilfred
The best things in life are the simple things
Post #556547
Perry Whittle
Perry Whittle
Posted Thursday, August 21, 2008 8:50 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 5,201,
Visits: 11,150
Wilfred van Dijk (8/21/2008)
AFAIK, you need to restart SQL in order to make these changes for @@SERVERNAME
yes that is correct
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
Post #556641
Ronzo
Ronzo
Posted Thursday, August 27, 2009 9:24 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Monday, May 13, 2013 12:35 PM
Points: 502,
Visits: 510
So, as a follow up, what about a more complex situation:
Clustered default instance of SQL Server with different virtual IP address and network name then the virtual name and address of the clustered Windows server it is installed on. (note: virtual as in cluster naming, not as in virtual server stuff).
The @@ServerName reports the SQL cluster name, while serverproperty('servername') reports the Windows cluster name. I do not want to change @@Servername to match serverproperty, I want to make serverproperty match @@Servername. How is this done in a clustered situation like this?
Have Fun!
Ronzo
Post #778407
Michael Valentine Jones
Michael Valentine Jones
Posted Thursday, August 27, 2009 9:46 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 3:14 PM
Points: 2,941,
Visits: 10,481
Ronzo (8/27/2009)
So, as a follow up, what about a more complex situation:
Clustered default instance of SQL Server with different virtual IP address and network name then the virtual name and address of the clustered Windows server it is installed on. (note: virtual as in cluster naming, not as in virtual server stuff).
The @@ServerName reports the SQL cluster name, while serverproperty('servername') reports the Windows cluster name. I do not want to change @@Servername to match serverproperty, I want to make serverproperty match @@Servername. How is this done in a clustered situation like this?
How to: Rename a SQL Server Failover Cluster Instance
http://msdn.microsoft.com/en-us/library/ms178083.aspx
Post #778424
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
