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
Solved

@@servername - where does instance get that info

Posted on 2010-08-20
12
511 Views
Last Modified: 2012-05-10
is it the 'computer name' of the SQL Server? or how/where does it get that info from?

thanks
0
Comment
Question by:anushahanna
  • 3
  • 3
  • 2
  • +4
12 Comments
 
LVL 12

Accepted Solution

by:
mcv22 earned 63 total points
ID: 33490009
Its the local server name (initialized to computer name during setup). To change the name of the server, use sp_addserver, and then restart SQL Server. For the default instance its usually the computer name. For the named instance its computername\instancename.

The servername can be changed using sp_addserver and sp_dropserver procedures.
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 63 total points
ID: 33490015
This looks like a definition question, the only best answer would be from the book itself...
http://msdn.microsoft.com/en-us/library/ms187944.aspx

@@servername reports from master..sysservers or sys.servers and is affected by sp_addserver, sp_dropserver

select SERVERPROPERTY('SERVERNAME')

SERVERPROPERTY / SERVERNAME returns the actual network name of the server
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 125 total points
ID: 33490017
It's the name that the computer running SQL Server is known by the network.  Usually the same as the name of the computer, but you can name it otherwise.  
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 28

Assisted Solution

by:sammySeltzer
sammySeltzer earned 63 total points
ID: 33490021
During SQL Server setup, the computer or servername is setup at that time.


To get the servername where your sql server is set up, simply run:

SELECT @@SERVERNAME AS 'Server Name'

If for some reason you wish to change server name, you use this syntax:

 sp_addserver @server or

 sp_addserver 'servername'

You run this within sql query window

Hope this answers your question.
0
 
LVL 2

Assisted Solution

by:rajeshprasath
rajeshprasath earned 62 total points
ID: 33490200
while installing the SQL Server it automatically takes the host name as @@SERVERNAME, But if you change the host name of the computer after installation. It won't change automatically.

The @@SERVERNAME remains the old host name. So at that time if you want to change the @@SERVERNAME you can do it by the following syntax,

sp_addserver 'new server name'

So it defines the name of the local instance of SQL Server.

you can also refer the following url,
http://msdn.microsoft.com/en-us/library/aa933172%28SQL.80%29.aspx
http://technet.microsoft.com/en-us/library/ms174411%28SQL.90%29.aspx
0
 
LVL 12

Assisted Solution

by:NormanMaina
NormanMaina earned 124 total points
ID: 33490329
select @@servername  for the default instance returns the server name while for named instances, it returns the computername\instancename.
That info is stored in the master table during install of the instances.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33500662
Thanks for your helpful input.

@@servername is not bringing the name of the computer.

I tried sp_addserver 'new server name' syntax, and restarted the SQL Service; still it reflects the old name? what am i missing?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33502463
what do you see here:

Select hostname(), * from sys.servers

Pay attention to the local host,  id=0, which is where @@servername comes from




0
 
LVL 6

Author Comment

by:anushahanna
ID: 33502749
dqmq,
I get
'hostname' is not a recognized built-in function name.

>> id=0, which is where @@servername comes from

after giving a new name with sp_addserver and restarting sql service, what is incomplete that the change has not yet occurred?

thanks
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 125 total points
ID: 33503052
sorry, should have been
Select host_name(), * from sys.servers
0
 
LVL 12

Assisted Solution

by:NormanMaina
NormanMaina earned 124 total points
ID: 33503213
anushahanna:The question you asked originally did not suggest you had a problem with your server name.

A couple of questions to help understand your problem.
1)what version of sql server have you installed that you want to rename?
2)start a new quesry on ssms and type:sp_helpserver :then execute
This will show you the installed instances in your machine
What instance do you want to rename?
3)Run the below code get your server name,your instance name,hostname and port.

set nocount on

Declare @key Varchar(100), @PortNumber varchar(20)

if charindex('\',CONVERT(char(20), SERVERPROPERTY('servername')),0) <>0

begin

set @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'+@@servicename+'\MSSQLServer\Supersocketnetlib\TCP'

end

else

begin

set @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supersocketnetlib\TCP'

end

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@key,@value_name='Tcpport',@value=@PortNumber OUTPUT

SELECT CONVERT(char(20), SERVERPROPERTY('servername')) ServerName,

CONVERT(char(20), SERVERPROPERTY('InstanceName')) instancename,

CONVERT(char(20), SERVERPROPERTY('MachineName'))

as HOSTNAME, convert(varchar(10),@PortNumber) PortNumber

0
 
LVL 6

Author Comment

by:anushahanna
ID: 33504310
NormanMaina, thanks for your feedback on those points.

Yes, I can see I am going outside of what I asked. it is like at least 2 questions in one post;
and you had already helped me with the solution. sorry for the confusion.

I have put the follow question below:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_26423214.html
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_26423215.html

Thanks again to all the experts.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL R 21 27
Index and Stats Management-Specific tables 8 22
RAISERROR WITH NOWAIT 2 17
SQL Availablity Groups List 2 8
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

860 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