September 12, 2006 at 11:04 am
Hello ,
I have a script which checks the status of the sql server agent whether it is running or not.
The Script is:
select Agent_running = case when count(*) > 0 then 1 else 0 end
from master.dbo.sysprocesses
where program_name like 'SQLAgent%'
I am using the SMTP server for my mail notifications.
How can modify this , so that I can get a mail when the sql server agent stops. Like running it in xp_cmdshell or though command line .
Or is there any method or script which I can use to report the dba mail box when the sql server agent fails or stops.
Thanks
September 12, 2006 at 11:14 am
If this process needs to work it should keep running every X minutes. But the problem is then you need to schedule a job that keeps running and sends email. But if SQL Agent is not running how witll the job run.
My Guess is you might have to use VBScript code and use SQLServer object and use it to find if the services are running and that code need to be scheduled in Window Scheduler.
Thanks
Sreejith
September 12, 2006 at 11:18 am
Hi,
Also if there are 2 servers , the 1st server can check the status of the 2nd server agent and mail when it fails and vice-versa.
Let me know if there is any method or script how I can go about this
Thanks
September 12, 2006 at 1:37 pm
Here is an article I wrote to do exactly what you are talking about. If you have questions let me know:
http://www.databasejournal.com/features/mssql/article.php/3622746
Gregory A. Larsen, MVP
September 12, 2006 at 2:09 pm
Hello Larsen,
Thank you very much for the script . I will be testing it today.
I got a doubt if I can use the SMTP server mail, which I am using for my other mail notifications.
The one I am using is:
EXEC master.dbo.xp_smtp_sendmail
@FROM = N'testsql2000@is.depaul.edu',
@TO = N'dvaddi@depaul.edu',
@server = N'smtp.depaul.edu',
@subject = N'Status of sqlserver Agent!',
@type = N'text/html',
@message = @message
Thanks
September 12, 2006 at 2:18 pm
Hello Larsen,
Thank you very much for the script . I will be testing it today.
I got a doubt if I can use the SMTP server mail, which I am using for my other mail notifications.
The one I am using is:
EXEC master.dbo.xp_smtp_sendmail
@FROM = N'testsql2000@is.depaul.edu',
@TO = N'dvaddi@depaul.edu',
@server = N'smtp.depaul.edu',
@subject = N'Status of sqlserver Agent!',
@type = N'text/html',
@message = @message
And also I am getting an error , while I am executing : usp_service_monitor_backup .
The error is in the last few lines:
exec master.dbo.sp_executesql @cmd2,N'@state1 varchar(100) out',@state1 out
-- Is the service that was monitored not running
if @state1 <1 'Running'
begin
-- Display message that primary monitor is down
print 'Master server "' + rtrim(@masterserver) + '" for monitoring is not available.'
-- Perform monitoring
exec dba.dbo.usp_service_monitor @email_dist
end
else
print 'Master Server is running'
end
The Error I am facing is :
Line 62: Incorrect syntax near 'Running'.
Line 70
Incorrect syntax near the keyword 'else'.
Thanks
September 12, 2006 at 4:10 pm
Hello Larsen,
While executing the scripts , I came to know I am can not run the script as there is a problem with my server name .
My server name is : testsql.is.depaul.edu
It giving me an error , while I was executing the 1st script , which checks the connection with the other server.
Its giving the error ' . ' and ' is ' a keyword.
Is there any alternative on which I can work.
Thanks
September 13, 2006 at 11:14 am
It's because 'IS' is a reserve word. It's just a good idea never to name things that are keywords/Reserved words, but since this is how it is...
Typically you can use [] in SQL server to escape a reserved word. In your case I'd imagine it would work with testsql.[is].depaul.edu.
-Luke.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply