How to get sql server installation folder?

  • darvinpappachan

    SSC Veteran

    Points: 235

    I am a VB programmer.

    I need to get the sql server installation folder in server.

    I want to keep a file in that folder for verification.

    By default that may be "C:\Program Files\Microsoft SQL Server\MSSQL".

    If a stored procedure or function can return the installation path it will be very useful for me in my current project.

    Please help me

    thanks

  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    There are a couple of ways to do this. Just using standard TSQL, you can use XP_REGREAD to read the registry key for the instance root key:

    HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL

    Then you have to read the SQLPath key from :

    HKLM\Software\Microsoft\Microsoft SQL Server\[InstancePath]\MSSQLServer\Setup

    If you wanted to do it in SQL CLR, it is possibly, but requires External_Access to do so since you are reading the Registry.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    Jonathan Kehayias (7/23/2008)


    There are a couple of ways to do this. Just using standard TSQL, you can use XP_REGREAD to read the registry key for the instance root key:

    HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL

    Then you have to read the SQLPath key from :

    HKLM\Software\Microsoft\Microsoft SQL Server\[InstancePath]\MSSQLServer\Setup

    If you wanted to do it in SQL CLR, it is possibly, but requires External_Access to do so since you are reading the Registry.

    can you post an XP_REGREAD code sample, if possible?

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    You can find the syntax reference here:

    http://www.mssqlcity.com/FAQ/Devel/xp_regread.htm

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    For this particular question, here is the example code.

    DECLARE @InstanceName varchar(100),

    @InstanceLocation varchar(100),

    @InstancePath varchar(100)

    SELECT @InstanceName = convert(varchar, ServerProperty('InstanceName'))

    EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

    @key='Software\Microsoft\Microsoft SQL Server\Instance Names\SQL',

    @value_name=@InstanceName,

    @value=@InstanceLocation OUTPUT

    SELECT @InstanceLocation = 'Software\Microsoft\Microsoft SQL Server\'+@InstanceLocation+'\Setup'

    EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

    @key=@InstanceLocation,

    @value_name='SQLPath',

    @value=@InstancePath OUTPUT

    SELECT @InstancePath

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    Jonathan Kehayias (8/1/2008)


    For this particular question, here is the example code.

    DECLARE @InstanceName varchar(100),

    @InstanceLocation varchar(100),

    @InstancePath varchar(100)

    SELECT @InstanceName = convert(varchar, ServerProperty('InstanceName'))

    EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

    @key='Software\Microsoft\Microsoft SQL Server\Instance Names\SQL',

    @value_name=@InstanceName,

    @value=@InstanceLocation OUTPUT

    SELECT @InstanceLocation = 'Software\Microsoft\Microsoft SQL Server\'+@InstanceLocation+'\Setup'

    EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

    @key=@InstanceLocation,

    @value_name='SQLPath',

    @value=@InstancePath OUTPUT

    SELECT @InstancePath

    Great, thank you!

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • zionassedo

    Newbie

    Points: 2

    Hi,

    A small improvement to this script may be :

    SELECT @InstanceName = convert(varchar, ISNULL(ServerProperty('InstanceName'),'MSSQLSERVER'))

    (Since the return value may be null when default instance name is used)

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply