Below is a piece of code which will help you with the job alert failure to be sent as an sms alert by using calling the stored procedure USP_DailyJobFailure_SMSAlert, Add the user details into the tblSMS_Users table and alter the alerting url as per your convenience as the below link in modified.
/********************SMS Recipient***********************/
USE [DBA]
GO
/****** Object: Table [dbo].[tblSMS_Users] Script Date: 12/29/2017 12:02:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblSMS_Users](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](200) NULL,
[MobileNo] [varchar](20) NULL,
[Active] [int] NULL,
[EmailID] [varchar](200) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/******************SMS Procedure************************/
CREATE procedure [dbo].[pr_SendSmsSQL]
@MobileNo varchar(12),
@smstext as varchar(500),
@sResponse varchar(1000) OUT
as
BEGIN
Declare @iReq int,@hr int
Declare @sUrl as varchar(500)
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)
-- Create Object for XMLHTTP
EXEC @hr = sp_OACreate 'Microsoft.XMLHTTP', @iReq OUT
print @hr
if @hr <> 0
Raiserror('sp_OACreate Microsoft.XMLHTTP FAILED!', 16, 1)
--set @sUrl='http://1.2.3.4/smpp/sendsms?username=usr&password=SSS=#MobNo#&from=MA&text=#Msg#'
set @sUrl='http://alerts.XYZ.com/api/web2sms.php?&sender=XYZ&to=#MobNo#&message=#Msg#&type=json'
set @sUrl=REPLACE(@sUrl,'#MobNo#',@MobileNo)
set @sUrl=REPLACE(@sUrl,'#Msg#',@smstext)
print @sUrl
-- sms code start
EXEC @hr = sp_OAMethod @iReq, 'Open', NULL, 'GET', @sUrl, true
print @hr
if @hr <> 0
Raiserror('sp_OAMethod Open FAILED!', 16, 1)
EXEC @hr = sp_OAMethod @iReq, 'send'
End
/********** Parent Procedure to be used in SQL Agent Job*********/
CREATE Proc [dbo].[USP_DailyJobFailure_SMSAlert]
AS
SET NOCOUNT ON
BEGIN
IF OBJECT_ID('tempdb..#tempdate') is not null
Drop table #tempdate
SELECT * into #tempdate
from
( SELECT JN.name
,JN.job_id
,JH.message
,JH.run_date
,run_time
,msdb.dbo.agent_datetime(JH.run_date,JH.run_time) as RunDateTime
,JH.step_id,JH.step_name
from msdb..sysjobs JN
JOIN msdb..sysjobsteps JS
ON JS.job_id = JN.job_id
JOIN msdb..sysjobhistory JH
ON JH.job_id = JN.job_id
WHERE JH.run_date = CONVERT(VARCHAR(8),GETDATE(),112) AND
run_status=0
--and name in ('')
)as aa
IF OBJECT_ID('tempdb..#final') is not null
Drop table #final
SELECT DISTINCT name
INTO #final from #tempdate
--WHERE RunDateTime>= DATEADD(MINUTE,-15,GETDATE())
ALTER TABLE #final ADD ID int IDENTITY(1,1)
----Mail sending script---
BEGIN
DECLARE @SQL NVARCHAR(max)
SET @SQL = '<html>
<body>
<p>
Hi, <br><br>
</br></br>
Please find the Failed Job Details List
below:
</p>
</br>
<table border="1">
<tr style="background-color:blue;color:white;">
<td align="center">
Job Name
</td>
</tr>'
END
DECLARE @i INT=1
,@E INT
,@jobname varchar(MAX)
SELECT @E = COUNT(*) FROM #final
WHILE(@i<= @E)
BEGIN
SELECT @jobname= name
FROM #final WHERE ID=@i
SET @SQL = @SQL + '<tr style="background-color:red;color:white">
<td align=Left>' + ISNULL(@jobname, 'N/A') + ' </tr>'
SET @i=@i+1
END
SELECT @SQL = @SQL + '</table>
<p>
Thanks,
<br />
DBA Team
</br>
</p>
</body>
</html>'
--SELECT @SQL
DECLARE @F INT,@N INT=1
SELECT @F= COUNT(*) FROM #final
SELECT @F
SELECT @N
IF @F>0
BEGIN
DECLARE @EmailList VARCHAR(MAX)
SELECT @EmailList = COALESCE(@EmailList+';' , '') + emailid
FROM DBA..tblSMS_Users WHERE Active=1
EXEC msdb..sp_send_dbmail
@profile_name = '_MAIL',
@recipients = @EmailList,
@subject = 'Daily Job is Failed in Server',
@body = @SQL,
@body_format = 'HTML'
END
/*SMS Sending step*/
DECLARE @list VARCHAR(MAX)
SELECT @list = COALESCE(@list+',' , '') + name
FROM #final
DECLARE @Message varchar(max),@MobNo varchar(15)
DECLARE @TotCount int,@Begin int
SET @Begin=1
SELECT @TotCount = COUNT(*) FROM DBA..tblSMS_Users WHERE Active=1
IF(@list is not null)
Begin
WHILE ( @Begin <= @TotCount)
Begin
SET @list = @list+' '+'Alerts'
select @MobNo=MobileNo select *FROM OperationsDB..tblSMS_Users WHERE Active=1 AND id=@Begin
EXEC [OperationsDB].dbo.pr_SendSmsSQL @MobNo,@list,''
SET @Begin = @Begin +1
END
END
END
No comments:
Post a Comment