if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_SqlServerRestartNotificationMail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SqlServerRestartNotificationMail]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE   proc  [dbo].[sp_SqlServerRestartNotificationMail]    
    
  
--SET NOCOUNT ON    
    
As    

DECLARE @msgg as varchar(100) 
DECLARE @servername as varchar(50)
DECLARE @msg as varchar(500)   
DECLARE @activenode as varchar(30)   


select @servername =@@servername

Select @activenode=CONVERT(varchar(30), ServerProperty('ComputerNamePhysicalNetBIOS'))


Set @msgg = 'SQL Server Instance '  + @servername + ' has restarted'

Set @msg = 'SQL Server Instance ' + @servername + ' has restarted at '  +  cast(getdate() as varchar(30)) + '. Instance is currently active on ' + @activenode + '. You will be getting another email in 5 mins with current status of databases. If you did not receive another email in 5mins then please connect to the server and check Sql server agent status / health check. Thanks. '    
    

EXEC  WPDMD551.master.dbo.xp_smtp_sendmail @FROM = 'test@test.com',  
     -- @FROM_NAME = @From_Name,  
      @TO = 'test1@test.com',  
      -- @replyto = @Reply_Address,  
      -- @CC = @Copy_Address,  
      -- @priority = @Priority,  
      @subject = @msgg,  
      @type = 'text/plain',  
      @message = @msg,
  -- @attachments = 'T:\job_output.txt',  
@SERVER     	= N'111.111.111.111'


EXEC msdb..sp_start_job 'DB Status after Sql Restart'

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

exec sp_procoption N'sp_SqlServerRestartNotificationMail', N'startup', N'true'
GO