June 16, 2009 at 2:18 pm
Below is the code which need to the added as t-sql step of the job and schedule it for every 5 min or as per your requirement it will monitor the url given by you and will send the mail when the application is down. It will also take care of certification errors(Ignore the Errors).It will be very useful and also does not require any third party stuff.Here I added few steps to enable ole automation to be enabled temperorly and again turning it off if you disabled that option for security reasons.
DECLARE @flag int
select @flag= convert(int,value) from sys.configurations where name='Ole Automation Procedures'
--Enable the ole automation option temperorly if it is turned off
if @flag=0
begin
EXEC sp_configure 'show advanced options' , '1'
reconfigure
EXEC sp_configure 'OLE AUTOMATION' , '1'
reconfigure
end
DECLARE @url varchar(300)
DECLARE @win int
DECLARE @hr int
DECLARE @text varchar(8000)
DECLARE @bodytext varchar(300)
SET @url = 'http://yoururl.com'
EXEC @hr=sp_OACreate 'MSXML2.ServerXMLHTTP',@win OUT
IF @hr <> 0
EXEC sp_OAGetErrorInfo @win
EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'
EXEC @hr=sp_OAMethod @win, 'SetOption',NULL,'2','13056'
EXEC @hr=sp_OAMethod @win,'Send'
EXEC @hr=sp_OAGetProperty @win,'StatusText',@text OUTPUT
EXEC @hr=sp_OADestroy @win
IF @hr <> 0
EXEC sp_OAGetErrorInfo @win
if @text is null
begin
set @bodytext=@url+'- Application is down.Please take necessary action immediately'
--Sending Mail when website is down
exec msdb.dbo.sp_send_dbmail @profile_name = 'nameof mailserver'
, @recipients = 'xxx@xyz.com'
, @copy_recipients = null
, @blind_copy_recipients =null
, @subject ='Application is Down'
, @body =@bodytext
end
-- disabling the ole automation if we enabled it temperorly
if @flag=0
begin
EXEC sp_configure 'OLE AUTOMATION' , '0'
EXEC sp_configure 'show advanced options' , '0'
reconfigure
end
June 17, 2009 at 7:43 am
You might want to post this in Scripts area (look to the left, just below forums on the menu choices). It's more likely to be found by people looking for solutions of this type.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply