Get SQL Server instance name from registry

Question

I'm trying to create a database and run some SQL scripts from my Windows installer, which works fine as long as I hard-code the server name. If I try to get the server name from the registry, the installer will fail. In this case, I have the 32-bit SQL Server installed on a 64-bit OS, so I'm using the following registry key:

<Property Id="SQL_SERVER_INSTANCE">
  <RegistrySearch Id="rs_ServerInstance"
                  Type="directory"
                  Root="HKLM"
                  Key="Software\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL"
                  Name="SQLEXPRESS" />
</Property>

Then I plug this property into my database setup:

    <sql:SqlDatabase Id="NewSQLDB" Database="wix_test_DB" User="Users" Server="[SQL_SERVER_INSTANCE]"
                     CreateOnInstall="yes" DropOnUninstall="yes" ContinueOnError="no" >
      <sql:SqlScript ..."/>
    </sql:SqlDatabase>

And it will fail. I've checked the registry and the only Name/Value pair there is "SQLEXPRESS" and the value is not a server instance. The server itself is called "SQLEXPRESS", but if it's stored as the Name, then I need to know it in advance to be able to search for it, defeating the purpose of attaining it in the first place.

I understand why this code doesn't work, but I don't know how I can get the server name. I've read that you can use [SQLSERVER] as the SqlDatabase@Server to get the default server, but the installer still failed when I tried this.

Is there a way to get the server name (not necessarily using the registry)?

I think `SQLEXPRESS` is not the instance name of SQL Server and it is there because of installing some software like Visual Studio or etc. The default instance name is `MSSQLSERVER` and for more check [this question](http://stackoverflow.com/q/141154/4519059) ;). — shA.t, Jun 07 '15 at 04:47
The instance name is `SQLEXPRESS`. I created it with that name. I think the instances listed in the registry path that I'm using might be intended for human use only. The link you posted looks promising though, thanks! — Oliver Nicholls, Jun 07 '15 at 21:50

score 1 · Answer 1 · answered Jul 06 '17 at 12:13

actually the MSSQLSERVER is the default SQL instance, and its a reserved keyword that can't be used in an instance name according to this, so what I think you are looking for is SQL instances, if you are you can find in the path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server there is a value that is called InstalledInstances it contains the instances names and if you use the code the registry keys are brought as lists. keep in mind that this path works if you have default named instances or named instances, and after that you should exclude MSSQLSERVER from the list

score 1 · Answer 2 · edited Aug 27 '19 at 07:08

1

You can use the below to get all named instance in your server Using ‍Powershell‍:

(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances

edited Aug 27 '19 at 07:08

iminiki

2,549
12
35
45

answered Aug 27 '19 at 04:11

Mohamed k.elsawy

11
2

Get SQL Server instance name from registry

2 Answers2