A follow up to yesterday

As I have been asked,  here to tidy up yesterdays post is the procedure my startup procedure calls along with the logging table deployed in the DBA database.

Just to muddy the water further I have routines for remotely calling the DBAMessages table through a remote server to send out email from a central server!!

Just to explain that I have been ( previously ) limited to only using one Server to send email alerts for multiple Servers so I attempt to code to deal with all possible circumstances.

I have two DBA databases on each server which hold code and data I use to manage the server.
I try to make sure everything is generic rather than specific as carrying a different version of a procedure or table on every server can quickly become a nightmare, not counting anything  specific to the SQL Server version or edition.

Here’s the procedure which handles messages and the table that messages are written to ( optionally ).

/****** Object:  StoredProcedure [dbo].[dbasp_SendMessage]    Script Date: 11/21/2011 19:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[dbasp_SendMessage]
@subject varchar(100),
@message varchar(1000),
@to varchar(255)
-- =============================================================  
--  Stored Procedure:     dbo.dbasp_SendMessage                          
--  Written by:         Colin Leversuch-Roberts
--                      www.kelemconsulting.co.uk     
--                        (c) 2005                            
--                                                                
--  Purpose:               simplifies sending a message to email or file   
--                                                                
--  System:                DBA maintenance - ServerAdmin
--
--  Input Paramters:        @subject   -  the email/message subject
--                                     @message   -  email/message body
--                                     @to        -  recipients
--             
--  Output Parameters:     None                                      
--  Return Status:         @@error
--                                                                
--  Usage:                 EXEC dbo.dbasp_SendMessage 'Warning','message','fred@somedomain.co.uk'
--                                                                
--  Called By:                                                           
--  Calls:                 DBADatabase.dbo.dbasp_SendSMTPmail
--                        DBADatabase.dbo.ServerParameters ( table )
--                      DBADatabase.dbo.DbaMessages ( table )
--
--  Notes:        
--            This is to simplify the direction of messages if a mail server
--            is unavailable. Sends mail or writes to message table
--
--                                                                
--  VERSION HISTORY
--  Version No        Date            Description
--  1            19th May 2005    Initial Release
--  2                            sql 2008
-- =============================================================  
--
as
set nocount on
declare @from varchar(100) = @@servername+'@regus.com';
--
IF (Select ParmValue from DBADatabase.dbo.ServerParameters where ParmName = 'MessageMail')=1
    BEGIN
        exec DBADatabase.dbo.dbasp_SendSMTPmail @from, @to, @subject, @message;
        print  @subject;
    END
--endif
IF (Select ParmValue from DBADatabase.dbo.ServerParameters where ParmName = 'MessageLog')=1
    BEGIN
        Insert into DBADatabase.dbo.DbaMessages(Alert,[Notification]) values (@subject,@message);
        print  @subject;
    END
--endif
return(@@error);
GO

And this is the table definition

/****** Object:  Table [dbo].[DbaMessages]    Script Date: 11/21/2011 20:01:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DbaMessages](
    [Numkey] [int] IDENTITY(1,1) NOT NULL,
    [TheDate] [datetime] NOT NULL,
    [Alert] [varchar](250) NOT NULL,
    [Notification] [varchar](5000) NOT NULL,
    [Actioned] [tinyint] NOT NULL,
 CONSTRAINT [PK_DbaMessages] PRIMARY KEY CLUSTERED 
(
    [Numkey] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[DbaMessages] ADD  CONSTRAINT [DF_DbaMessages_TheDate]  DEFAULT (getdate()) FOR [TheDate]
GO
ALTER TABLE [dbo].[DbaMessages] ADD  CONSTRAINT [DF_DbaMessages_Actioned]  DEFAULT ((0)) FOR [Actioned]
GO
Published Tuesday, November 22, 2011 10:02 PM by GrumpyOldDBA

Comments

No Comments