July 23, 2008 at 7:36 am
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
July 23, 2008 at 8:27 am
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]
August 1, 2008 at 12:02 pm
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]
August 1, 2008 at 12:07 pm
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]
August 1, 2008 at 12:29 pm
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]
August 1, 2008 at 12:31 pm
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]
May 20, 2020 at 8:06 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy