I'm doing some installations at the moment for Monitoring agent and I needed a quick way of deriving
the Error Log Path and the SQL Server Home Path .
My first problem was to derive the t-sql to gain the information for 1 instance .
The second problem was to use a framework where I would be able to iterate through every instance I manage
The t-sql to derive the error log file was straightforward
----------------CODE START------------------------
select ServerProperty('ErrorLogFileName')
----------------CODE END----------------------------
The code for the SQL Server path was an undocumented method . By documented it means Microsoft do not support it.
In the master database, there is an extended stored procedure called “xp_instance_regread” that allows you to derive
values from the registry. There is no information in BOL. If its undocumented, this means that they are unsupported by
Microsoft and you have to google to figure out how to use them.
The code below returns the SQL Server home for a given SQL Server instance
----------------CODE START------------------------
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
----------------CODE END----------------------------
The second problem involved getting this information form multiple sql server instances on the same network. As I've
been getting into Powershell quote a bit recently , I thought I'd attempt it in Powershell. The results were useful.
The script reads the instances from "C:\MyInstancesPROD_2005.txt" and writes them out to "C:\projects\test.txt"
--------------------CODE START-------------------------
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, SERVERPROPERTY('ProductLevel') as
SP,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
}
--------------------CODE END-------------------------
Comments