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.

makerofthings7
  • 60,103
  • 53
  • 215
  • 448
  • The question is old, but I think now I have an answer for you! (see **[here](https://stackoverflow.com/a/46238551/1016343)**) – Matt Sep 15 '17 at 11:53

5 Answers5

9

Run this query:

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

and use the result as your instance name.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • I tried this to connect to default SQL Server instance through SSMS but it doesn't work. If I simply try `.` or `` it works. Query `SELECT @@SERVICENAME AS InstanceName` returns `MSSQLSERVER`. `\MSSQLSERVER` doesn't work – RBT Sep 28 '16 at 09:58
  • 1
    For default instance this nomenclature doesn't work even from code. It has to be either `.` or ``. – RBT Sep 29 '16 at 06:09
  • 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. – Matt Sep 15 '17 at 11:27
3

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

user2911284
  • 31
  • 1
3

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).

Matt
  • 25,467
  • 18
  • 120
  • 187
0

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

Quick Joe Smith
  • 8,074
  • 3
  • 29
  • 33
0

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

See step 12 in the MSDN page

gbn
  • 422,506
  • 82
  • 585
  • 676