SQL- SP_PROCOPTION – AutoNotification to App Owners – When SQL Instance Restarts

We had a situation where Application owners automatically notified whenever server restarts.  The SP_PROCOPTION do have a facility to perform this activity. The SP_PROCOPTION built-in stored procedure being used to invoke the user defined stored proc automatically.

The details of this implementation is as follows

• User defined meta data table to hold all Application Owner Details
• Create user defined Stored procedure Named “Notification” under MASTER database(Mandatory).The below automation developed with an intellegence to concatenate all Application Owner’s email-ids.
• Modify the stored procedure “Notification” – ProfileName and BlindCopyReceipents
• Add this stored procedure for automatic execution
• Once done Restart the SQL Instance .

Pre-requisites

•  Mail Profile – Make sure that you have DB Mail Configured.

Download the complete code here SQL- AutoNotification to App Owners – When SQL Instance Restarts

Table Creation to store Meta Data about the Server

USE MASTER
GO
CREATE TABLE AutoAlert
(
id INT IDENTITY(1,1),
DbName VARCHAR(100),
DbOwner VARCHAR(100) DEFAULT ‘DBA Team’,
AppOwner VARCHAR(100),
AppOwnerID VARCHAR(100))

/* Insert AppOwners Details*/
INSERT INTO AutoAlert(dbname,AppOwner,AppOwnerID) VALUES(‘PROD_OLAP’,’Dave H’,’dheim@PowerSQL.com’)
INSERT INTO AutoAlert(dbname,AppOwner,AppOwnerID) VALUES(‘Crystal_App’,’Ana V’,’vana@PowerSQL.com’)

–SELECT * FROM dbo.AutoAlert

Stored Procedure Creation – Change Profile and BCC receipients

/* Create a new Stored Proc NOTIFICATION Under Master Database*/

CREATE PROCEDURE NOTIFICAION
AS
DECLARE @tableHTML NVARCHAR(MAX),
@td1 CHAR(15),
@td2 CHAR(15) ,
@td3 CHAR(20) ,
@td4 CHAR(15),
@td5 CHAR(15),
@td6 CHAR(15),
@td7 CHAR(15),
@Loopstatus2 INT,
@RowId2 INT,
@dmll NVARCHAR(max),
@dml2 NVARCHAR(max),
@Loopstatus1 INT,
@RowId1 INT,
@ProfileName VARCHAR(100),
@blind_copy_recipients VARCHAR(100),
@receipients VARCHAR(200),
@subject VARCHAR(500),
@email1 VARCHAR(MAX),
@email2 VARCHAR(MAX),
@email varCHAR(100)

/* Change Profile and Receipients details*/

SET @ProfileName =’PowerSQL’
SET @blind_copy_recipients=’PowerSQL@PowerSQL.com’
CREATE TABLE #mail
(
id INT IDENTITY(1,1),
email VARCHAR(100))

INSERT INTO #mail(email)
SELECT DISTINCT AppOwnerID FROM dbo.AutoAlert

SET @LoopStatus2=1
SET @RowId2=1
SET @email1=”
SET @email2=”

WHILE @LoopStatus2>0
BEGIN
SELECT @email=email FROM #mail WHERE id=@RowId2
IF @@ROWCOUNT = 0
BEGIN
SET @LoopStatus2=0
END
ELSE
BEGIN
SET @email1=@email1+@email+’;’
SET @email2=@email2+@email1
PRINT @email2
SET @email1=”
END
SET @RowId2=@RowId2+1
END

PRINT @email2
SET @email2=SUBSTRING(@email2,0,len(@email2))
PRINT @email2
SET @dmll=”
SET @dml2=”
SET @Loopstatus1=1
SET @RowId1=1
SET @tableHTML =
N'<H1>Server Restart Notification </H1>’ +
N'<table border=”1″ cellpadding=”1″><tr>
<th BGCOLOR=”RED”>[Server]</th>
<th BGCOLOR=”RED”>[DB Name]</th>
<th BGCOLOR=”RED”>[App Owner]</th>
</tr>’

WHILE @Loopstatus1<>0
BEGIN
SELECT
@td1 =@@servername,
@td2 =DBName,
@td3 =AppOwner
FROM AutoAlert WHERE id=@RowId1

IF @@ROWCOUNT=0
BEGIN
SET @Loopstatus1=0
END
ELSE
BEGIN
SET @dmll= @dml2+N'<tr><td>’+@td1+N'</td><td>’+@td2+'</td><td>’+@td3+'</td></tr>’
SET @dml2=@dmll
SET @dmll=”
END
SET @RowId1=@RowId1+1
END

SET @tableHTML=@tableHTML+@dml2+'</table>’+CHAR(13)+’Auto generated Email, Do not reply’+CHAR(13)+’Appvion DBA Team’

–PRINT @tableHTML

SET @Subject=’DB Server ‘+ @@servername +’ Restarted, Please Check all your dependent App Services’

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients=@email2,
@blind_copy_recipients=@blind_copy_recipients,
@subject = @subject,
@body = @tableHTML,
@body_format = ‘HTML’;

DROP TABLE #mail

The following SQL sets a procedure for automatic execution

EXEC sp_procoption @ProcName = ‘NOTIFICAION’
, @OptionName = ‘startup’
, @OptionValue = ‘on’;

The following SQL stops a procedure from automatic execution

EXEC sp_procoption @ProcName = ‘NOTIFICAION’
, @OptionValue = ‘off’;

Restart SQL Instance

Output :-