How to get sql server installation folder?

Question

How to get sql server installation folder?

darvinpappachan

SSC Veteran

Points: 235
More actions
July 23, 2008 at 7:36 am

#194316

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

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

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

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.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

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 More actions · Answer 3

You can find the syntax reference here:

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

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

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 5

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 More actions · Answer 6

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)