Labels

SSMS SQL Server Agent Job alert




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   


2 comments:
Write comments

Please do not enter spam links

Services

More Services