Hi Guys,

I have the following query:

CREATE proc [dbo].[sp__TNGAlert]

@JobName NVARCHAR(4000),

@Alert VARCHAR(50)

as

SET NOCOUNT ON

--

DECLARE @AlertDir NVARCHAR(4000)

DECLARE @ServerName VARCHAR(50)

DECLARE @FailureDate VARCHAR(50)

DECLARE @Error NVARCHAR(4000)

DECLARE @cmd SYSNAME

--

SET @AlertDir = ' >> \\BEPSQL01\SQLG\TNGLog\Alerts.txt'

SET @ServerName = @@ServerName

SET @FailureDate = (select getdate())

SET @Error = @ServerName + ' - ' + @JobName + ' ' + @Alert + ' on ' +
@FailureDate + @AlertDir

SET @cmd = 'echo ' + @error

--

EXEC master.dbo.xp_cmdshell @cmd

This script was created to send alerts via email and txt message (sms)
when a particular job on a server fails.

I am planning to create a DTS package to display the list of sevrers in
a csv file where the agent is running and also where the agent is not
running. I have to figure out a way, if the SQL agent is not running
then an alert needs to be sent via email and txt message. Can anyone pls
assist me on this?

The following script that I am using will be used in the DTS package to
display whether the service is running or not

create proc sp__SQLAgent
As

select @@Servername as ServerName,Program_name,
Case when program_name = 'SQLAgent - Generic Refresher'
then 'SQL Agent Running'
else 'SQL Agent Not running'
end as Process
from sysprocesses where program_name Like '%Ref%'



Kind Regards

Anchelin Koert

OptiSolutions - Vodacom SQL DBA/Support

Office : +27 21 440 8955

Cell : +27 72 607 6150
Email : Ancheline.[Email address protected]
<mailto:Ancheline.[Email address protected]
Web : http://optisolutions.co.za/ <blocked::http://optisolutions.co.za/>









“This e-mail is sent on the Terms and Conditions that can be accessed by Clicking on this link http://www.vodacom.co.za/legal/email.jsp "

MS Sql Server LazyDBA home page