SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

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

28 August,2008 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer