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 (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – 2005 – OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE
Next Post
SQL SERVER – 2005 – Explanation of TRY…CATCH and ERROR Handling With RAISEERROR Function

Related Posts

35 Comments. Leave new

  • IMPORTANT: Your problem might be the name of the instance instead of the server. To find out use select @@servicename instead

    Reply
  • 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.

    Reply
  • tesfayeteklu
    July 10, 2012 5:31 pm

    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:WINDOWSassemblyGAC_32System.EnterpriseServices2.0.0.0__b03f5f7f11d50a3aSystem.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.

    Reply
  • thanks a bunch

    Reply
  • how to transfer data or view data from one server to another server

    Reply
  • to the point, crisp information in simple language. great stuff.

    Reply
  • 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 ..

    Reply
  • very helpful….

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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.

    Reply

Leave a Reply

Menu