13

I'm installing some software and I need to tell it to use the default instance of SQL Server, however the Installshield GUI insists that I enter in information for a named instance.

The target SQL Server is version 2008R2

I'm hoping there is a clean hack that I can use such as "." or "default" but I haven't guessed it yet. I've also tried MSSQLSERVER, and that didn't work either.

1
  • The question is old, but I think now I have an answer for you! (see here) Commented Sep 15, 2017 at 11:53

5 Answers 5

9

Run this query:

SELECT @@SERVERNAME + '\' + @@SERVICENAME AS InstanceName

and use the result as your instance name.

Sign up to request clarification or add additional context in comments.

3 Comments

I tried this to connect to default SQL Server instance through SSMS but it doesn't work. If I simply try . or <machineName> it works. Query SELECT @@SERVICENAME AS InstanceName returns MSSQLSERVER. <machineName>\MSSQLSERVER doesn't work
For default instance this nomenclature doesn't work even from code. It has to be either . or <MachineName>.
The instance name created this way is not correct, if no instance name is specified and there is only a default instance on the server.
4

All SQL server instances are stored in the Windows Registry. You can query the registry by using the Windows tool C:\Windows\System32\Regedt32.exe and browse/search there, you can do it by using a language, like C#  (see Example 1 there) or even T-SQL (also known as Transact-SQL).

In this answer, I'll show you how to do it with T-SQL. Use this script, which I found here, to determine ServerName, InstanceName, HostName and PortNumber:

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

If the InstanceName is null, it means there is no named instance configured, then you have two, non-exclusive options:

  1. Specify the ServerName to access the default instance
    - or -
  2. Use the Sql Server Configuration Manager -> SQL Native Client XX.Y Configuration -> Aliases to define an alias. For example:
    Define_Alias
    will set up alias Inst1 for the local SQL server. Specify (local)\Inst1,1433 to access it. Of course, instead of (local) you can also use the ServerName.

    IMPORTANT: After setting up the alias, you need to restart the SQL service of the related instance or reboot your PC, otherwise it will not be accessible immediately. If you're using a port other than the default port 1433, you might need to open your local firewall in order to get this working.

Note: If you don't have it in the start menu, the SQL Server Configuration Manager can be found in C:\Windows\System32. Look for SqlServerManagerXX.msc (where XX is the SQL version, e.g. XX=11 or XX=13).

Comments

3

If you have a default instance configured just use the server hostname as instance

Comments

0

Try MSSQLSERVER. That may be the default instance name according to this MSDN page.

Comments

0

The setup wizard will take MSSQLSERVER which means "default" instance.

See step 12 in the MSDN page

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.