SQL Server xp_regread with T-SQL for SQL Error Log Path and Home Path

Finding the SQL Error Log Path and the SQL Server Home Path with T-SQL is straightforward. As a DBA  I want to spend as little time as possible completing tasks , that I can otherwise script out and automate.

An example is The SQL Server Tivoli Monitoring agent which requires the  SQL Error Log Path and the SQL Server Home Path ,  for installation and configuration purposes.

My first problem was to derive the t-sql  to gain the information for one sql server instance .
The second problem was to use a scripting framework to iterate through every sql server instance on the database server inventory 

The t-sql to derive the error log file was straightforward

select ServerProperty('ErrorLogFileName')

The code for the SQL Server path is an "undocumented" method . "Undocumented"  means Microsoft do not support it.  "Undocumented"  also means you have to figure out to how to use it without BOL.

The extended stored procedure  “xp_instance_regread” , in the master database,   derives values from the registry. 

The code below returns the SQL Server home for a given SQL Server instance

declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot
OUTPUT
print @SmoRoot

The second problem involves getting this information from multiple sql server instances on the same network. Powershell is great for this purpose. The results were useful.

The script reads the instances from "C:\MyInstancesPROD_2005.txt"   and writes them out to "C:\projects\test.txt"

foreach ($svr in get-content "C:\InstancesPROD_2005.txt"){
    $dt = new-object "System.Data.DataTable"
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=master;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT
SELECT @@SERVERNAME AS ServerName, SERVERPROPERTY('ProductVersion') AS Version, 'itmsql' AS LOGIN,SERVERPROPERTY('ErrorLogFileName') AS ErrorLog,@SmoRoot AS SQLPath
"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    $dt | Format-Table -autosize  
    $dt | out-File "C:\projects\test.txt" -append -encoding ASCII

    
}

 Related Posts

SQL Server – Find the maintenance plan log file path

Powershell and Disaster Recovery preparation – T-SQL Tuesday