SQL Server: How to Select the Installation Path

SQL Server: How to SELECT the installation path?

How to select the installation path

Note: xp_instance_regread doesn't read the registry key you specify, but instead converts that key path into the appropriate path for the specific SQL Server instance you're running on. In other words: xp_regread fails where xp_instance_regread succeeds.

SQL Server Installation Directory

declare @rc int, @dir nvarchar(4000) 

exec @rc = master.dbo.xp_instance_regread
      N'HKEY_LOCAL_MACHINE',
      N'Software\Microsoft\MSSQLServer\Setup',
      N'SQLPath', 
      @dir output, 'no_output'
select @dir AS InstallationDirectory

SQL Server Backup Directory

declare @rc int, @dir nvarchar(4000) 

exec @rc = master.dbo.xp_instance_regread
      N'HKEY_LOCAL_MACHINE',
      N'Software\Microsoft\MSSQLServer\MSSQLServer',
      N'BackupDirectory', 
      @dir output, 'no_output'
select @dir AS BackupDirectory

SQL Server 2000 Location Functions

SQL Server is not a valid installation folder how to fix location

please see if what sql server version\edition (64 or 32bit) you try to install
what is your OS?

and if you have the right .exe, right click and "run as admin" i hope this one will help you. :)

C# How to get SQL Server installation path programatically?

using(RegistryKey sqlServerKey = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server"))
{
    foreach (string subKeyName in sqlServerKey.GetSubKeyNames())
    {
        if(subKeyName.StartsWith("MSSQL."))
        {
            using(RegistryKey instanceKey = sqlServerKey.OpenSubKey(subKeyName))
            {
                string instanceName = instanceKey.GetValue("").ToString();

                if (instanceName == "MSSQLSERVER")//say
                {
                    string path = instanceKey.OpenSubKey(@"Setup").GetValue("SQLBinRoot").ToString();
                    path = Path.Combine(path, "sqlserver.exe");
                    return path;
                }
            }
        }
    }
}

How to get path to active SQL Server

This is all stored in the registry for the directory paths, and you can retrieve it with Get-ItemPropertyValue. First you have to find the instance name that is running on your server.

$InstanceName=Get-ItemPropertyValue -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL' -Name 'MSSQLServer'

Once you have that you can get the rest easily enough from the Setup key for that instance:

$BackupDir     = Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$InstanceName\MSSQLServer" -Name 'BackupDirectory'
$RootDataDir   = Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$InstanceName\Setup" -Name 'SQLDataRoot'
$MasterDataDir = Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$InstanceName\MSSQLServer\Parameters" -Name 'SQLArg0' | ForEach-Object {$_.Substring(2)}
$MasterLogsDir = Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$InstanceName\MSSQLServer\Parameters" -Name 'SQLArg2' | ForEach-Object {$_.Substring(2)}

The last two values have some extra characters before the path so I trimmed those, you may want to double check that yours are correct once you get the values. If you want other paths just poke around the MSSQLServer and Setup keys in the registry to find what you're looking for.

Detecting SQL server install directory

I finally got it working, the problem was that the fragment wasint being called I had to add <ComponentGroup Id="Fake" /> to the fragment then declare it with the other componentRefs.

The following code detects sqlserver 2008 copyes the connection manager and the data reader to the correct dts folders and installs them both in GAC after.

Hope this helps someone.

<?xml version="1.0" encoding="UTF-8"?>
<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi">
  <Product Id="*" Name="Daimto for SSIS (SQL Server 2008)" Language="1033" Version="1.0.0.0" Manufacturer="DAIMTO" UpgradeCode="84543418-55c4-48c0-b5be-2496fb84ffb6">
    <Package InstallerVersion="200" Compressed="yes" InstallScope="perUser"  InstallPrivileges="elevated" AdminImage="yes" />
    <MediaTemplate EmbedCab="yes" CompressionLevel="high" />

    <MajorUpgrade DowngradeErrorMessage="A newer version of [ProductName] is already installed." />

    <Feature Id="SSISMSSQL" Title="SSIS Components" Display='expand'  Level='1'>
      <ComponentRef Id='SSISPipelineComponents' />
      <ComponentRef Id='SSISConnections' />
      <ComponentGroupRef Id="CopyGAC"/>
      <ComponentGroupRef Id="Fake"/>
    </Feature>
    <UIRef Id="WixUI_Minimal"/>
  </Product>

  <!-- Get install location of SQL Server 2008 -->
  <Fragment>
    <Property Id="SQLSERVERINSTALLFOLDER">
      <RegistrySearch Id='InstallDir'
                    Type='directory'
                    Root='HKLM'
                     Key="SOFTWARE\Microsoft\Microsoft SQL Server\100" Name="VerSpecificRootDir" />

    </Property>
    <Condition Message='Install Folder not found'>SQLSERVERINSTALLFOLDER</Condition>
    <ComponentGroup Id="Fake" />
  </Fragment>

  <!-- map the directory structures -->
  <Fragment>
    <Directory Id = "TARGETDIR" Name="SourceDir">
      <Directory Id="GAC" Name="GAC" />
      <Directory Id="SQLSERVERINSTALLFOLDER" >
        <Directory Id="DTSDir" Name="DTS">
          <Directory Id="PIPELINECOMPONENTS" Name="PipelineComponents" />
          <Directory Id="CONECTIONS" Name="Connections" />
        </Directory>
      </Directory>
    </Directory>

    <!-- Set TARGETDIR dir to c: -->
    <SetDirectory Id="TARGETDIR" Value="[WindowsVolume]" />
  </Fragment>

  <!-- copie files. -->
  <Fragment>

    <!-- copy data reader files -->
    <ComponentGroup Id="SSISPipelineComponentsgrp" Directory="PIPELINECOMPONENTS">
      <Component Id="SSISPipelineComponents" Guid="{51CE96C7-42BF-4CE7-AE88-5C0085868062}" >
        <File Id="Daimto.SSIS.Tasks.Current" Name="Daimto.SSIS.Tasks.Current.dll" DiskId="1" KeyPath="yes" Source="$(var.Daimto.SSIS.Tasks.Current.TargetPath)" />
        <File Id="Newtonsoft.Json.dll" Name="Newtonsoft.Json.dll" DiskId="1" KeyPath="no" Source="$(var.ProjectDir)Externals\Newtonsoft.Json.dll" />
      </Component>
    </ComponentGroup>

    <!-- copy connection manager file -->
    <ComponentGroup Id="SSISConnectionsgrp" Directory="CONECTIONS">
      <Component Id="SSISConnections" Guid="{8DFFB861-18E4-4E08-8D4E-CAD99911E8A8}" >
        <File Id="Daimto.SSIS.Connection" Name="Daimto.SSIS.Connection.dll" DiskId="1" KeyPath="yes" Source="$(var.Daimto.SSIS.Connection.TargetPath)" />
      </Component>
    </ComponentGroup>

    <!-- copy everything to GAC (remember dlls must be strong name signed.) -->
    <ComponentGroup Id="CopyGAC" Directory="GAC">
      <Component Id="GACCOPYCurrent" Guid="{4D4E991E-77F5-4CCF-9048-B4C0260B43BD}" >
        <File Id="GACDaimto.SSIS.Tasks.Current" Name="Daimto.SSIS.Tasks.Current.dll"  KeyPath="yes" Source="$(var.Daimto.SSIS.Tasks.Current.TargetPath)" Assembly=".net"/>
      </Component>
      <Component Id="GACCOPYJson" Guid="{65E88F62-E73F-4851-9422-6C8D1DA5183F}" >
        <File Id="GACNewtonsoft.Json.dll" Name="Newtonsoft.Json.dll"  KeyPath="yes" Source="$(var.ProjectDir)Externals\Newtonsoft.Json.dll" Assembly=".net"/>
      </Component>
      <Component Id="GACCOPYConnection" Guid="{2DCABCEA-62CD-4F0D-BCAC-395A079AF27A}" >
        <File Id="GACDaimto.SSIS.Connection" Name="Daimto.SSIS.Connection.dll" KeyPath="yes" Source="$(var.Daimto.SSIS.Connection.TargetPath)" Assembly=".net"/>
      </Component>
    </ComponentGroup>
  </Fragment>
</Wix>

---

Related Topics