Checking SQLAgent service and starting it if not.

/*
Because many programs I wrote depends on the SQL Agent Service and to really make sure that no one has configured 
this service to run manually (you see many things in life) I rely on this litle piece of code

*/Use master
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp__CheckSQLAgent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp__CheckSQLAgent]
GO
CREATE PROC dbo.sp__CheckSQLAgent
AS

/*********************************************************************************************************************
** Creation Date: 2002-10-02
** Created By: avigneau
** Database: master
** Description  : If SQL Agent is not already running run it  
  
** Parameters   : none (could be flagged for startup)

** Compatibility: SQL Server 7.0, 2000
** Remark: You need the priviledge to run xp_cmdshell extended.
  
** Example: EXEC sp__CheckSQLAgent
**********************************************************************************************************************/
--*****************************************************************************************************************
--********************************** Make sure SQL Agent Service is running *************************************--
--*****************************************************************************************************************
SET NOCOUNT ON
DECLARE @flag int,@vcCommand varchar(2048)
EXEC @flag = msdb..sp_sqlagent_notify 'G'
IF @flag <> 0
BEGIN
SELECT @vcCommand = 'NET START '+CASE WHEN PATINDEX('%\%',@@SERVERNAME) = 0 THEN 'SQLSERVERAGENT' 
ELSE 'SQLAGENT$'+SUBSTRING(@@SERVERNAME,PATINDEX('%\%',@@SERVERNAME)+1,LEN(@@SERVERNAME)-PATINDEX('%\%',@@SERVERNAME))END
EXEC master.dbo.xp_cmdshell @vcCommand
END

GO