none
How to find instance name

    Question

  • Hi

     

    I just start to learn SQL server now. I am trying to migrate data from oracle to SQL server 2005 using SQL server migrant assistant for Oracle.  But I don't know how to fiill this connection details

     

     

    SQL Server host:

    <ServerHost>

    SQL Server port
    (leave empty if default):

    <8888>

    SQL Server instance name
    (leave empty if default):

    <your_instance>

    Target database:

    <your_db_name>

    Target schema:

    dbo (by default)

    User name:

    <your_username>

    Password:

    <your_password>

     

    How to find these properties?  Anyone can help?

     

    Thanks

     

    Li

     

    Sunday, April 01, 2007 4:34 AM

Answers

  • Actually it is select @@servicename  (to find the last part of the instance)
    So total name would be select @@servername + '\' + @@servicename


    foxjazz

    My mistake. @@servername works correctly with the instance.

    • Proposed as answer by foxjazz2 Wednesday, June 24, 2009 8:24 PM
    • Marked as answer by Naomi NModerator Wednesday, January 11, 2012 5:10 AM
    • Edited by foxjazz2 Thursday, January 12, 2012 8:24 PM
    Wednesday, June 24, 2009 8:24 PM
  •  

    SQL Server Instance Name :

    In SQL Server Management Studio->RegisterServers->double click your server to open the Object Explorer -> right click to select the properties window ->General Tab - to see the ServerName (instance name)

     

    or Open a new query in SQL Server Management window  and execute the following

    -- to get the sql server instance name

    select @@ServerName 

     

    Thanks.

    Naras.

    Sunday, April 01, 2007 3:36 PM

All replies

  • 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

    • Proposed as answer by stefanobi Thursday, September 24, 2009 5:51 AM
    Sunday, April 01, 2007 6:28 AM
  • In a simple way :

     

    Start ->All progrmas->Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager

    In Configuration Manager -> SQL Server 2005 Services -> double click the SQL Service -> click the Service Tab.

    There you will find the name of the Host.

    In Configuration Manager -> SQL Server 2005 Network configuration ->double click the TCP/IP protcol to see the port no .

     

    or

     

    In SQL Server Management Studio->RegisterServers->double click your server to open the Object Explorer -> right click to select the properties window ->General Tab - to see the ServerName

     

    or Open a new query in SQL Server Management window  and execute the following

    -- to get the server name

    select @@ServerName 

     

    Thanks

    Naras.

    • Proposed as answer by Gorguss Wednesday, November 06, 2013 2:33 PM
    • Unproposed as answer by Gorguss Wednesday, November 06, 2013 2:33 PM
    Sunday, April 01, 2007 6:37 AM
  • Thanks!

     

    What about instance name?  How to find it?

     

    Li

     

    Sunday, April 01, 2007 8:03 AM
  •  

    SQL Server Instance Name :

    In SQL Server Management Studio->RegisterServers->double click your server to open the Object Explorer -> right click to select the properties window ->General Tab - to see the ServerName (instance name)

     

    or Open a new query in SQL Server Management window  and execute the following

    -- to get the sql server instance name

    select @@ServerName 

     

    Thanks.

    Naras.

    Sunday, April 01, 2007 3:36 PM
  • Thanks, Naras, very helpful

     

    Li

    Monday, April 02, 2007 1:07 PM
  • Actually it is select @@servicename  (to find the last part of the instance)
    So total name would be select @@servername + '\' + @@servicename


    foxjazz

    My mistake. @@servername works correctly with the instance.

    • Proposed as answer by foxjazz2 Wednesday, June 24, 2009 8:24 PM
    • Marked as answer by Naomi NModerator Wednesday, January 11, 2012 5:10 AM
    • Edited by foxjazz2 Thursday, January 12, 2012 8:24 PM
    Wednesday, June 24, 2009 8:24 PM
  • very helpful - thanx a lot
    Thursday, September 24, 2009 5:52 AM
  • Keep in mind, sometimes you will have a 'server name' with NO 'instance name;' especially in cases of Clusters, in which case, using a 'default instance,' some product installations want JUST the 'Cluster name,' and you don't need to enter any instance name. Just FYI.
    Wednesday, August 24, 2011 3:18 PM
  • Hi,

    You can see server name by running below queries:

    Select

    @@servername

    SELECT

    * from

    Sys.Servers


    Thanks Shiven:) If Answer is Helpful, Please Vote
    Thursday, December 29, 2011 11:01 AM
  • The easy way is , open SQL Server Configuration Manager from the Start menu. Under SQL Server Services, you see the SQL Server main service. The service name contains the connection string in parentheses.
    Wednesday, January 11, 2012 2:04 AM
  • short answer is 

    SELECT SERVERPROPERTY ('InstanceName')

    Bear in mind that by default (at least for SQL Server 2012) the instance name is empty THEREFORE instance name should NOT be provided within connection string! 

    P.S. Do not assume it is MSSQLSERVER if it has not been defined during the installation


    • Edited by IGKU Tuesday, April 08, 2014 8:02 PM
    Tuesday, April 08, 2014 7:47 PM