For a script that I am working on, I need to access System Properties such as the base folder of the errorlog.
SQL Server Management Studio ( SSMS )
Of course the information is available via SSMS, just click on the Server’s Property and you will see something like this…
- We can that “Root Directory” is “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL“
Registry – CPE
Knowing the value that we are searching for took to the registry and searched for “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL“.
Ended up here…
Registry – Setup
Most of the most important information is saved in the Setup branch.
Registry – MSSQLServer
Btw, the MSSQLServer path is very important, as well.
Let us read ErrorDumpDir from the CPE branch
DECLARE @rootKey NVARCHAR(500) DECLARE @path NVARCHAR(500) DECLARE @name NVARCHAR(500) DECLARE @value NVARCHAR(1000) set @rootKey = N'HKEY_LOCAL_MACHINE' set @path = N'SOFTWARE\Microsoft\MSSQLServer\CPE' set @name = N'ErrorDumpDir' EXEC master..xp_instance_regread @rootkey = @rootKey , @path = @path , @name = @name , @value = @value output PRINT 'Error Dump Dir :' + isNull(@value, '')
Please keep in mind that xp_instance_regread is an extended Stored Procedure.
As there could be a few SQL Server Instances on a box, Microsoft abstracted things a bit and xp_instance_regread is able to read information for the specific Instance it is ran on.
The more generic SP that does not abstract for the specific instance is xp_RegRead.