SQL Agent job to show SQL Server service details upon agent restart
This script is being offered for public use and as such is being offered as untested and unverified. Please use this script at your own risk, as I take NO responsibility for it's use elsewhere in environments that are NOT under my control. Redistribution or sale of the code, in whole or in part, is prohibited! Always ensure that you run such scripts in test prior to production and perform due diligence as to whether they meet yours, or your company needs! Description : To show sql and physical server details upon instance restart so that we have visibility of unexpected issues with servers and services. The Windows uptime.exe executable MUST be installed in the Windows\System32 area of the O/S for this to work, and xp_cmdshell must be configured for this to be run! The calling SQL Agent job Schedule Type must be set to ... "Start automatically when SQL Server Agent starts" so that the job runs upon instance startup. This script is invoked by the following named SQL Agent job in all instances ... "Report Server/Service Status upon system restart" This procedure uses the following extended stored procedures ... xp_servicecontrol (undocumented procedure -- Use with care!!) xp_regread xp_cmdshell
--############################################################################################################################
--
--This script is being offered for public use and as such is being offered as untested and unverified.
--Please use this script at your own risk, as I take NO responsibility for it's use elsewhere in environments
--that are NOT under my control.
--Redistribution or sale of the code, in whole or in part, is prohibited!
--Always ensure that you run such scripts in test prior to production and perform due diligence as to whether they meet yours,
--or your company needs!
--
--############################################################################################################################
--
-- Author:Haden Kingsland
--
-- Date:8th July 2011
--
-- Description :To show sql and physical server details upon instance restart
--so that we have visibility of unexpected issues with servers and
--services.
--The Windows uptime.exe executable MUST be installed in the
--Windows\System32 area of the O/S for this to work, and
--xp_cmdshell must be configured for this to be run!
--
--The calling SQL Agent job Schedule Type must be set to ...
--"Start automatically when SQL Server Agent starts" so that
--the job runs upon instance startup.
--
--This script is invoked by the following named SQL Agent job
--in all instances ...
--"Report Server/Service Status upon system restart"
--
--This procedure uses the following extended stored procedures ...
--
--xp_servicecontrol (undocumented procedure -- Use with care!!)
--xp_regread
--xp_cmdshell
--
--#######################################################################################################
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'SQLServer Agent Restarted',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'To show sql and physical server details upon instance restart
so that we have visibility of unexpected issues with servers and
services.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa',
@notify_email_operator_name=N'Haden Kingsland', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [To report all system statuses and send email upon agent restart] Script Date: 09/28/2011 11:43:57 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'To report all system statuses and send email upon agent restart',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
BEGIN
DECLARE@MailProfileName VARCHAR(50),
@ERR_MESSAGE varchar(200),
@ERR_NUM int,
@MESSAGE_BODY varchar(2000),
@MESSAGE_BODY2 varchar(1000),
@p_error_description varchar(300),
@job_name varchar(80),
@NewLine CHAR(2),
@Q CHAR(1),
@tableHTML VARCHAR(MAX),
@tableHTML1 VARCHAR(MAX),
@tableHTML2 VARCHAR(MAX),
@lineHTML VARCHAR(MAX),
@lineHTML1 VARCHAR(MAX),
@lineHTML2 VARCHAR(MAX),
@start_table VARCHAR(MAX),
@start_table1 VARCHAR(MAX),
@start_table2 VARCHAR(MAX),
@TR varchar(20),
@END varchar(30),
@END_TABLE varchar(30),
@ENDTAB varchar(20),
@recipient_listvarchar(1000),
@email varchar(100),
@value varchar(30),
@mailsubject varchar(200),
@propertyid int,
@userid bigint,
@property_value varchar(1000),
@output VARCHAR(1000),
@ChkInstanceName nvarchar(128), /*Stores SQL Instance Name*/@ChkSrvName nvarchar(128), /*Stores Server Name*/@TrueSrvName nvarchar(128), /*Stores instance name of MSSQLSERVER for default instances*/@SQLSrv NVARCHAR(128), /*Stores server name*/@PhysicalSrvName NVARCHAR(128), /*Stores physical name*/@FTS nvarchar(128), /*Stores Full Text Search Service name*/@RS nvarchar(128), /*Stores Reporting Service name*/@SQLAgent NVARCHAR(128), /*Stores SQL Agent Service name*/@OLAP nvarchar(128), /*Stores Analysis Service name*/@REGKEY NVARCHAR(128), /*Stores Registry Key information*/@PhysicalSrverName VARCHAR(128),
@ServerName VARCHAR(128),
@ServiceName VARCHAR(128),
@ServiceStatus VARCHAR(128),
@StatusDateTime DATETIME,
@XPCMDSH_ORIG_ON varchar(1),
@failsafe VARCHAR(100);
SET @NewLine = CHAR(13) + CHAR(10)
SET @Q = CHAR(39)
-- initialize variables (otherwise concat fails because the variable value is NULL)
set @lineHTML = ''''
set @lineHTML1 = ''''
set @lineHTML2 = ''''
set @tableHTML = ''''
set @tableHTML1 = ''''
set @tableHTML2 = ''''
set @start_table = ''''
set @start_table1 = ''''
set @start_table2 = ''''
SET @output = ''''
set @XPCMDSH_ORIG_ON = ''''
SET @tableHTML =
''<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Frameset// EN">'' +
''<html>'' +
''<LANG="EN">'' +
''<head>'' +
''<TITLE>SQL and Windows Server Status</TITLE>'' +
''</head>'' +
''<body>''
set @start_table = ''<font color="black" face="Tahoma" >'' +
''<CENTER>'' +
''<H1>Server Status</H1>'' +
''<table border="1">'' +
''<tr BGCOLOR="orange">'' +
-- list all table headers here
''<th BGCOLOR="#FFCC99" width="100%" colspan="3">SQL and Windows Server Status</th>''+''</tr>'' +
''<tr>'' +
''<th BGCOLOR="#FFCC99">Uptime Status</th>'' +
''</tr>''
--set @start_table1 =''<font color="green" face="Tahoma" >'' +
--''<H1>Checking users that have been switched to new LB ...</H1>'' +
--''<table border="1">'' +
--''<tr BGCOLOR="green">'' +
---- list all table headers here
--''<th width="100%" colspan="2">Usernames switched after the delete</th>'' +''</tr>'' +
--''<tr>'' +
--''<th>Email</th>'' +
--''<th>Value</th>'' +
--''</tr>''
set @start_table2 = ''<font color="black" face="Tahoma" >'' +
''<H1>Full SQL Server Install Details</H1>'' +
''<table border="1">'' +
''<tr BGCOLOR="#3399FF">'' +
-- list all table headers here
''<th BGCOLOR="#99CCFF" width="100%" colspan="5">SQL Component Details</th>'' +''</tr>'' +
''<tr>'' +
''<th BGCOLOR="#99CCFF">Physical Server Name</th>'' +
''<th BGCOLOR="#99CCFF">SQL Instance Name</th>'' +
''<th BGCOLOR="#99CCFF">SQL Server Services</th>'' +
''<th BGCOLOR="#99CCFF">Current Service Status</th>'' +
''<th BGCOLOR="#99CCFF">Date/Time of Status Checked</th>'' +
''</tr>''
SET @TR = ''</tr>''
SET @ENDTAB = ''</table></font>''
--SET @END = ''</table></font></body></html>''
SET @END_TABLE = ''</table></font>''
SET @END = ''</body></html>''
SET @mailsubject = ''The SQL Agent for .. '' + @@SERVERNAME + '' has been restarted at ... '' + CONVERT(VARCHAR(16),GETDATE(),121)
+ ''. If this is scheduled, please ignore this email, otherwise, check the SQL Server & Event Logs for details!''
--------------------------------------------------------------------------------------------------------------------
-- Check whether xp_cmdshell is turned off via Surface Area Configuration (2005) / Instance Facets (2008)
-- This is best practice !!!!! If it is already turned on, LEAVE it on !!
-- turn on advanced options
EXEC sp_configure ''show advanced options'', 1 reconfigure
RECONFIGURE
CREATE TABLE #advance_opt (name VARCHAR(20),min int, max int, conf int, run int)
INSERT #advance_opt
EXEC sp_configure ''xp_cmdshell'' -- this will show whether it is turned on or not
IF (select conf from #advance_opt) = 0 -- check if xp_cmdshell is turned on or off, if off, then turn it on
BEGIN
set @XPCMDSH_ORIG_ON = ''N'' -- make a note that it is NOT supposed to be on all the time
--turn on xp_cmdshell to allow operating system commands to be run
EXEC sp_configure ''xp_cmdshell'', 1 reconfigure
RECONFIGURE
END
ELSE
BEGIN
-- make a note that xp_cmdshell was already turned on, so not to turn it off later by mistake
set @XPCMDSH_ORIG_ON = ''Y''
END
-- drop the temporary table to tidy up after ourselves.
IF EXISTS (
select * from tempdb.sys.objects
where name like ''%advance_opt%''
)
BEGIN
drop table #advance_opt
END
--------------------------------------------------------------------------------------------------------------------
--
--create temporary server uptime table
--
CREATE TABLE #tbl_uptime
(
id INT IDENTITY(1, 1) ,
out_put VARCHAR(MAX)
)
SET NOCOUNT ON
DECLARE @crdate DATETIME ,
@hr VARCHAR(50) ,
@min VARCHAR(5)
SELECT @crdate = crdate
FROM sys.sysdatabases
WHERE name = ''tempdb''
SELECT @hr = ( DATEDIFF(mi, @crdate, GETDATE()) ) / 60
IF ( ( DATEDIFF(mi, @crdate, GETDATE()) ) / 60 ) = 0
SELECT @min = ( DATEDIFF(mi, @crdate, GETDATE()) )
ELSE
SELECT @min = ( DATEDIFF(mi, @crdate, GETDATE()) ) -
( ( DATEDIFF(mi,
@crdate,
GETDATE()) ) / 60 ) * 60
--
-- show how long the physical server has been up using the results from the "uptime" utility
--
--INSERT INTO #tbl_uptime
-- ( out_put
-- )
--VALUES ( ''SQL Server "'' + CONVERT(VARCHAR(30), SERVERPROPERTY(''SERVERNAME''))
-- + ''" has been online for the past '' + @hr + '' hours & '' + @min
-- + '' minutes'' -- out_put - varchar(max)
-- )
-- check to see if the SQL Agent is running
--IF NOT EXISTS ( SELECT 1
-- FROM master.dbo.sysprocesses
-- WHERE program_name = N''SQLAgent - Generic Refresher'' )
-- BEGIN
-- INSERT INTO #tbl_uptime
-- ( out_put
-- )
-- VALUES ( ''The SQL Server Engine is running but the SQL Server Agent <<IS NOT>> running on ''
-- + CONVERT(VARCHAR(30), SERVERPROPERTY(''SERVERNAME''))
-- )
-- END
--ELSE
-- BEGIN
--INSERT INTO #tbl_uptime
-- ( out_put
-- )
-- VALUES ( ''SQL Server and SQL Server Agent both are running for ... ''
-- + CONVERT(VARCHAR(30), SERVERPROPERTY(''SERVERNAME''))
-- )
--END
IF SERVERPROPERTY(''ISCLUSTERED'') = 1
BEGIN
INSERT INTO #tbl_uptime
( out_put
)
VALUES ( ''SQL Server '' --+ CONVERT(VARCHAR(30), SERVERPROPERTY(''SERVERNAME''))
+ '' has been online for the past '' + @hr + '' hours & '' + @min
+ '' minutes for cluster "'' + CONVERT(VARCHAR(30), SERVERPROPERTY(''SERVERNAME'')) + ''"'' -- out_put - varchar(max)
)
INSERT INTO #tbl_uptime
( out_put
)
VALUES ( ''SQL Server is currently running on the following Cluster node ...''
+ CONVERT(VARCHAR(30), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS''))
)
--INSERT INTO #tbl_uptime
-- ( out_put
-- )
--VALUES ( ''Both SQL Server & SQL Server Agent are running on the cluster ... ''
-- + CONVERT(VARCHAR(30), SERVERPROPERTY(''SERVERNAME''))
-- )
END
ELSE
BEGIN
--INSERT INTO #tbl_uptime
-- ( out_put
-- )
-- VALUES ( ''Both SQL Server & SQL Server Agent are running on ... ''
-- + CONVERT(VARCHAR(30), SERVERPROPERTY(''SERVERNAME''))
-- )
INSERT INTO #tbl_uptime
( out_put
)
VALUES ( ''SQL Server '' --+ CONVERT(VARCHAR(30), SERVERPROPERTY(''SERVERNAME''))
+ '' has been online for the past '' + @hr + '' hours & '' + @min
+ '' minutes for server "'' + CONVERT(VARCHAR(30), SERVERPROPERTY(''SERVERNAME'')) + ''"'' -- out_put - varchar(max)
)
END
--SELECT SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')
--SELECT SERVERPROPERTY(''SERVERNAME'')
--SELECT SERVERPROPERTY(''MACHINENAME'')
--SELECT SERVERPROPERTY(''ISCLUSTERED'')
--execute the uptime.exe file pointing it at various servers
INSERT #tbl_uptime
EXEC master..xp_cmdshell ''uptime''
--SELECT *
--FROM #tbl_uptime
--WHERE out_put IS NOT NULL
SELECT @MailProfileName = name
FROM msdb.dbo.sysmail_profile WITH (NOLOCK)
PRINT @MailProfileName
BEGIN TRY
DECLARE build_report CURSOR
FOR
SELECT out_put
FROM #tbl_uptime
WHERE out_put IS NOT NULL
-- Open the cursor.
OPEN build_report;
-- Loop through the update_stats cursor.
FETCH NEXT
FROM build_report
INTO @output
--WHILE @@FETCH_STATUS = 0
--BEGIN
PRINT ''Fetch Status is ... '' + CONVERT(VARCHAR(10),@@FETCH_STATUS)
WHILE @@FETCH_STATUS <> -1 -- Stop when the FETCH statement failed or the row is beyond the result set
BEGIN
IF @@FETCH_STATUS = 0 -- to ignore -2 status "The row fetched is missing"
BEGIN
set @lineHTML = @lineHTML +
''<tr>'' +
''<td>'' + cast(@output as varchar(400)) + ''</td>''
--''<td>'' + '' '' + cast(@userid as nvarchar(30)) + ''</td>'' +
--''<td>'' + '' '' + cast(@property_value as nvarchar(30)) + ''</td>''
+ ''</tr>''
print @lineHTML
END
FETCH NEXT
FROM build_report
INTO @output
END
-- Close and deallocate the cursor.
CLOSE build_report;
DEALLOCATE build_report;
-- get all installed features, status and time checked here ...
-- Derived from here ...
-- http://pawansingh1431.blogspot.com/2011/02/check-what-are-sql-components-installed.html
CREATE TABLE #RegResult
(
ResultValue NVARCHAR(4)
)
CREATE TABLE #ServicesServiceStatus /*Create temp tables*/(
RowID INT IDENTITY(1,1)
,ServerName VARCHAR(60)
,ServiceName VARCHAR(60)
,ServiceStatus varchar(60)
,StatusDateTime DATETIME DEFAULT (GETDATE())
,PhysicalSrverName VARCHAR(60)
)
IF SERVERPROPERTY(''IsClustered'') = 1
BEGIN
SET @PhysicalSrvName = CAST(SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS VARCHAR(128))
END
ELSE
BEGIN
SET @PhysicalSrvName = CAST(SERVERPROPERTY(''MachineName'') AS VARCHAR(128))
END
SET @ChkSrvName = CAST(SERVERPROPERTY(''INSTANCENAME'') AS VARCHAR(128))
SET @ChkInstanceName = @@serverName
IF @ChkSrvName IS NULL /*Detect default or named instance*/
BEGIN
SET @TrueSrvName = ''MSSQLSERVER (DEFAULT)''
SELECT @OLAP = ''MSSQLServerOLAPService'' /*Setting up proper service name*/ SELECT @FTS = ''MSFTESQL''
SELECT @RS = ''ReportServer''
SELECT @SQLAgent = ''SQLSERVERAGENT''
SELECT @SQLSrv = ''MSSQLSERVER''
END
ELSE
BEGIN
SET @TrueSrvName = CAST(SERVERPROPERTY(''INSTANCENAME'') AS VARCHAR(128))
SET @SQLSrv = ''$'' + @ChkSrvName
SELECT @OLAP = ''MSOLAP'' + @SQLSrv /*Setting up proper service name*/ SELECT @FTS = ''MSFTESQL'' + @SQLSrv
SELECT @RS = ''ReportServer'' + @SQLSrv
SELECT @SQLAgent = ''SQLAgent'' + @SQLSrv
SELECT @SQLSrv = ''MSSQL'' + @SQLSrv
END
/* ---------------------------------- SQL Server Service Section ----------------------------------------------*/SET @REGKEY = ''System\CurrentControlSet\Services\'' + @SQLSrv
INSERT #RegResult
( ResultValue
)
EXEC master.sys.xp_regread @rootkey = ''HKEY_LOCAL_MACHINE'',
@key = @REGKEY
IF ( SELECT ResultValue
FROM #RegResult
) = 1
BEGIN
INSERT #ServicesServiceStatus
( ServiceStatus
) /*Detecting status of SQL Sever service*/ EXEC xp_servicecontrol N''QUERYSTATE'', @SQLSrv
UPDATE #ServicesServiceStatus
SET ServiceName = ''MS SQL Server Service''
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET ServerName = @TrueSrvName
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET PhysicalSrverName = @PhysicalSrvName
WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus
( ServiceStatus )
VALUES ( ''NOT INSTALLED'' )
UPDATE #ServicesServiceStatus
SET ServiceName = ''MS SQL Server Service''
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET ServerName = @TrueSrvName
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET PhysicalSrverName = @PhysicalSrvName
WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ---------------------------------- SQL Server Agent Service Section -----------------------------------------*/SET @REGKEY = ''System\CurrentControlSet\Services\'' + @SQLAgent
INSERT #RegResult
( ResultValue
)
EXEC master.sys.xp_regread @rootkey = ''HKEY_LOCAL_MACHINE'',
@key = @REGKEY
IF ( SELECT ResultValue
FROM #RegResult
) = 1
BEGIN
INSERT #ServicesServiceStatus
( ServiceStatus
) /*Detecting status of SQL Agent service*/ EXEC xp_servicecontrol N''QUERYSTATE'', @SQLAgent
UPDATE #ServicesServiceStatus
SET ServiceName = ''SQL Server Agent Service''
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET ServerName = @SQLAgent
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET PhysicalSrverName = @PhysicalSrvName
WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus
( ServiceStatus )
VALUES ( ''NOT INSTALLED'' )
UPDATE #ServicesServiceStatus
SET ServiceName = ''SQL Server Agent Service''
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET ServerName = @SQLAgent
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET PhysicalSrverName = @PhysicalSrvName
WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ---------------------------------- SQL Browser Service Section ----------------------------------------------*/SET @REGKEY = ''System\CurrentControlSet\Services\SQLBrowser''
INSERT #RegResult
( ResultValue
)
EXEC master.sys.xp_regread @rootkey = ''HKEY_LOCAL_MACHINE'',
@key = @REGKEY
IF ( SELECT ResultValue
FROM #RegResult
) = 1
BEGIN
INSERT #ServicesServiceStatus
( ServiceStatus
) /*Detecting status of SQL Browser Service*/ EXEC master.dbo.xp_servicecontrol N''QUERYSTATE'', N''sqlbrowser''
UPDATE #ServicesServiceStatus
SET ServiceName = ''SQL Browser Service - Instance Independent''
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET ServerName = @TrueSrvName
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET PhysicalSrverName = @PhysicalSrvName
WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus
( ServiceStatus )
VALUES ( ''NOT INSTALLED'' )
UPDATE #ServicesServiceStatus
SET ServiceName = ''SQL Browser Service - Instance Independent''
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET ServerName = @TrueSrvName
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET PhysicalSrverName = @PhysicalSrvName
WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ---------------------------------- Integration Service Section ----------------------------------------------*/SET @REGKEY = ''System\CurrentControlSet\Services\MsDtsServer''
INSERT #RegResult
( ResultValue
)
EXEC master.sys.xp_regread @rootkey = ''HKEY_LOCAL_MACHINE'',
@key = @REGKEY
IF ( SELECT ResultValue
FROM #RegResult
) = 1
BEGIN
INSERT #ServicesServiceStatus
( ServiceStatus
) /*Detecting status of Intergration Service*/ EXEC master.dbo.xp_servicecontrol N''QUERYSTATE'',
N''MsDtsServer''
UPDATE #ServicesServiceStatus
SET ServiceName = ''Intergration Service - Instance Independent''
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET ServerName = @TrueSrvName
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET PhysicalSrverName = @PhysicalSrvName
WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus
( ServiceStatus )
VALUES ( ''NOT INSTALLED'' )
UPDATE #ServicesServiceStatus
SET ServiceName = ''Intergration Service - Instance Independent''
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET ServerName = @TrueSrvName
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET PhysicalSrverName = @PhysicalSrvName
WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ---------------------------------- Reporting Service Section ------------------------------------------------*/SET @REGKEY = ''System\CurrentControlSet\Services\'' + @RS
INSERT #RegResult
( ResultValue
)
EXEC master.sys.xp_regread @rootkey = ''HKEY_LOCAL_MACHINE'',
@key = @REGKEY
IF ( SELECT ResultValue
FROM #RegResult
) = 1
BEGIN
INSERT #ServicesServiceStatus
( ServiceStatus
) /*Detecting status of Reporting service*/ EXEC master.dbo.xp_servicecontrol N''QUERYSTATE'', @RS
UPDATE #ServicesServiceStatus
SET ServiceName = ''Reporting Service''
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET ServerName = @TrueSrvName
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET PhysicalSrverName = @PhysicalSrvName
WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus
( ServiceStatus )
VALUES ( ''NOT INSTALLED'' )
UPDATE #ServicesServiceStatus
SET ServiceName = ''Reporting Service''
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET ServerName = @TrueSrvName
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET PhysicalSrverName = @PhysicalSrvName
WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ---------------------------------- Analysis Service Section -------------------------------------------------*/IF @ChkSrvName IS NULL /*Detect default or named instance*/
BEGIN
SET @OLAP = ''MSSQLServerOLAPService''
END
ELSE
BEGIN
SET @OLAP = ''MSOLAP'' + ''$'' + @ChkSrvName
SET @REGKEY = ''System\CurrentControlSet\Services\'' + @OLAP
END
INSERT #RegResult
( ResultValue
)
EXEC master.sys.xp_regread @rootkey = ''HKEY_LOCAL_MACHINE'',
@key = @REGKEY
IF ( SELECT ResultValue
FROM #RegResult
) = 1
BEGIN
INSERT #ServicesServiceStatus
( ServiceStatus
) /*Detecting status of Analysis service*/ EXEC master.dbo.xp_servicecontrol N''QUERYSTATE'', @OLAP
UPDATE #ServicesServiceStatus
SET ServiceName = ''Analysis Services''
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET ServerName = @TrueSrvName
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET PhysicalSrverName = @PhysicalSrvName
WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus
( ServiceStatus )
VALUES ( ''NOT INSTALLED'' )
UPDATE #ServicesServiceStatus
SET ServiceName = ''Analysis Services''
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET ServerName = @TrueSrvName
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET PhysicalSrverName = @PhysicalSrvName
WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ---------------------------------- Full Text Search Service Section -----------------------------------------*/SET @REGKEY = ''System\CurrentControlSet\Services\'' + @FTS
INSERT #RegResult
( ResultValue
)
EXEC master.sys.xp_regread @rootkey = ''HKEY_LOCAL_MACHINE'',
@key = @REGKEY
IF ( SELECT ResultValue
FROM #RegResult
) = 1
BEGIN
INSERT #ServicesServiceStatus
( ServiceStatus
) /*Detecting status of Full Text Search service*/ EXEC master.dbo.xp_servicecontrol N''QUERYSTATE'', @FTS
UPDATE #ServicesServiceStatus
SET ServiceName = ''Full Text Search Service''
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET ServerName = @TrueSrvName
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET PhysicalSrverName = @PhysicalSrvName
WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus
( ServiceStatus )
VALUES ( ''NOT INSTALLED'' )
UPDATE #ServicesServiceStatus
SET ServiceName = ''Full Text Search Service''
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET ServerName = @TrueSrvName
WHERE RowID = @@identity
UPDATE #ServicesServiceStatus
SET PhysicalSrverName = @PhysicalSrvName
WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ---------------------------------- End of Server Component Checks -----------------------------------------*/--
-- Uncomment this section to use for debug purposes if you are getting no date
--
--SELECT PhysicalSrverName ,
--ServerName ,
--ServiceName ,
--ServiceStatus ,
--StatusDateTime
--FROM #ServicesServiceStatus
--
-- Declare the cursor to read the results from the above Server Component Checks and format
-- them for HTML.
--
DECLARE installed_features CURSOR
FOR
SELECT PhysicalSrverName ,
ServerName ,
ServiceName ,
ServiceStatus ,
StatusDateTime
FROM #ServicesServiceStatus
-- Open the cursor.
OPEN installed_features;
-- Loop through the update_stats cursor.
FETCH NEXT
FROM installed_features
INTO @PhysicalSrverName ,
@ServerName ,
@ServiceName ,
@ServiceStatus ,
@StatusDateTime
--WHILE @@FETCH_STATUS = 0
--BEGIN
PRINT ''Fetch Status is ... '' + CONVERT(VARCHAR(10),@@FETCH_STATUS)
WHILE @@FETCH_STATUS <> -1 -- Stop when the FETCH statement failed or the row is beyond the result set
BEGIN
IF @@FETCH_STATUS = 0 -- to ignore -2 status "The row fetched is missing"
BEGIN
set @lineHTML2 = @lineHTML2 +
''<tr>'' +
''<td>'' + cast(RTRIM(LTRIM(@PhysicalSrverName)) as varchar(60)) + ''</td>'' +
''<td>'' + '' '' + cast(@ServerName as nvarchar(50)) + ''</td>'' +
''<td>'' + '' '' + cast(@ServiceName as nvarchar(50)) + ''</td>'' +
''<td>'' + '' '' + cast(@ServiceStatus as nvarchar(30)) + ''</td>'' +
''<td>'' + '' '' + convert(VARCHAR(16),@StatusDateTime,121) + ''</td>''
+ ''</tr>''
print @lineHTML2
END
FETCH NEXT
FROM installed_features
INTO @PhysicalSrverName, @ServerName, @ServiceName,
@ServiceStatus, @StatusDateTime
END
-- Close and deallocate the cursor.
CLOSE installed_features;
DEALLOCATE installed_features;
-- ###############################################
-- build up HTML statement
set @tableHTML = @tableHTML + @start_table + @lineHTML + @END_TABLE +
@start_table2 + @lineHTML2 + @END_TABLE + @END
-- as the <td> tags are auto-generated, I need to replace then with a new <td>
-- tag including all the required formatting.
set @tableHTML = REPLACE( @tableHTML, ''<td>'', ''<td BGCOLOR=#CCCCFF>'' );
print @tableHTML
-- FOR DEBUG PURPOSES! UNCOMMENT IF NEEDED
--
--DECLARE @failsafe VARCHAR(100)
--DECLARE @recipient_list VARCHAR(100)
SELECT @recipient_list = email_address
FROM msdb..sysoperators
WHERE name = ''LOR_SQL_Admin_Alerts'' -- Name of main required operator
IF @recipient_list IS NULL
BEGIN
EXECUTE master.dbo.xp_instance_regread N''HKEY_LOCAL_MACHINE'',
N''SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'',
N''AlertFailSafeOperator'',
@failsafe OUTPUT,
N''no_output''
SELECT @recipient_list = email_address
FROM msdb..sysoperators
WHERE name = @failsafe
END
PRINT @recipient_list
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @MailProfileName,
--@recipients = ''hkingsland@laingorourke.com'',
@recipients = @recipient_list,
@body_format = ''HTML'',
@importance = ''HIGH'',
@body = @tableHTML,
@subject = @mailsubject
END TRY
BEGIN CATCH
SELECT @ERR_MESSAGE = ERROR_MESSAGE(), @ERR_NUM = ERROR_NUMBER();
SET @MESSAGE_BODY=''Error running the ''''Server & Service Status upon system restart'''' script ''
+ ''. Error Code is ... '' + RTRIM(CONVERT(CHAR(10),@ERR_NUM)) + '' Error Message is ... '' + @ERR_MESSAGE
SET @MESSAGE_BODY2=''Failure of Report Server & Service Status upon system restart Check script within the ''
+ LTRIM(RTRIM(cast(@@SERVERNAME as VARCHAR(30)))) + '' instance''
SET @MESSAGE_BODY = @MESSAGE_BODY -- + @MESSAGE_BODY3
EXEC msdb.dbo.sp_notify_operator
@profile_name = @MailProfileName,
@name=N''LOR_SQL_Admin_Alerts'',
@subject = @MESSAGE_BODY2,
@body= @MESSAGE_BODY
-- If for some reason this script fails, check for any temporary
-- tables created during the run and drop them for next time.
IF EXISTS ( SELECT *
FROM tempdb.sys.objects
WHERE name = ''#tbl_uptime'' )
BEGIN
DROP TABLE #tbl_uptime
END
IF EXISTS ( SELECT *
FROM tempdb.sys.objects
WHERE name = ''#ServicesServiceStatus'' )
BEGIN
DROP TABLE #ServicesServiceStatus
END
IF EXISTS ( SELECT *
FROM tempdb.sys.objects
WHERE name = ''#RegResult'' )
BEGIN
DROP TABLE #RegResult
END
END CATCH
-----------------------------------------------------------------------------------------------------------------------
-- turn off advanced options
IF @XPCMDSH_ORIG_ON = ''N'' -- if xp_cmdshell was NOT originally turned on, then turn it off
BEGIN
-- turn off xp_cmdshell to dis-allow operating system commands to be run
EXEC sp_configure ''xp_cmdshell'', 0 reconfigure
RECONFIGURE
EXEC sp_configure ''show advanced options'', 0 reconfigure
RECONFIGURE
END
-----------------------------------------------------------------------------------------------------------------------
--
-- Cleanup after ourselves!!
--
DROP TABLE #tbl_uptime
DROP TABLE #ServicesServiceStatus
DROP TABLE #RegResult
END',
@database_name=N'master',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Report on server status upon system and agent restart',
@enabled=1,
@freq_type=64,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20110708,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO 
