SQL SERVER – Find Name of The SQL Server Instance

Few days ago, there was complex condition when we had one database on two different server. We were migrating database from one server to another server using nightly backup and restore. Based on database server stored procedures has to run different logic. We came up with two different solutions.

1) When database schema is very much changed, we wrote completely new stored procedure and deprecated older version once it was not needed.

2) When logic depended on Server Name we used global variable @@SERVERNAME. It was very convenient while writing migrating script which depended on server name for the same database.

Syntax:
SELECT @@SERVERNAME AS 'Server Name'
ResultSet:
Server Name
——————–
SQLAUTHORITY

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

35 thoughts on “SQL SERVER – Find Name of The SQL Server Instance

  1. After changing the server name, the @@SERVERNAME still returns the old server name, any idea where @@SERVERNAME is stored so that I can update it to reflect the new server name?

  2. After changing the server name, the @@SERVERNAME still returns the old server name, any idea where @@SERVERNAME is stored so that I can update it to reflect the new server name?

    It seems that SERVERPROPERTY(‘servername’) returns the new name of the server.

  3. @Goerge

    Did you restart your SQL Server Instance after you changed name?

    Also is it a default instance or named instance ?

    ~ IM.

  4. Yes, we restarted the default instance after renaming the server.

    I’m not sure why the @@SERVERNAME has the old name, but at least the SERVERPROPERTY(‘servername’) returns the new server name.

  5. An error has occured while establishing a connection to server.when connecting to sql server 2005,this failure may be caused by the fact that under the default setting sql server does not allow remote connection ,(provider:Named pipes provider,error:40-could not open a connection to sql sever )(Microsoft sql server error;53)

  6. That command gives you the server instance name which is only helpful if it is the name of the server. If you are in a clustered environment of want the actual physical name of the server run this…

    SELECT SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’);

  7. That command gives you the server instance name which is only helpful if it is the name of the server. If you are in a clustered environment of want the actual physical name of the server run this…
    This only works on SQL 2005 and above though.

    SELECT SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’);

  8. The problem arises on Windows 2008. SELECT SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) will return SQL virtual name rather then physical node name.

  9. pls help me i’m using windows 7 professional
    i’m getting this error when i connect to sql server..
    ADDITIONAL INFORMATION:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

  10. pls help me i’m using windows 7 enterprise
    i’m getting this error when i connect to sql server..
    ADDITIONAL INFORMATION:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

  11. I have the same error message :
    ” A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)”
    Can anyone help me

  12. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
    i am trying to connect to sql server from asp.net page i am getting this error.my server name-NISHITHA-PC\SQLEXPRESS that is local system . i am using sql server 2005
    what is the proble

  13. Hi everyone, i’m installing Primavera 6 for which i need server name which actually comes through SQL server management studio express but the problem is i can’t see anything in server name box. Even though, if i went for browse for more i’ld not retrieve any server list/name. Please help me i really need a server name otherwise i can’t install my software.

  14. it is an asp application that will work with sql database.when i run it i get the this error message on my browser.

    error CS0006: Metadata file ‘C:\WINDOWS\assembly\GAC_32\System.EnterpriseServices\2.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll’ could not be found
    what is this means do i need to modify the code on my application i use an asp .net application.

  15. why dont u all understand we are looking for the problem that server name is not found in sqlserver during establishing connection to object explorer server name does not appear ..

  16. i am using ms sql server express edition 2005. on the startup it asks for ‘servername’ to connect the server. how i got the servername when i want to run the queries on my pc only

  17. Hi PinalDave,

    There might be chances that we might need instance name and below is the query:
    SELECT @@SERVICENAME AS ‘Service Name’

    I needed instance name to start and stop the SQL server agent using command prompt and did this as below:
    net start “SQL SERVER AGENT()”

    I just shared as FYI..
    I know you might know this :) but wanted to share this to all to those who are new to SQL Server ( like me :P)

    Thanks,
    Sudheer

  18. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

  19. Hi,
    could you please provide the answer
    1.where the clustered index and non clustered index stored in sql server
    2.what is reason behind that truncate will not reset identity and delete will reset identity ..
    is there any specific reason pleas elaborate.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s