by
1
5
4,588
1
Top 1% !
Popular
Famous
Specified
OpenSource
Popularity: 1892nd place
Created
Modified Aug 26, 2013

Published on:

No tags for this snippet yet.
Architecturex86_64
DBMSMS SQL Server
LanguageT-SQL
LicenseMIT_X11
OSWindows

Monitor your website 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 temperorly and again turning it off if you disabled that option for security reasons.

Thanks to SQL SERVER KID, http://www.sqlservercentral.com/scripts/Monitor+URL/67271/
Copy Embed Code
<iframe id="embedFrame" style="width:600px; height:300px;"
src="https://www.snip2code.com/Embed/9902/Monitor-your-website-from-SQL-Server?startLine=0"></iframe>
Click on the embed code to copy it into your clipboard Width Height
Leave empty to retrieve all the content Start End
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
If you want to be updated about similar snippets, Sign in and follow our Channels

blog comments powered by Disqus