Invoke URL from Sql Server 2005 Stored Procedure

At many times, there are requirements to alert us through email or SMS.

On Sql Server these alert mechanism always found to be tedious.

Though sql server on there own provided some alert sending tools, but i found to complex in configuring them.

I found an other way of alerting us through invoking an url which in-turn would send email or sms as per our requirement.

By using xp_cmdshell command in sql server, operating system shell command can be executed. So, xp_cmdshell command can be used to execute a vbscript file.

Inturn Vb Script can send email or invoke a url to alert us. xp_cmdshell statement to be included to call the vbscript

EXEC master..xp_cmdshell 'C:\winnt\system32\cscript.exe c:\sqljobalert.vbs',no_output  

VBScript source code of c:\sqljobalert.vbs

call main()
sub main()
  'Below code would send an email from vbscript
	Dim cdomsg, icon, Flds
	Set cdomsg = wscript.CreateObject ("CDO.Message")
	set icon = wscript.CreateObject ("CDO.configuration")
	Set Flds = icon.Fields 
	Flds.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "your smtp server"
	Flds.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
	flds.Update
	set cdomsg.Configuration = icon
	
	cdoMsg.To = "to mail id"
	cdoMsg.From = "from mail id"
	cdoMSg.Subject = "Subject of the mail"
	cdomsg.TextBody = "Email Message Content"
	cdomsg.send

   'Below code would invoke an url from vbscript
	Dim xmlHTTP, urlString
	Set xmlHTTP = WScript.CreateObject("Msxml2.XMLHTTP")
	urlString = "http://url to be invoked"
	xmlHTTP.Open "GET", urlString , False
	xmlHTTP.Send  ""
end sub 

This can be used to alert on sql server job failure