Search

Thursday, May 5, 2011

Monitor your website or any URL from SQL Server

This code should be 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 temporarily 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 
Ref:http://www.sqlservercentral.com

1 comment:

  1. hey,

    I have tried this but i get the error constatnly no matter what the url is? Have you seen this or is there anything I should look out for?

    Cheers

    ReplyDelete