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 :-

ServerRestart

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL, T-SQL and tagged , , . Bookmark the permalink.

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

  1. aquaprima says:

    You are so interesting! I don’t think I have read through a single thing like this before. So great to discover someone with original thoughts on this issue. Really.. thanks for starting this up. This site is one thing that is required on the web, someone with some originality!

  2. midsummer says:

    Good post. I definitely appreciate this site. Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s