locked
Default SQL Server Instance Name RRS feed

  • Question

  • I am using ADO to connect to SQL Server. It seems that in SQL Server Management Studio, the local computer name can always be used as the default SQL Server instance name on the local computer? Is that correct for all the cases? Are there any document describing this?

    Also I think Windows API GetComputerName is used to get the local computer name.

    • Edited by chcw Wednesday, July 2, 2014 4:54 PM
    Wednesday, July 2, 2014 4:52 PM

Answers

  • My computer name is "MyComp" and I install a SQL Server default instance as "MSSQLSERVER".

    As you wrote, it's a "Default" unnamed instance; MSSQLSERVER is only a "pseudo" Name shown up in e.g. Windows Service Manager.

    But you can not use it like for a named instance; to logon you have to use only the machine Name.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Elvis Long Tuesday, July 15, 2014 2:47 AM
    Friday, July 4, 2014 2:05 PM
  • But the problem is that I can connect to SQL Server by using server name MyComp, but cannot connect with server name MyComp\MYSQLSERVER.

    Try connecting from SSMS using either the host name, ".", or "(local)"  and run the following query.  If this returns NULL, that means you have a default instance installed and do not need to specify the instance name in the connection string.

    SELECT SERVERPROPERTY('InstanceName');


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Elvis Long Tuesday, July 15, 2014 2:47 AM
    Friday, July 4, 2014 3:15 PM

All replies

  • Hello,

    Yes, for a default instance you have to use only the machine name, while for a named instance you always have to use machinename\instance name.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Elvis Long Thursday, July 3, 2014 5:38 AM
    Wednesday, July 2, 2014 5:02 PM
  • Careful - you might be making an assumption that a default instance has been installed on a particular machine - there is no such requirement. Perhaps you should rephrase your question as:  how do I enumerate the instances of sql server installed on a machine? If that is your goal, you can search using that terminology to find suggestions.
    Wednesday, July 2, 2014 5:24 PM
  • Also it depends on the edition as well. For example if its express name would be machinename\SQLEXPRESS etc So you cant always say it would be machinename itself.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Elvis Long Thursday, July 3, 2014 5:38 AM
    Wednesday, July 2, 2014 6:13 PM
  • My computer name is "MyComp" and I install a SQL Server default instance as "MSSQLSERVER".

    However, it seems if I use "MyComp" as the server name, then the connection is OK.

    If I use "MyComp\MSSQLSERVER", then the connection will fail.

    Why?

    Thursday, July 3, 2014 4:54 PM
  • My computer name is "MyComp" and I install a SQL Server default instance as "MSSQLSERVER".

    However, it seems if I use "MyComp" as the server name, then the connection is OK.

    If I use "MyComp\MSSQLSERVER", then the connection will fail.

    Why?

    When you install the default instance, you specify an InstanceId (default MSSQLSERVER).  This is not the same as an instance name (named instance).  The InstanceId is a unique identifier used in registry paths, folder names, etc. and is not specified in the connection string.

    You can always connect to the local default instance using "." as the server name.  Local connections uses the shared memory protocol by default so it doesn't matter if the server to listen on a non-default TCP port.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, July 4, 2014 2:13 AM
  • I am confused. I check the article at http://blogs.msdn.com/b/askjay/archive/2011/10/11/how-can-i-get-a-list-of-installed-sql-server-instances.aspx and find two entries under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL:

    That is:

    MYSQLSERVER

    SQLEXPRESS

    so I believe these two are Instance names, not Instance Ids? Since they are under the registry entry of \Instance Names\

    Friday, July 4, 2014 2:52 AM
  • Yes, you have two named instances, no default instance, so use

    MyComp\MYSQLSERVER
    MyComp\SQLEXPRESS

    to connect to the instances.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 4, 2014 6:41 AM
  • But the problem is that I can connect to SQL Server by using server name MyComp, but cannot connect with server name MyComp\MYSQLSERVER.
    Friday, July 4, 2014 1:47 PM
  • My computer name is "MyComp" and I install a SQL Server default instance as "MSSQLSERVER".

    As you wrote, it's a "Default" unnamed instance; MSSQLSERVER is only a "pseudo" Name shown up in e.g. Windows Service Manager.

    But you can not use it like for a named instance; to logon you have to use only the machine Name.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Elvis Long Tuesday, July 15, 2014 2:47 AM
    Friday, July 4, 2014 2:05 PM
  • But the problem is that I can connect to SQL Server by using server name MyComp, but cannot connect with server name MyComp\MYSQLSERVER.

    Try connecting from SSMS using either the host name, ".", or "(local)"  and run the following query.  If this returns NULL, that means you have a default instance installed and do not need to specify the instance name in the connection string.

    SELECT SERVERPROPERTY('InstanceName');


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Elvis Long Tuesday, July 15, 2014 2:47 AM
    Friday, July 4, 2014 3:15 PM