Hi Anchelin,

Try this to check for your SQL agent.

Create table ##tempResults (
ResultText varchar(300)
)

Insert into ##tempResults
exec master..xp_cmdshell 'net start'

Select LTRIM(ResultText) from ##tempResults
where LTRIM(ResultText) = 'SQLSERVERAGENT'

Cheers
Sam

-----Original Message-----
From: Ancheline Koert
[mailto:mssqldba-ezmlmshield-x83012208.[Email address protected]
Sent: 27 February 2007 11:21
To: LazyDBA Discussion
Subject: FW: SQL Agent Running

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 "


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



This email is confidential and may be privileged. If you are not the intended recipient please notify the sender immediately and delete the email from your computer. You should not copy the email, use it or any purpose or disclose its contents to any other person. Please note that any views or opinions presented in this email may be personal to the author and do not necessarily represent the views or opinions of Digica. It is the responsibility of the recipient to check this email for the presence of viruses. Digica accepts no liability for any damage caused by any virus transmitted by this email.

Digica Group Limited
Registered in England No. 5244928

Digica Limited
Registered in England No. 3434654

Digica (FMS) Limited
Registered in England No. 3798091

Registered address: Phoenix House, Colliers Way, Nottingham, NG8 6AT UK
Reception Tel: + 44 (0) 115 977 1177
Support Centre: 0845 607 7070
Fax: + 44 (0) 115 977 7000
http://www.digica.com

SOUTH AFRICA: Building 3, Parc du Cap, Mispel Road, Bellville, 7535, South Africa
Tel: + 27 (0) 21 957 4900
Fax: + 27 (0) 21 948 3135
http://www.digica.com

This message has been scanned for viruses by Surfcontrol in Partnership with Digica

MS Sql Server LazyDBA home page