SQL Server Agent Status

/* CREATE THIS STORED PROCEDURE IN YOUR SQL SERVER DATABASE */

CREATE PROCEDURE [dbo].[spAgentStatus]

@user		varchar(30),	-- VALID SQL Server Login
@pwd		varchar(30)	-- VALID Login Password

AS

-- Returned information
DECLARE @source 	varchar (255)
DECLARE @description 	varchar (255)
DECLARE @status_msg	varchar(100) 
DECLARE @Status		int 

-- Internal variables
DECLARE @object int
DECLARE @hr int

-- Create SQLDMO Object
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object, @source OUT, @description OUT 
   SELECT Source=@source, Description=@description, status_msg = @status_msg, status = @status
   RETURN
END

-- Call Method to connect to the server 
EXEC @hr = sp_OAMethod @object, 'Connect', NULL, @@ServerName, @user, @pwd
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object, @source OUT, @description OUT 
   SELECT Source=@source, Description=@description, status_msg = @status_msg, status = @status
   RETURN
END

-- Get property value for the SQL Server Agent current status
DECLARE @property varchar(255)
EXEC @hr = sp_OAGetProperty @object, 'JobServer.Status', @property OUT
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object, @source OUT, @description OUT 
   SELECT Source=@source, Description=@description, status_msg = @status_msg, status = @status
   RETURN
END

--Get status information to return
SET @status_msg = CASE @property WHEN 1 THEN 'SQL Server Agent is running'
                             WHEN 3 THEN 'SQL Server Agent is stopped' 
                             WHEN 2 THEN 'SQL Server Agent is paused'
                             WHEN 6 THEN 'SQL Server Agent is in transition from paused to running'
                             WHEN 7 THEN 'SQL Server Agent is in transition from running to paused'
                             WHEN 4 THEN 'SQL Server Agent is in transition from stopped to running'
                             WHEN 5 THEN 'SQL Server Agent is in transition from running to stopped'
			     ELSE 'Unable to determine service execution state.' 	
               END
SET @status = @property

-- clean up objects
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object, @source OUT, @description OUT 
   SELECT Source=@source, Description=@description, status_msg = @status_msg, status = @status
   RETURN
END

-- if no errors return status information
SELECT Source=@source, Description=@description, status_msg = @status_msg, status = @status
GO

/* End of Stored Procedure */

IF EXISTS(SELECT 1 FROM MASTER.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
    BEGIN
       SELECT @@SERVERNAME AS 'InstanceName', 1 AS 'SQLServerAgentRunning'
    END
ELSE
    BEGIN
       SELECT @@SERVERNAME AS 'InstanceName', 0 AS 'SQLServerAgentRunning'
    END