Problem
In my last tip Checking SQL Service Status – An Evolution (part I), I spoke about the important need to be able to monitor and keep track of the status of all necessary SQL Server Services. I also showed you one method using t-sql, xp_cmdshell and an OS component called SC.exe (service control). Sometimes, using OS components or even xp_cmdshell is not feasible for all sorts of reasons, including security, or limited DBA access. In this tip we look at other techniques to get the same information.
Solution
Here we can revisit the undocumented, but more generally known, stored procedure call xp_servicecontrol. First, the caveat. I wanted to make sure that everyone knows that xp_servicecontrol is an undocumented XP, which means it is not supported and Microsoft can decide to change the code or remove it anytime. However, it has been a part of SQL Server since version 7.0 through version 2008. It is actually recommended by MS, as an alternative you use xp_cmdshell ‘SC query <servicename>’ . In that case, you can see Part I of my SQL Service Series. Nonetheless, xp_servicecontrol is a handy one to have in your toolkit. Despite the warnings, and the caveat, we know that the code is NOT going to change, retroactively, in previous versions.
I will show you how we can use this extended stored procedure to get the status of all the SQL Services.
Method 2 – Using xp_servicecontrol
You can use this extended stored procedure to monitor and query status, as well as control the service by starting and stopping it. There are two general parameters that take the Action desired, and the Service Name. Here are the actions and syntax that you can specify:
| start | Starts the service if it is not running. If the service is already in running state, an error is raised. |
| stop | Stops the service if it is running. If the service is not running, an error is raised. |
| pause | Pauses a running service. An error is raised if the service is not running. Also, not all services support pausing. |
| continue | Continues running a paused service. An error is raised if the serviced is not in paused state. |
| querystate | Returns the current state of the service. |
In this case, we will have to know all of the exact Service Names of each service, which is different from the Display Name. The Service Name is the parameter that we need to pass to the @ServiceName. This of course can be found out by going to the Services Management Console in the control panel. The quickest way to invoke it, is to go to the Start Menu Button –> Run, type “services.msc” and hit <Enter>.
Therefore, for example, we see that the main SQL Server Service Name is “MSSQLServer”, and we would query its status using the following syntax:
<span style="color: blue;">EXEC </span><span style="color: darkred;">xp_servicecontrol </span><span style="color: red;">N'querystate'</span><span style="color: gray;">,</span><span style="color: red;">N'MSSQLServer' </span>With the script I will be discussing in this tip, I already have all the service names of the common SQL Server services included for you, so you don’t need to look them up.
How the Script Works
This script will determine the status of SQL server service(s) if they are running or stopped, and also determine if the service is installed or not. This script has been adapted and modified to account for the correct Integration Services service name, depending on whether its SQL Server version 2005, MsDtsServer, or SQL Server version 2008, MsDtsServer100. (The original source can be found here.) We also add some email notification, using DB Mail , so you can set up a scheduled job to run each morning before you get in.
<span style="color: green;">--Check SQL Server Services Status</span><span style="color: blue;">SET NOCOUNT ON
CREATE TABLE </span><span style="color: black;">tempdb.dbo.RegResult
</span><span style="color: gray;">(
</span><span style="color: black;">ResultValue </span><span style="color: blue;">NVARCHAR</span><span style="color: gray;">(</span><span style="color: black;">4</span><span style="color: gray;">)
)
</span><span style="color: blue;">CREATE TABLE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus
</span><span style="color: gray;">(
</span><span style="color: black;">RowID </span><span style="color: blue;">INT </span><span style="color: #434343;">IDENTITY</span><span style="color: gray;">(</span><span style="color: black;">1</span><span style="color: gray;">,</span><span style="color: black;">1</span><span style="color: gray;">)
,</span><span style="color: black;">ServerName </span><span style="color: blue;">NVARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">)
,</span><span style="color: black;">ServiceName </span><span style="color: blue;">NVARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">)
,</span><span style="color: black;">ServiceStatus </span><span style="color: blue;">VARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">)
,</span><span style="color: black;">StatusDateTime </span><span style="color: blue;">DATETIME DEFAULT </span><span style="color: gray;">(</span><span style="color: magenta;">GETDATE</span><span style="color: gray;">())
,</span><span style="color: black;">PhysicalSrverName </span><span style="color: blue;">NVARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">)
)
</span><span style="color: blue;">DECLARE
</span><span style="color: #434343;">@ChkInstanceName </span><span style="color: blue;">NVARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">) </span><span style="color: green;">/*Stores SQL Instance Name*/
</span><span style="color: gray;">,</span><span style="color: #434343;">@ChkSrvName </span><span style="color: blue;">NVARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">) </span><span style="color: green;">/*Stores Server Name*/
</span><span style="color: gray;">,</span><span style="color: #434343;">@TrueSrvName </span><span style="color: blue;">NVARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">) </span><span style="color: green;">/*Stores where code name needed */
</span><span style="color: gray;">,</span><span style="color: #434343;">@SQLSrv </span><span style="color: blue;">NVARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">) </span><span style="color: green;">/*Stores server name*/
</span><span style="color: gray;">,</span><span style="color: #434343;">@PhysicalSrvName </span><span style="color: blue;">NVARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">) </span><span style="color: green;">/*Stores physical name*/
</span><span style="color: gray;">,</span><span style="color: #434343;">@DTS </span><span style="color: blue;">NVARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">) </span><span style="color: green;">/*Store SSIS Service Name */
</span><span style="color: gray;">,</span><span style="color: #434343;">@FTS </span><span style="color: blue;">NVARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">) </span><span style="color: green;">/*Stores Full Text Search Service name*/
</span><span style="color: gray;">,</span><span style="color: #434343;">@RS </span><span style="color: blue;">NVARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">) </span><span style="color: green;">/*Stores Reporting Service name*/
</span><span style="color: gray;">,</span><span style="color: #434343;">@SQLAgent </span><span style="color: blue;">NVARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">) </span><span style="color: green;">/*Stores SQL Agent Service name*/
</span><span style="color: gray;">,</span><span style="color: #434343;">@OLAP </span><span style="color: blue;">NVARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">) </span><span style="color: green;">/*Stores Analysis Service name*/
</span><span style="color: gray;">,</span><span style="color: #434343;">@REGKEY </span><span style="color: blue;">NVARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">) </span><span style="color: green;">/*Stores Registry Key information*/
</span><span style="color: blue;">SET </span><span style="color: #434343;">@PhysicalSrvName </span><span style="color: blue;">= </span><span style="color: magenta;">CAST</span><span style="color: gray;">(</span><span style="color: magenta;">SERVERPROPERTY</span><span style="color: gray;">(</span><span style="color: red;">'MachineName'</span><span style="color: gray;">) </span><span style="color: blue;">AS VARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">))
</span><span style="color: blue;">SET </span><span style="color: #434343;">@ChkSrvName </span><span style="color: blue;">= </span><span style="color: magenta;">CAST</span><span style="color: gray;">(</span><span style="color: magenta;">SERVERPROPERTY</span><span style="color: gray;">(</span><span style="color: red;">'INSTANCENAME'</span><span style="color: gray;">) </span><span style="color: blue;">AS VARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">))
</span><span style="color: blue;">SET </span><span style="color: #434343;">@ChkInstanceName </span><span style="color: blue;">= </span><span style="color: #434343;">@@serverName
</span><span style="color: blue;">IF </span><span style="color: #434343;">@ChkSrvName </span><span style="color: blue;">IS </span><span style="color: gray;">NULL </span><span style="color: green;">/*Detect default or named instance*/
</span><span style="color: blue;">BEGIN
SET </span><span style="color: #434343;">@TrueSrvName </span><span style="color: blue;">= </span><span style="color: red;">'MSSQLSERVER'
</span><span style="color: blue;">SELECT </span><span style="color: #434343;">@OLAP </span><span style="color: blue;">= </span><span style="color: red;">'MSSQLServerOLAPService' </span><span style="color: green;">/*Setting up proper service name*/
</span><span style="color: blue;">SELECT </span><span style="color: #434343;">@FTS </span><span style="color: blue;">= </span><span style="color: red;">'MSFTESQL'
</span><span style="color: blue;">SELECT </span><span style="color: #434343;">@RS </span><span style="color: blue;">= </span><span style="color: red;">'ReportServer'
</span><span style="color: blue;">SELECT </span><span style="color: #434343;">@SQLAgent </span><span style="color: blue;">= </span><span style="color: red;">'SQLSERVERAGENT'
</span><span style="color: blue;">SELECT </span><span style="color: #434343;">@SQLSrv </span><span style="color: blue;">= </span><span style="color: red;">'MSSQLSERVER'
</span><span style="color: blue;">END
ELSE
BEGIN
SET </span><span style="color: #434343;">@TrueSrvName </span><span style="color: blue;">= </span><span style="color: magenta;">CAST</span><span style="color: gray;">(</span><span style="color: magenta;">SERVERPROPERTY</span><span style="color: gray;">(</span><span style="color: red;">'INSTANCENAME'</span><span style="color: gray;">) </span><span style="color: blue;">AS VARCHAR</span><span style="color: gray;">(</span><span style="color: black;">128</span><span style="color: gray;">))
</span><span style="color: blue;">SET </span><span style="color: #434343;">@SQLSrv </span><span style="color: blue;">= </span><span style="color: red;">'$'</span><span style="color: gray;">+</span><span style="color: #434343;">@ChkSrvName
</span><span style="color: blue;">SELECT </span><span style="color: #434343;">@OLAP </span><span style="color: blue;">= </span><span style="color: red;">'MSOLAP' </span><span style="color: gray;">+ </span><span style="color: #434343;">@SQLSrv </span><span style="color: green;">/*Setting up proper service name*/
</span><span style="color: blue;">SELECT </span><span style="color: #434343;">@FTS </span><span style="color: blue;">= </span><span style="color: red;">'MSFTESQL' </span><span style="color: gray;">+ </span><span style="color: #434343;">@SQLSrv
</span><span style="color: blue;">SELECT </span><span style="color: #434343;">@RS </span><span style="color: blue;">= </span><span style="color: red;">'ReportServer' </span><span style="color: gray;">+ </span><span style="color: #434343;">@SQLSrv
</span><span style="color: blue;">SELECT </span><span style="color: #434343;">@SQLAgent </span><span style="color: blue;">= </span><span style="color: red;">'SQLAgent' </span><span style="color: gray;">+ </span><span style="color: #434343;">@SQLSrv
</span><span style="color: blue;">SELECT </span><span style="color: #434343;">@SQLSrv </span><span style="color: blue;">= </span><span style="color: red;">'MSSQL' </span><span style="color: gray;">+ </span><span style="color: #434343;">@SQLSrv
</span><span style="color: blue;">END
</span><span style="color: green;">/* ---------------------------------- SQL Server Service Section ----------------------------------------------*/
</span><span style="color: blue;">SET </span><span style="color: #434343;">@REGKEY </span><span style="color: blue;">= </span><span style="color: red;">'System\CurrentControlSet\Services\'</span><span style="color: gray;">+</span><span style="color: #434343;">@SQLSrv
</span><span style="color: blue;">INSERT </span><span style="color: black;">tempdb.dbo.RegResult </span><span style="color: gray;">( </span><span style="color: black;">ResultValue </span><span style="color: gray;">) </span><span style="color: blue;">EXEC MASTER</span><span style="color: black;">.sys.</span><span style="color: darkred;">xp_regread </span><span style="color: #434343;">@rootkey</span><span style="color: blue;">=</span><span style="color: red;">'HKEY_LOCAL_MACHINE'</span><span style="color: gray;">, </span><span style="color: #434343;">@key</span><span style="color: blue;">= </span><span style="color: #434343;">@REGKEY
</span><span style="color: blue;">IF </span><span style="color: gray;">(</span><span style="color: blue;">SELECT </span><span style="color: black;">ResultValue </span><span style="color: blue;">FROM </span><span style="color: black;">tempdb.dbo.RegResult</span><span style="color: gray;">) </span><span style="color: blue;">= </span><span style="color: black;">1
</span><span style="color: blue;">BEGIN
INSERT </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: gray;">(</span><span style="color: black;">ServiceStatus</span><span style="color: gray;">) </span><span style="color: green;">/*Detecting staus of SQL Sever service*/
</span><span style="color: blue;">EXEC </span><span style="color: darkred;">xp_servicecontrol </span><span style="color: red;">N'QUERYSTATE'</span><span style="color: gray;">,</span><span style="color: #434343;">@SQLSrv
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServiceName </span><span style="color: blue;">= </span><span style="color: red;">'MS SQL Server Service' </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServerName </span><span style="color: blue;">= </span><span style="color: #434343;">@TrueSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">PhysicalSrverName </span><span style="color: blue;">= </span><span style="color: #434343;">@PhysicalSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">TRUNCATE TABLE </span><span style="color: black;">tempdb.dbo.RegResult
</span><span style="color: blue;">END
ELSE
BEGIN
INSERT INTO </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: gray;">(</span><span style="color: black;">ServiceStatus</span><span style="color: gray;">) </span><span style="color: blue;">VALUES </span><span style="color: gray;">(</span><span style="color: red;">'NOT INSTALLED'</span><span style="color: gray;">)
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServiceName </span><span style="color: blue;">= </span><span style="color: red;">'MS SQL Server Service' </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServerName </span><span style="color: blue;">= </span><span style="color: #434343;">@TrueSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">PhysicalSrverName </span><span style="color: blue;">= </span><span style="color: #434343;">@PhysicalSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">TRUNCATE TABLE </span><span style="color: black;">tempdb.dbo.RegResult
</span><span style="color: blue;">END
</span><span style="color: green;">/* ---------------------------------- SQL Server Agent Service Section -----------------------------------------*/
</span><span style="color: blue;">SET </span><span style="color: #434343;">@REGKEY </span><span style="color: blue;">= </span><span style="color: red;">'System\CurrentControlSet\Services\'</span><span style="color: gray;">+</span><span style="color: #434343;">@SQLAgent
</span><span style="color: blue;">INSERT </span><span style="color: black;">tempdb.dbo.RegResult </span><span style="color: gray;">( </span><span style="color: black;">ResultValue </span><span style="color: gray;">) </span><span style="color: blue;">EXEC MASTER</span><span style="color: black;">.sys.</span><span style="color: darkred;">xp_regread </span><span style="color: #434343;">@rootkey</span><span style="color: blue;">=</span><span style="color: red;">'HKEY_LOCAL_MACHINE'</span><span style="color: gray;">, </span><span style="color: #434343;">@key</span><span style="color: blue;">= </span><span style="color: #434343;">@REGKEY
</span><span style="color: blue;">IF </span><span style="color: gray;">(</span><span style="color: blue;">SELECT </span><span style="color: black;">ResultValue </span><span style="color: blue;">FROM </span><span style="color: black;">tempdb.dbo.RegResult</span><span style="color: gray;">) </span><span style="color: blue;">= </span><span style="color: black;">1
</span><span style="color: blue;">BEGIN
INSERT </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: gray;">(</span><span style="color: black;">ServiceStatus</span><span style="color: gray;">) </span><span style="color: green;">/*Detecting staus of SQL Agent service*/
</span><span style="color: blue;">EXEC </span><span style="color: darkred;">xp_servicecontrol </span><span style="color: red;">N'QUERYSTATE'</span><span style="color: gray;">,</span><span style="color: #434343;">@SQLAgent
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServiceName </span><span style="color: blue;">= </span><span style="color: red;">'SQL Server Agent Service' </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServerName </span><span style="color: blue;">= </span><span style="color: #434343;">@TrueSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">PhysicalSrverName </span><span style="color: blue;">= </span><span style="color: #434343;">@PhysicalSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">TRUNCATE TABLE </span><span style="color: black;">tempdb.dbo.RegResult
</span><span style="color: blue;">END
ELSE
BEGIN
INSERT INTO </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: gray;">(</span><span style="color: black;">ServiceStatus</span><span style="color: gray;">) </span><span style="color: blue;">VALUES </span><span style="color: gray;">(</span><span style="color: red;">'NOT INSTALLED'</span><span style="color: gray;">)
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServiceName </span><span style="color: blue;">= </span><span style="color: red;">'SQL Server Agent Service' </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServerName </span><span style="color: blue;">= </span><span style="color: #434343;">@TrueSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">PhysicalSrverName </span><span style="color: blue;">= </span><span style="color: #434343;">@PhysicalSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">TRUNCATE TABLE </span><span style="color: black;">tempdb.dbo.RegResult
</span><span style="color: blue;">END
</span><span style="color: green;">/* ---------------------------------- SQL Browser Service Section ----------------------------------------------*/
</span><span style="color: blue;">SET </span><span style="color: #434343;">@REGKEY </span><span style="color: blue;">= </span><span style="color: red;">'System\CurrentControlSet\Services\SQLBrowser'
</span><span style="color: blue;">INSERT </span><span style="color: black;">tempdb.dbo.RegResult </span><span style="color: gray;">( </span><span style="color: black;">ResultValue </span><span style="color: gray;">) </span><span style="color: blue;">EXEC MASTER</span><span style="color: black;">.sys.</span><span style="color: darkred;">xp_regread </span><span style="color: #434343;">@rootkey</span><span style="color: blue;">=</span><span style="color: red;">'HKEY_LOCAL_MACHINE'</span><span style="color: gray;">, </span><span style="color: #434343;">@key</span><span style="color: blue;">= </span><span style="color: #434343;">@REGKEY
</span><span style="color: blue;">IF </span><span style="color: gray;">(</span><span style="color: blue;">SELECT </span><span style="color: black;">ResultValue </span><span style="color: blue;">FROM </span><span style="color: black;">tempdb.dbo.RegResult</span><span style="color: gray;">) </span><span style="color: blue;">= </span><span style="color: black;">1
</span><span style="color: blue;">BEGIN
INSERT </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: gray;">(</span><span style="color: black;">ServiceStatus</span><span style="color: gray;">) </span><span style="color: green;">/*Detecting staus of SQL Browser Service*/
</span><span style="color: blue;">EXEC MASTER</span><span style="color: black;">.dbo.</span><span style="color: darkred;">xp_servicecontrol </span><span style="color: red;">N'QUERYSTATE'</span><span style="color: gray;">,</span><span style="color: red;">N'sqlbrowser'
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServiceName </span><span style="color: blue;">= </span><span style="color: red;">'SQL Browser Service - Instance Independent' </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServerName </span><span style="color: blue;">= </span><span style="color: #434343;">@TrueSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">PhysicalSrverName </span><span style="color: blue;">= </span><span style="color: #434343;">@PhysicalSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">TRUNCATE TABLE </span><span style="color: black;">tempdb.dbo.RegResult
</span><span style="color: blue;">END
ELSE
BEGIN
INSERT INTO </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: gray;">(</span><span style="color: black;">ServiceStatus</span><span style="color: gray;">) </span><span style="color: blue;">VALUES </span><span style="color: gray;">(</span><span style="color: red;">'NOT INSTALLED'</span><span style="color: gray;">)
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServiceName </span><span style="color: blue;">= </span><span style="color: red;">'SQL Browser Service - Instance Independent' </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServerName </span><span style="color: blue;">= </span><span style="color: #434343;">@TrueSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">PhysicalSrverName </span><span style="color: blue;">= </span><span style="color: #434343;">@PhysicalSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">TRUNCATE TABLE </span><span style="color: black;">tempdb.dbo.RegResult
</span><span style="color: blue;">END
</span><span style="color: green;">/* ---------------------------------- Integration Service Section ----------------------------------------------*/
</span><span style="color: blue;">IF CHARINDEX</span><span style="color: gray;">(</span><span style="color: red;">'2008'</span><span style="color: gray;">,</span><span style="color: #434343;">@@Version</span><span style="color: gray;">) > </span><span style="color: black;">0 </span><span style="color: blue;">SET </span><span style="color: #434343;">@DTS</span><span style="color: blue;">=</span><span style="color: red;">'MsDtsServer100'
</span><span style="color: blue;">IF CHARINDEX</span><span style="color: gray;">(</span><span style="color: red;">'2005'</span><span style="color: gray;">,</span><span style="color: #434343;">@@Version</span><span style="color: gray;">) > </span><span style="color: black;">0 </span><span style="color: blue;">SET </span><span style="color: #434343;">@DTS</span><span style="color: blue;">= </span><span style="color: red;">'MsDtsServer'
</span><span style="color: blue;">SET </span><span style="color: #434343;">@REGKEY </span><span style="color: blue;">= </span><span style="color: red;">'System\CurrentControlSet\Services\'</span><span style="color: gray;">+</span><span style="color: #434343;">@DTS
</span><span style="color: blue;">INSERT </span><span style="color: black;">tempdb.dbo.RegResult </span><span style="color: gray;">( </span><span style="color: black;">ResultValue </span><span style="color: gray;">) </span><span style="color: blue;">EXEC MASTER</span><span style="color: black;">.sys.</span><span style="color: darkred;">xp_regread </span><span style="color: #434343;">@rootkey</span><span style="color: blue;">=</span><span style="color: red;">'HKEY_LOCAL_MACHINE'</span><span style="color: gray;">, </span><span style="color: #434343;">@key</span><span style="color: blue;">= </span><span style="color: #434343;">@REGKEY
</span><span style="color: blue;">IF </span><span style="color: gray;">(</span><span style="color: blue;">SELECT </span><span style="color: black;">ResultValue </span><span style="color: blue;">FROM </span><span style="color: black;">tempdb.dbo.RegResult</span><span style="color: gray;">) </span><span style="color: blue;">= </span><span style="color: black;">1
</span><span style="color: blue;">BEGIN
INSERT </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: gray;">(</span><span style="color: black;">ServiceStatus</span><span style="color: gray;">) </span><span style="color: green;">/*Detecting staus of Intergration Service*/
</span><span style="color: blue;">EXEC MASTER</span><span style="color: black;">.dbo.</span><span style="color: darkred;">xp_servicecontrol </span><span style="color: red;">N'QUERYSTATE'</span><span style="color: gray;">,</span><span style="color: #434343;">@DTS
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServiceName </span><span style="color: blue;">= </span><span style="color: red;">'Integration Service - Instance Independent' </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServerName </span><span style="color: blue;">= </span><span style="color: #434343;">@TrueSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">PhysicalSrverName </span><span style="color: blue;">= </span><span style="color: #434343;">@PhysicalSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">TRUNCATE TABLE </span><span style="color: black;">tempdb.dbo.RegResult
</span><span style="color: blue;">END
ELSE
BEGIN
INSERT INTO </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: gray;">(</span><span style="color: black;">ServiceStatus</span><span style="color: gray;">) </span><span style="color: blue;">VALUES </span><span style="color: gray;">(</span><span style="color: red;">'NOT INSTALLED'</span><span style="color: gray;">)
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServiceName </span><span style="color: blue;">= </span><span style="color: red;">'Integration Service - Instance Independent' </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServerName </span><span style="color: blue;">= </span><span style="color: #434343;">@TrueSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">PhysicalSrverName </span><span style="color: blue;">= </span><span style="color: #434343;">@PhysicalSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">TRUNCATE TABLE </span><span style="color: black;">tempdb.dbo.RegResult
</span><span style="color: blue;">END
</span><span style="color: green;">/* ---------------------------------- Reporting Service Section ------------------------------------------------*/
</span><span style="color: blue;">SET </span><span style="color: #434343;">@REGKEY </span><span style="color: blue;">= </span><span style="color: red;">'System\CurrentControlSet\Services\'</span><span style="color: gray;">+</span><span style="color: #434343;">@RS
</span><span style="color: blue;">INSERT </span><span style="color: black;">tempdb.dbo.RegResult </span><span style="color: gray;">( </span><span style="color: black;">ResultValue </span><span style="color: gray;">) </span><span style="color: blue;">EXEC MASTER</span><span style="color: black;">.sys.</span><span style="color: darkred;">xp_regread </span><span style="color: #434343;">@rootkey</span><span style="color: blue;">=</span><span style="color: red;">'HKEY_LOCAL_MACHINE'</span><span style="color: gray;">, </span><span style="color: #434343;">@key</span><span style="color: blue;">= </span><span style="color: #434343;">@REGKEY
</span><span style="color: blue;">IF </span><span style="color: gray;">(</span><span style="color: blue;">SELECT </span><span style="color: black;">ResultValue </span><span style="color: blue;">FROM </span><span style="color: black;">tempdb.dbo.RegResult</span><span style="color: gray;">) </span><span style="color: blue;">= </span><span style="color: black;">1
</span><span style="color: blue;">BEGIN
INSERT </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: gray;">(</span><span style="color: black;">ServiceStatus</span><span style="color: gray;">) </span><span style="color: green;">/*Detecting staus of Reporting service*/
</span><span style="color: blue;">EXEC MASTER</span><span style="color: black;">.dbo.</span><span style="color: darkred;">xp_servicecontrol </span><span style="color: red;">N'QUERYSTATE'</span><span style="color: gray;">,</span><span style="color: #434343;">@RS
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServiceName </span><span style="color: blue;">= </span><span style="color: red;">'Reporting Service' </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServerName </span><span style="color: blue;">= </span><span style="color: #434343;">@TrueSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">PhysicalSrverName </span><span style="color: blue;">= </span><span style="color: #434343;">@PhysicalSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">TRUNCATE TABLE </span><span style="color: black;">tempdb.dbo.RegResult
</span><span style="color: blue;">END
ELSE
BEGIN
INSERT INTO </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: gray;">(</span><span style="color: black;">ServiceStatus</span><span style="color: gray;">) </span><span style="color: blue;">VALUES </span><span style="color: gray;">(</span><span style="color: red;">'NOT INSTALLED'</span><span style="color: gray;">)
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServiceName </span><span style="color: blue;">= </span><span style="color: red;">'Reporting Service' </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServerName </span><span style="color: blue;">= </span><span style="color: #434343;">@TrueSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">PhysicalSrverName </span><span style="color: blue;">= </span><span style="color: #434343;">@PhysicalSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">TRUNCATE TABLE </span><span style="color: black;">tempdb.dbo.RegResult
</span><span style="color: blue;">END
</span><span style="color: green;">/* ---------------------------------- Analysis Service Section -------------------------------------------------*/
</span><span style="color: blue;">IF </span><span style="color: #434343;">@ChkSrvName </span><span style="color: blue;">IS </span><span style="color: gray;">NULL </span><span style="color: green;">/*Detect default or named instance*/
</span><span style="color: blue;">BEGIN
SET </span><span style="color: #434343;">@OLAP </span><span style="color: blue;">= </span><span style="color: red;">'MSSQLServerOLAPService'
</span><span style="color: blue;">END
ELSE
BEGIN
SET </span><span style="color: #434343;">@OLAP </span><span style="color: blue;">= </span><span style="color: red;">'MSOLAP'</span><span style="color: gray;">+</span><span style="color: red;">'$'</span><span style="color: gray;">+</span><span style="color: #434343;">@ChkSrvName
</span><span style="color: blue;">SET </span><span style="color: #434343;">@REGKEY </span><span style="color: blue;">= </span><span style="color: red;">'System\CurrentControlSet\Services\'</span><span style="color: gray;">+</span><span style="color: #434343;">@OLAP
</span><span style="color: blue;">END
INSERT </span><span style="color: black;">tempdb.dbo.RegResult </span><span style="color: gray;">( </span><span style="color: black;">ResultValue </span><span style="color: gray;">) </span><span style="color: blue;">EXEC MASTER</span><span style="color: black;">.sys.</span><span style="color: darkred;">xp_regread </span><span style="color: #434343;">@rootkey</span><span style="color: blue;">=</span><span style="color: red;">'HKEY_LOCAL_MACHINE'</span><span style="color: gray;">, </span><span style="color: #434343;">@key</span><span style="color: blue;">= </span><span style="color: #434343;">@REGKEY
</span><span style="color: blue;">IF </span><span style="color: gray;">(</span><span style="color: blue;">SELECT </span><span style="color: black;">ResultValue </span><span style="color: blue;">FROM </span><span style="color: black;">tempdb.dbo.RegResult</span><span style="color: gray;">) </span><span style="color: blue;">= </span><span style="color: black;">1
</span><span style="color: blue;">BEGIN
INSERT </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: gray;">(</span><span style="color: black;">ServiceStatus</span><span style="color: gray;">) </span><span style="color: green;">/*Detecting staus of Analysis service*/
</span><span style="color: blue;">EXEC MASTER</span><span style="color: black;">.dbo.</span><span style="color: darkred;">xp_servicecontrol </span><span style="color: red;">N'QUERYSTATE'</span><span style="color: gray;">,</span><span style="color: #434343;">@OLAP
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServiceName </span><span style="color: blue;">= </span><span style="color: red;">'Analysis Services' </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServerName </span><span style="color: blue;">= </span><span style="color: #434343;">@TrueSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">PhysicalSrverName </span><span style="color: blue;">= </span><span style="color: #434343;">@PhysicalSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">TRUNCATE TABLE </span><span style="color: black;">tempdb.dbo.RegResult
</span><span style="color: blue;">END
ELSE
BEGIN
INSERT INTO </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: gray;">(</span><span style="color: black;">ServiceStatus</span><span style="color: gray;">) </span><span style="color: blue;">VALUES </span><span style="color: gray;">(</span><span style="color: red;">'NOT INSTALLED'</span><span style="color: gray;">)
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServiceName </span><span style="color: blue;">= </span><span style="color: red;">'Analysis Services' </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServerName </span><span style="color: blue;">= </span><span style="color: #434343;">@TrueSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">PhysicalSrverName </span><span style="color: blue;">= </span><span style="color: #434343;">@PhysicalSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">TRUNCATE TABLE </span><span style="color: black;">tempdb.dbo.RegResult
</span><span style="color: blue;">END
</span><span style="color: green;">/* ---------------------------------- Full Text Search Service Section -----------------------------------------*/
</span><span style="color: blue;">SET </span><span style="color: #434343;">@REGKEY </span><span style="color: blue;">= </span><span style="color: red;">'System\CurrentControlSet\Services\'</span><span style="color: gray;">+</span><span style="color: #434343;">@FTS
</span><span style="color: blue;">INSERT </span><span style="color: black;">tempdb.dbo.RegResult </span><span style="color: gray;">( </span><span style="color: black;">ResultValue </span><span style="color: gray;">) </span><span style="color: blue;">EXEC MASTER</span><span style="color: black;">.sys.</span><span style="color: darkred;">xp_regread </span><span style="color: #434343;">@rootkey</span><span style="color: blue;">=</span><span style="color: red;">'HKEY_LOCAL_MACHINE'</span><span style="color: gray;">, </span><span style="color: #434343;">@key</span><span style="color: blue;">= </span><span style="color: #434343;">@REGKEY
</span><span style="color: blue;">IF </span><span style="color: gray;">(</span><span style="color: blue;">SELECT </span><span style="color: black;">ResultValue </span><span style="color: blue;">FROM </span><span style="color: black;">tempdb.dbo.RegResult</span><span style="color: gray;">) </span><span style="color: blue;">= </span><span style="color: black;">1
</span><span style="color: blue;">BEGIN
INSERT </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: gray;">(</span><span style="color: black;">ServiceStatus</span><span style="color: gray;">) </span><span style="color: green;">/*Detecting staus of Full Text Search service*/
</span><span style="color: blue;">EXEC MASTER</span><span style="color: black;">.dbo.</span><span style="color: darkred;">xp_servicecontrol </span><span style="color: red;">N'QUERYSTATE'</span><span style="color: gray;">,</span><span style="color: #434343;">@FTS
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServiceName </span><span style="color: blue;">= </span><span style="color: red;">'Full Text Search Service' </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServerName </span><span style="color: blue;">= </span><span style="color: #434343;">@TrueSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">PhysicalSrverName </span><span style="color: blue;">= </span><span style="color: #434343;">@PhysicalSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">TRUNCATE TABLE </span><span style="color: black;">tempdb.dbo.RegResult
</span><span style="color: blue;">END
ELSE
BEGIN
INSERT INTO </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: gray;">(</span><span style="color: black;">ServiceStatus</span><span style="color: gray;">) </span><span style="color: blue;">VALUES </span><span style="color: gray;">(</span><span style="color: red;">'NOT INSTALLED'</span><span style="color: gray;">)
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServiceName </span><span style="color: blue;">= </span><span style="color: red;">'Full Text Search Service' </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">ServerName </span><span style="color: blue;">= </span><span style="color: #434343;">@TrueSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">UPDATE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: blue;">SET </span><span style="color: black;">PhysicalSrverName </span><span style="color: blue;">= </span><span style="color: #434343;">@PhysicalSrvName </span><span style="color: blue;">WHERE </span><span style="color: black;">RowID </span><span style="color: blue;">= </span><span style="color: #434343;">@@identity
</span><span style="color: blue;">TRUNCATE TABLE </span><span style="color: black;">tempdb.dbo.RegResult
</span><span style="color: blue;">END
</span><span style="color: green;">/* -------------------------------------------------------------------------------------------------------------*/
</span><span style="color: blue;">SELECT </span><span style="color: black;">ServiceName </span><span style="color: blue;">AS </span><span style="color: red;">'SQL Server Service'
</span><span style="color: gray;">,</span><span style="color: black;">ServiceStatus </span><span style="color: blue;">AS </span><span style="color: red;">'Current Service Status'
</span><span style="color: gray;">,</span><span style="color: black;">StatusDateTime </span><span style="color: blue;">AS </span><span style="color: red;">'Date/Time Service Status Checked'
</span><span style="color: blue;">FROM </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus
</span><span style="color: green;">/* -------------------------------------------------------------------------------------------------------------*/
/* --Send DB Mail - Uncomment this section if you want to send email of the service(s) status
EXEC msdb.dbo.sp_send_dbmail @profile_name='SQLAdmin',
@recipients='pearlknows@yahoo.com',
@subject='SQL Service(s) Status Update',
@body='This is the latest SQL Server Service(s) Status Report. Please review and take appropriate action if necessary:',
@query='SET NOCOUNT ON SELECT ServiceName AS ''SQL Server Service''
,ServiceStatus AS ''Current Service Status''
FROM tempdb.dbo.ServicesServiceStatus'
*/
</span><span style="color: blue;">DROP TABLE </span><span style="color: black;">tempdb.dbo.ServicesServiceStatus </span><span style="color: green;">/*Perform cleanup*/
</span><span style="color: blue;">DROP TABLE </span><span style="color: black;">tempdb.dbo.RegResult
</span>
First, the above script will declare and initialize all the variables for each SQL Service being evaluated. Then, it will look at each SQL related service independently. As of SQL Server 2005, not all services are installed by default, and therefore each service that is installed, will have an existing registry key in the Registry.
In order to evaluate the registry keys, to see if they exist, you will need to use the xp_regread undocumented extended stored procedure via T-SQL
This is the syntax of the xp_regread:
EXECUTE xp_regread [@rootkey=]'rootkey', [@key=]'key' [, [@value_name=]'value_name'] [, [@value=]@value OUTPUT]
For example, we can get the DISPLAY NAME for the SQLSERVERAGENT service, which is stored in the registry, by running:
DECLARE @regkeyval varchar(20), @value varchar(255), @rc int EXEC @rc=master.dbo.xp_regread @rootkey= 'HKEY_LOCAL_MACHINE', @key='System\CurrentControlSet\Services\SQLSERVERAGENT', @value_name='DisplayName', @regkeyval=@value OUTPUT SELECT @value AS DISPLAY_NAME
All we are doing in the case of this script, is checking for the existence of the key, essentially confirming the service is installed. If it returns an integer value of ‘1’, then it evaluated as true, and therefore the code will continue on to check the status of the service. Otherwise, the result will yield the text ‘NOT INSTALLED’, and will appear in the ‘Current Service Status’ column in the final output.
In addition to the Service Name and Status, you will also see the Time Checked, Server Name, and Instance Name, as some services are Instance Independent. In other words, you can have multiple installations of SSIS. You can also remove the column(s) from the SELECT statement, you don’t wish to see displayed.
Finally, here is what the query results should look like:
SQL Server Uptime
Before we finish this tip, along with the need to get the service status of your SQL Server Instance, another important thing to know is when the SQL Server was last restarted. With that information, you can easily calculate SQL Server Uptime and answer the question how long has the SQL Server been running? There are some ways to derive this, but the easiest way is to grab the ‘login_time’ from the very first process/spid that logs in upon startup from the sys.sysprocesses view. The script below will calculate the difference between the current time and the login time.
--Calculate SQLServer Uptime -Returns [Days:Hours:Minutes:Seconds]
select RTRIM(CONVERT(CHAR(3),DATEDIFF(second,login_time,getdate())/86400)) + ':' +
RIGHT('00'+RTRIM(CONVERT(CHAR(2),DATEDIFF(second,login_time,getdate())%86400/3600)),2) + ':' +
RIGHT('00'+RTRIM(CONVERT(CHAR(2),DATEDIFF(second,login_time,getdate())%86400%3600/60)),2) + ':' +
RIGHT('00'+RTRIM(CONVERT(CHAR(2),DATEDIFF(second,login_time,getdate())%86400%3600%60)),2) AS [DD:HRS:MIN:SEC]
from sys.sysprocesses --sysprocesses for SQL versions <2000
where spid = 1
Conclusion
The above method is yet another way to get the current status of your SQL Services. With this method, we are aiming to use (2) two unsupported and undocumented extended stored procedures – xp_servicecontrol and xp_regread, which we showed you here. You can see how powerful and potentially malicious these could be, and therefore must be conscious of securing these extended procs. (See some articles on that below). Furthermore, it seems like quite a hefty query with several lines of code (261 to be exact), just to get us the status of our services. It would be real neat, if we can return all the relevant service(s) status and start-up time of the SQL Server in one line of code. Imagine that! Well, stay tuned for the next and concluding tip of this 3-part series, SQL Service Status – An Evolution, and I will show you how!
Next Steps
In the meanwhile, to learn some more about using the above-referenced components, check out some of these previous tips and resources below:
