Tuesday 14 July 2015

Standardizing DBMAIL Profile & Account Setup for Notification

Copy the script to SSMS
Change the parameter values mentioned in the procedure argument lists
Run the procedure.
Once its successfully setup, you'll receive an alert.
 
USE [db_maint]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---------------------------------------------------------------------------------------------------------------------
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'usp_dbMAilAccount')
                    AND type =  N'P')

DROP PROCEDURE [usp_dbMAilAccount];
GO
---------------------------------------------------------------------------------------------------------------------
Create Proc usp_dbMAilAccount (@profile_name nvarchar(400) = 'DBMail_Global_Profile', --newly added.
                                  @account_name nvarchar(400) = 'DBMail_Account', --newly added.
                                 -- @email_address nvarchar(400) = 'MSSQL_DB_Mail@mail.com',
                                  @display_name nvarchar(400) = 'DBMail',
                                  @description nvarchar(100) = 'Database Mail Account',
                                  @mailserver_name nvarchar(400) = 'mail_server_name',
                                  @mailserver_type nvarchar(100) = 'SMTP',
                                  @replyto_address nvarchar(100) = NULL,
                                  @port INT = 25,
                                  @recipients nvarchar(500) = 'youremail@mail.com',
                                  --@recipients nvarchar(500) = 'xyz@mail.com',
                                  @Option nvarchar(5) = 'C')

as
--------------------------------------------------------------------------------------------------------------------
/*                   
                    Purpose: The purpose of this procedure is to create / setup/ delete/ update database mail account.
            
                             First it will cleanup all existing accounts and followed by setting up the fresh accounts.
----------------------------------------------------------------------------------------------------------------------
                    **RUN :
                            -- To Create the DB mail account
                            EXEC usp_dbMAilAccount @recipients = 'xyz@mail.com'

                            -- To Delete the DB mail account
                            EXEC usp_dbMAilAccount @Option ='C'
----------------------------------------------------------------------------------------------------------

Important Procedures: 1. sysmail_add_profileaccount_sp
                      2. sysmail_help_principalprofile_sp
                      3. sp_send_dbmail
----------------------------------------------------------------------------------------------------------
EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;
---------------------------------------------------------------------------------------------------------------------

Key Procedures to Note & Remember:
1. sysmail_add_account_sp
2. sysmail_add_profile_sp
3. sysmail_add_profileaccount_sp
4. sysmail_add_principalprofile_sp
5. sp_send_dbmail
6. sysmail_delete_profileaccount_sp
7. sysmail_delete_profile_sp
8. sysmail_delete_account_sp
------------------------------------------------------------------------
--Profiles
SELECT * FROM msdb.dbo.sysmail_profile
------------------------------------------------------------------------------
--Accounts
SELECT * FROM msdb.dbo.sysmail_account
------------------------------------------------------------------------------
--Profile Accounts
select * from msdb.dbo.sysmail_profileaccount
------------------------------------------------------------------------------
--Principal Profile
select * from msdb.dbo.sysmail_principalprofile
------------------------------------------------------------------------------
--Mail Server
SELECT * FROM msdb.dbo.sysmail_server
SELECT * FROM msdb.dbo.sysmail_servertype
SELECT * FROM msdb.dbo.sysmail_configuration
------------------------------------------------------------------------------
--Email Sent Status
SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT * FROM msdb.dbo.sysmail_unsentitems
SELECT * FROM msdb.dbo.sysmail_faileditems
--------------------------------------------------------------------------------------------------------------------------------
--Email Status
SELECT SUBSTRING(fail.subject,1,25) AS 'Subject',       fail.mailitem_id,       LOG.descriptionFROM msdb.dbo.sysmail_event_log LOG
join msdb.dbo.sysmail_faileditems fail ON fail.mailitem_id = LOG.mailitem_id WHERE event_type = 'error'
--------------------------------------------------------------------------------------------------------------------------------
 --Mail Queues
EXEC msdb.dbo.sysmail_help_queue_sp
--------------------------------------------------------------------------------------------------------------------------------
--DB Mail Status
EXEC msdb.dbo.sysmail_help_status_sp
--------------------------------------------------------------------------------------------------------------------------------------   
Key Queries:

select * from msdb.dbo.sysmail_profileaccount
select * from msdb.dbo.sysmail_profile
select * from msdb.dbo.sysmail_account

--SELECT * FROM msdb.dbo.sysmail_profileaccount pa
--      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

----------------------------------------------------------------------------------------------------------------------------------------------
-- Delete Unsent Emails.
 SELECT * FROM msdb.dbo.sysmail_event_log;
-- To get number of unsent emails
select count(*) from msdb.dbo.sysmail_unsentitems;
-- remove all the unsent emails
delete from msdb.dbo.sysmail_unsentitems;

----------------------------------------------------------------------------------------------------------------------------------------------
-- Delete failed alerts older than 2 days.
DECLARE @Date datetime
SELECT @Date = DateAdd(dd,-2,Getdate())
SELECT @Date
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @Date , @sent_status = 'failed'
------------------------------------------------------------------------------------------------------------------------------------------
-- Here is how we will delete all events in the log older than two days
DECLARE @Date datetime
SELECT @Date = DateAdd(dd,-2,Getdate())

EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @Date
----------------------------------------------------------------------------------------------------------------------------------------------
*/
BEGIN
BEGIN TRY
SET NOCOUNT ON

--Is db mail enabled for use?
IF NOT EXISTS (SELECT value_in_use
               FROM sys.configurations
               WHERE name = 'Database Mail XPs'
               AND value_in_use = 1)
BEGIN
   EXEC ('sp_configure ''show advanced options'', 1')
   RECONFIGURE;
   EXEC ('sp_configure ''Database Mail XPs'', 1')
   RECONFIGURE;
   EXEC ('sp_configure ''show advanced options'', 0')
   RECONFIGURE;
END
----------------------------------------------------------------------------------------------------------------------------
-- Generate logic to build account name.
declare @servername sysname = (select @@servername);
--declare @account_name nvarchar(1200) = 'DBMail' ;
-- declare @profile_name nvarchar(1200);
declare @email_address nvarchar(1200)= 'DBMAIL';

--DBMail_LVDB0001_SPE
--select @backup_path
          DECLARE @len   INT,
                  @i     INT,
                  @Left  nVARCHAR(4000),
                  @right nVARCHAR(4000),
                  @full_string nVARCHAR(4000);

          --SET @SERVERNAME = (SELECT @@servername);
          --select @backup_path AS [TLOG Backup PATH]
          --PRINT @backup_path
          IF @SERVERNAME LIKE '%\%'
            BEGIN
                SET @Left= (SELECT LEFT(@SERVERNAME, Charindex('\', @SERVERNAME)
                                                     - 1))
                --SELECT @Left AS [DEFAULT SERVER NAME]
                SET @len = (SELECT Len(@SERVERNAME))
                --SELECT @LEN
                SET @right = (SELECT Substring(@SERVERNAME,
                                     Charindex ('\', @SERVERNAME) +
                                     1
                                     , @len))
             --SELECT @right AS [SQL INSTANCE NAME]
             SET @full_string = @Left + '_'+ @right;
                --PRint @full_string
            END
          ELSE
            BEGIN
                SELECT @left = @SERVERNAME;
                SET @full_string = @left;
                --PRint @full_string

            END

-- Build the final account name.
SET @email_address = @email_address + '_'+ @full_string+'@mail.com';
--Print @email_address

SET @display_name = @display_name + '_' + @full_string;
--Print @display_name

---------------------------------------------------------------------------------------------------------------------
--declare @ProfileID INT;

----Does the profile exist already?
--SELECT @ProfileID = p.profile_id
----SELECT p.name, *
--FROM msdb.dbo.sysmail_profileaccount pa
--JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--full outer JOIN msdb.dbo.sysmail_account a ON a.account_id=pa.account_id
--WHERE p.name = @profile_name;
---------------------------------------------------------------------------------------------------------------------
--SELECT * FROM msdb.dbo.sysmail_profileaccount pa
--      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

----------------------------------------------------------------------------------------------------------------------
-- Clean up the unnecesassary db accounts and profiles. We need one global profile to send email alerts. [Mandatory Step]

--IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_profileaccount pa       JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id WHERE       p.name = @ProfileName AND
--      a.name = @AccountName)
--BEGIN
--      PRINT 'Deleting Profile Account'
--      EXECUTE sysmail_delete_profileaccount_sp       @profile_name = @ProfileName,       @account_name = @AccountName
--END
-- IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_profile p WHERE p.name = @ProfileName)
--BEGIN
--      PRINT 'Deleting Profile.'      EXECUTE sysmail_delete_profile_sp       @profile_name = @ProfileName
--END
-- IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_account a WHERE a.name = @AccountName)
--BEGIN
--      PRINT 'Deleting Account.'        EXECUTE sysmail_delete_account_sp       @account_name = @AccountName
--END

---------------------------------------------------------------------------------------------------------------------------------------
select * from msdb.dbo.sysmail_profile
select * from msdb..sysmail_profileaccount
select * from msdb.dbo.sysmail_account
---------------------------------------------------------------------------------------------------------------------------------------

create table #temp2 (profile_id INT, account_id INT, Profile_name nvarchar(1000), account_name nvarchar(1000), display_name nvarchar(1000), last_modified_datetime nvarchar(1000), last_modified_user nvarchar(1000))

INSERT INTO #temp2
SELECT p.profile_id, a.account_id, p.name,a.name, a.display_name,a.last_mod_datetime,a.last_mod_user
    FROM msdb.dbo.sysmail_profileaccount pa
      full outer JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
     full outer JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

--select * from #temp2

declare @profile_id3 INT, @Profile_name3 nvarchar(1000),@account_name3 nvarchar(1000);

select @profile_id3 = profile_id from #temp2;

select @Profile_name3 = Profile_name from #temp2;

select @account_name3 = account_name from  #temp2;

IF ((@profile_id3 IS NULL) and (@Profile_name3 IS NULL))
BEGIN
 
        DELETE FROM msdb.dbo.sysmail_account
        WHERE name = @account_name3

  --   PRINT 'Deleting Account, when there''s NO Profile Available (i.e. PRofile_ID and Profile_NAme Both NULL) := ' + @account_name3;
     --EXECUTE msdb..sysmail_delete_account_sp       @account_name = @account_name3;  

--declare @cmd2 nvarchar(4000)
--if not exists ( select d.is_encrypted, d.name, k.* from sys.dm_database_encryption_keys k inner join sys.databases d on d.database_id = k.database_id
--                where d.name = @db_nm and is_encrypted = 1)
--begin
--    select @cmd2 = 'USE ' + quotename(@db_nm) + ';' + CHAR(13)+
--        'CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE ' + quotename(@cert_nm) + ';'
  
--  Print @cmd2
--   EXEC sp_executesql @cmd2;
--END
--ELSE
--BEGIN
-- PRINT 'DATABASE ENCRYPTION KEY Already Exists Against the Database:= ' + @db_nm
--END

END
--select * from #temp2

drop table #temp2
------------------------------------------------------------------------------------------------------------------------------------------
create table #temp (profile_id INT, account_id INT, Profile_name nvarchar(1000), account_name nvarchar(1000), display_name nvarchar(1000), last_modified_datetime nvarchar(1000), last_modified_user nvarchar(1000))

INSERT INTO #temp
SELECT p.profile_id, a.account_id, p.name,a.name, a.display_name,a.last_mod_datetime,a.last_mod_user
    FROM msdb.dbo.sysmail_profileaccount pa
      full outer JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
     full outer JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
--SELECT p.profile_id, a.account_id, p.name as 'Profile_name' ,a.name as 'account_name', a.display_name,a.last_mod_datetime,a.last_mod_user
--    FROM msdb.dbo.sysmail_profileaccount pa
--      inner JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--     full outer JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
    --where p.profile_id IS NOT NULL and a.account_id IS NOT NULL;

--select * from #temp

---------------------------------------------------------------------------------------------------------------------------------------
declare @profile_id1 INT, @account_id1 INT, @Profile_name1 nvarchar(1000), @account_name1 nvarchar(1000);

DECLARE Cur1 CURSOR FOR
SELECT profile_id, account_id , Profile_name , account_name FROM #temp

OPEN Cur1;
FETCH NEXT FROM Cur1 INTO @profile_id1 ,  @account_id1, @Profile_name1, @account_name1;

WHILE @@FETCH_STATUS = 0
   BEGIN
   
     --print @profile_id1
     --print @account_id1
---------------------------------------------------------------------------------------------------------------------------------------
--IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_profileaccount pa       JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id WHERE       p.name = @ProfileName AND
--      a.name = @AccountName)
--BEGIN
--      PRINT 'Deleting Profile Account'
--      EXECUTE sysmail_delete_profileaccount_sp       @profile_name = @ProfileName,       @account_name = @AccountName
--END
-- IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_profile p WHERE p.name = @ProfileName)
--BEGIN
--      PRINT 'Deleting Profile.'      EXECUTE sysmail_delete_profile_sp       @profile_name = @ProfileName
--END
-- IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_account a WHERE a.name = @AccountName)
--BEGIN
--      PRINT 'Deleting Account.'        EXECUTE sysmail_delete_account_sp       @account_name = @AccountName
--END
---------------------------------------------------------------------------------------------------------------------------------------
     --Delete from msdb.dbo.sysmail_profileaccount
     --where profile_id = @profile_id1 and account_id = @account_id1;

      IF EXISTS(
        SELECT * FROM msdb.dbo.sysmail_profileaccount pa       JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
              JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id WHERE       p.name = @Profile_name1 AND
              a.name = @account_name1)
        BEGIN

      PRINT 'Deleting Profile Account:= ' + @Profile_name1;
      EXECUTE msdb..sysmail_delete_profileaccount_sp   @profile_name = @Profile_name1,  @account_name = @account_name1;

      END
 
     --DELETE FROM msdb.dbo.sysmail_profile
     --where profile_id = @profile_id

       IF EXISTS(
        SELECT * FROM msdb.dbo.sysmail_profile p WHERE p.name = @Profile_name1)
        BEGIN

      PRINT 'Deleting Profile:= ' + @Profile_name1   
      EXECUTE msdb..sysmail_delete_profile_sp       @profile_name = @Profile_name1;
      END

     --DELETE FROM msdb.dbo.sysmail_account
     --WHERE account_id  = @account_id1;

    IF EXISTS(
    SELECT * FROM msdb.dbo.sysmail_account a WHERE a.name = @account_name1)
    BEGIN

     PRINT 'Deleting Account:= ' + @account_name1     
     EXECUTE msdb..sysmail_delete_account_sp       @account_name = @account_name1;
     END

FETCH NEXT FROM Cur1 INTO @profile_id1 ,  @account_id1, @Profile_name1, @account_name1;
   END;

CLOSE Cur1;
DEALLOCATE Cur1;

drop table #temp
---------------------------------------------------------------------------------------------------------------------
create table #temp1 (profile_id INT, account_id INT, Profile_name nvarchar(1000), account_name nvarchar(1000), display_name nvarchar(1000), last_modified_datetime nvarchar(1000), last_modified_user nvarchar(1000))

INSERT INTO #temp1
SELECT p.profile_id, a.account_id, p.name,a.name, a.display_name,a.last_mod_datetime,a.last_mod_user
    FROM msdb.dbo.sysmail_profileaccount pa
      full outer JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
     full outer JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

--select * from #temp1

/*
declare @profile_id2 INT, @Profile_name2 nvarchar(1000);

select @profile_id2 = profile_id from #temp1;

select @Profile_name2 = Profile_name from #temp1;

IF (@profile_id2 IS NULL) and (@Profile_name2 IS NULL)
BEGIN
 
     PRINT 'Deleting Account, when there''s NO Profile Available (i.e. PRofile_ID and Profile_NAme Both NULL) := ' + @account_name1     
     EXECUTE msdb..sysmail_delete_account_sp       @account_name = @account_name1;  
END

*/

drop table #temp1
-----------------------------------------------------------------------------------------------------------------------------
IF @Option = 'C'
BEGIN

IF exists (select * from msdb.dbo.sysmail_account where name = @account_name)
BEGIN
PRint 'Database Mail Account ' + quotename (@account_name) + ' is already setup in the Server:= ' + quotename(@servername);
Print 'NOT Allowed to Create DB Mail Account Multiple Time.'

--select * from msdb.dbo.sysmail_account;

--select * from msdb.dbo.sysmail_account where name = @account_name;

END

ELSE IF NOT exists (select * from msdb.dbo.sysmail_account where name = @account_name)
BEGIN
PRINT 'Creating Account: ' + @account_name;

--select * from msdb.dbo.sysmail_account where name = @account_name
-----------------------------------------------------------------------------------------------------------------------------------------
-- To setup database mail.

-- Steps1:  EXECUTE msdb.dbo.sysmail_add_profile_sp
-- step 2:  EXECUTE msdb.dbo.sysmail_update_account_sp
-- Step 3:  EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
-- Step 4:  EXECUTE msdb.dbo.sysmail_add_account_sp
-- Step 5:  EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
-----------------------------------------------------------------------------------------------

--EXECUTE msdb.dbo.sysmail_add_profile_sp
--       @profile_name = @profile_name,
--       @description = @description;

-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<              >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- STEP 1 Create Email Account in SQL Server:
-----------------------------------------------------------------------------------------------------------------
-- Setup mail account
declare @account_id int
execute msdb.dbo.sysmail_add_account_sp
@account_name= @account_name,
@email_address= @email_address,
@display_name= @display_name,
@description = @description,
@mailserver_name= @mailserver_name,
@mailserver_type = @mailserver_type,
@port = @port,
@username=null,
@password=null,
@enable_ssl=0,
--@username=null,
--@password=null,
@use_default_credentials=1,
@account_id=@account_id output;
---------------------------------------------------------------------------------------------------------------------
--select @account_id
--select * from msdb.dbo.sysmail_account

------------------------------------------------------------------------------
-- to update the above details to add user_name, pswd = NULL, use the updte script.
--declare @account_id int
execute msdb.dbo.sysmail_update_account_sp
@account_id= @account_id, -- Pass the correct account ID.
@account_name=@account_name,
@email_address=@email_address,
@display_name=@display_name,
@description=@description,
@replyto_address= @replyto_address,
@mailserver_name=@mailserver_name,
@mailserver_type=@mailserver_type,
@port=@port,
@username=null,
@password=null,
@use_default_credentials=1,
@enable_ssl=0;
------------------------------------------------------------------------------
-- STEP 2 Creat Email Profile in SQL Server:
-- CREATE PROFILE

SET @description = 'Database Mail Profile';
EXECUTE msdb.dbo.sysmail_add_profile_sp
       @profile_name = @profile_name,
       @description = @description;
------------------------------------------------------------------------------
-- Set the New Profile as the Default
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = @profile_name,
    @principal_name = 'public',
    @is_default = 1 ;
---------------------------------------------------------------------------------------------------------------------
-- STEP 3 Link email account to email profile:
-- LINK ACCOUNT TOPROFILE
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
@profile_name = @profile_name,
@account_name = @account_name,
@sequence_number = 1
---------------------------------------------------------------------------------------------------------------------
PRint 'Database Mail Account Has been Setup ' + quotename (@account_name) + ' is already setup in the Server:= ' + quotename(@servername);
---------------------------------------------------------------------------------------------------------------------------------------

--Create primary profile; we always want a profile for DBOPS team.
--IF @ProfileID IS NULL
--BEGIN
--      PRINT 'Creating profile: ' + @account_name;
--      EXEC msdb.dbo.sysmail_add_profile_sp
--            @profile_name = @account_name,
--            @description = @description,
--            @profile_id = @ProfileID output

--Print @ProfileID
---------------------------------------------------------------------------------------------------

   --IF (@profile_id IS NOT NULL) -- use id
   --BEGIN
      --SELECT @profileid = profile_id FROM msdb.dbo.sysmail_profile WHERE profile_id=@profile_id
      --IF (@profileid IS NULL) -- id is invalid
      --BEGIN
      --   RAISERROR(14606, -1, -1, 'profile')
      --   RETURN(3)
      --END    

-- SET @ProfileName = (select name from msdb..sysmail_profile where profile_id = @cnt)

--SET @Sub = 'Testing Email From ' + quotename(@Server) + ' from profile : ' + @ProfileName

--declare @Profile_Name1 nvarchar(400);
/*
SELECT @profileid = profile_id FROM msdb.dbo.sysmail_profile WHERE name like '%DBMail_%';

select @Profile_Name = Name FROM msdb.dbo.sysmail_profile WHERE name like '%DBMail_%';

 IF (@Profile_Name1 is not null)
 BEGIN

       Print @profileid;
      Print @Profile_Name

      -- give everybody access to use this profile and make it the default
      EXEC msdb.dbo.sysmail_add_principalprofile_sp
            @profile_id = @profileid,
            @principal_name = 'public',
            @is_default = 1

END
*/
---------------------------------------------------------------------------------------------------------------------------------------
END

/*
ELSE IF @Option = 'D'
BEGIN
Print 'delete'
IF exists (select * from msdb.dbo.sysmail_account where name = @account_name)
BEGIN

print 'db mail account ' + quotename (@account_name) + ' exists. Can be Deleted.'
-- Delete the mail account:=
--delete from msdb.dbo.sysmail_account
--where name = @account_name;

    Print @profile_id
    Print @account_id1
    Print @account_name;

     Delete from msdb.dbo.sysmail_profileaccount
     where profile_id = @profile_id and account_id = @account_id1;

      --PRINT 'Deleting Profile.'      EXECUTE msdb..sysmail_delete_profile_sp       @profile_name = @Profile_name;

     DELETE FROM msdb.dbo.sysmail_profile
     where profile_id = @profile_id

     --PRINT 'Deleting Account.'        EXECUTE msdb..sysmail_delete_account_sp       @account_name = @account_name1;

     DELETE FROM msdb.dbo.sysmail_account
     WHERE account_id  = @account_name;


print quotename (@account_name) + ' is successfully Deleted From the Server:= ' + quotename(@servername);

END


ELSE IF NOT exists (select * from msdb.dbo.sysmail_account where name = @account_name)
BEGIN


Print 'db mail account ' + quotename (@account_name) + ' deleted already From the Server:= ' + quotename(@servername);
Print 'Can''t be deleted again.'
END
END
*/
---------------------------------------------------------------------------------------------------------------------
--- Clean Up All Other DB Mail Accounts Other than Server DB Mail Account.

select * from msdb.dbo.sysmail_profileaccount
select * from msdb.dbo.sysmail_profile
select * from msdb.dbo.sysmail_account
---------------------------------------------------------------------------------------------------------------------
SELECT * FROM msdb.dbo.sysmail_profileaccount pa
      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
---------------------------------------------------------------------------------------------------------------------
/*
IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profileaccount pa
      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE
      p.name != @account_name AND
      a.name != @account_name)
BEGIN

--declare @count INT = 1, @tot INT;

--SET @tot = (SELECT count(*) FROM msdb.dbo.sysmail_profileaccount pa
--      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
--WHERE
--      p.name != @account_name AND
--      a.name != @account_name)
------------------------------------------------------------------------------------------------------

create table #temp (profile_id INT, account_id INT, Profile_name nvarchar(1000), display_name nvarchar(1000), last_modified_datetime nvarchar(1000), last_modified_user nvarchar(1000))

INSERT INTO #temp
SELECT * FROM msdb.dbo.sysmail_profileaccount pa
      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id


select * from #temp

--WHILE(@count <= @tot)
 --BEGIN
      PRINT 'Deleting Profile Account';
     
--   IF (@profileid IS NOT NULL AND @accountid IS NOT NULL) -- both parameters supplied for deletion

      DELETE FROM msdb.dbo.sysmail_profileaccount

      WHERE profile_id=@profileid AND account_id=@accountid

      select * from msdb.dbo.sysmail_profileaccount;


      select * from msdb.dbo.sysmail_profile

   --ELSE IF (@profileid IS NOT NULL) -- profile id is supplied

   --   DELETE FROM msdb.dbo.sysmail_profileaccount

   --   WHERE profile_id=@profileid



   --ELSE IF (@accountid IS NOT NULL) -- account id is supplied

   --   DELETE FROM msdb.dbo.sysmail_profileaccount

   --   WHERE account_id=@accountid



   --ELSE -- no parameters are supplied for deletion

   --BEGIN

   --   RAISERROR(14608, -1, -1, 'profile', 'account') 

   --   RETURN(3)  

   --END



      --EXECUTE msdb..sysmail_delete_profileaccount_sp
      --@profile_name = @account_name,
      --@account_name = @account_name
--END
------------------------------------------------------------------------------------------------------
IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profile p
WHERE p.name = @account_name)
BEGIN
      PRINT 'Deleting Profile.'
     
     DELETE FROM msdb.dbo.sysmail_profile
     WHERE name ! = @account_name

      --EXECUTE msdb..sysmail_delete_profile_sp
      --@profile_name = @account_name
END

IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_account a
WHERE a.name = @account_name)
BEGIN
      PRINT 'Deleting Account.'

         DELETE FROM msdb.dbo.sysmail_account

        WHERE name != @account_name


      --EXECUTE msdb..sysmail_delete_account_sp
      --@account_name = @account_name
END

--END
END
*/
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Test whether the dbmail account has been setup correctly. It should have one entry, so recipient should receive only a single email alert from each server.

DECLARE
 @Total int
 ,@cnt int
 ,@ProfileName nvarchar(1000)
 --,@AccountName nvarchar(4000)
 ,@Server varchar(500)
 ,@Sub nvarchar(4000)
 ,@body nvarchar(4000),
 @cnt_Accounts INT;

SET @Total = (SELECT MAX(profile_id) FROM msdb..sysmail_profile)
 SET @cnt = 1

--SET @cnt_Accounts = (select count(*) from msdb..sysmail_account)

--DECLARE @Table TABLE (account_name varchar(400),account_id INT)

--INSERT INTO @TABLE
--select name, account_id from msdb..sysmail_account
--Order by Account_id

--select * from @TABLE

DECLARE @tableHTML  NVARCHAR(4000) ;
-------------------------------------------------------
--select * from msdb.dbo.sysmail_profileaccount
--select * from msdb.dbo.sysmail_profile
--select * from msdb.dbo.sysmail_account
------------------------------------------------------

SET @tableHTML =
    N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd)    { background-color:#eee; }
tr:nth-child(even)    { background-color:#fff; }   
</style>'
+


    N'<H1><body><font color = red> Global Mail Profile & Accounts Configured (Server :  ' + @full_string + ')' + ' </font></H1>' + '<BR>' + '<BR>' +
   
    N'<table id="box-table"  border="1">' +
    N'<tr><th>Profile_ID</th>'+
    N'<th>Profile_Name</th>'+
    N'<th>Profile_Description</th>'+
    N'<th>Last_Mod_Datetime</th>'+
    N'<th>Last_Mod_User</th></tr>' +
    CAST ( ( SELECT td = profile_id, '',
                    td = name, '',
                    td = [description], '',
                    td = convert(varchar(19), [last_mod_datetime],121) , '',
                    td = [last_mod_user], ''
              FROM msdb.dbo.sysmail_profile
              ORDER BY profile_id ASC
             FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
        N'</table>'

        +
    '<BR>' + '<BR>' + '<BR>' +
    N'<table id="box-table"  border="1">' +
    N'<tr><th>Account_ID</th>'+
    N'<th>Account_Name</th>'+
    N'<th>Account_Description</th>'+
    N'<th>Email_Address</th>'+
    N'<th>Display_Name</th>'+
    N'<th>Last_Mod_Datetime</th>' +
    N'<th>Last_Mod_User</th></tr>' +
 CAST ( ( SELECT td = account_id, '',
                    td = name, '',
                    td = [description], '',
                    td = email_address, '',
                    td = display_name, '' ,
                    td = convert(varchar(19), [last_mod_datetime],121) , '',
                    td = last_mod_user, ''
              FROM msdb.dbo.sysmail_account
              ORDER BY account_id ASC
             FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>';

select @tableHTML;


------------------------------------------------------------------------------------------------------
SET @Server = @@SERVERNAME

WHILE(@cnt <= @Total)
 BEGIN
 SET @ProfileName = (select name from msdb..sysmail_profile where profile_id = @cnt)

 --SET @AccountName = (select * from msdb..sysmail_account where @account_id = @cnt)

SET @Sub = 'Testing Email From ' + quotename(@Server) + ' from profile : ' + @ProfileName;

 IF (@ProfileName is not null)
 BEGIN

--Print '------------------------------'
--PRINT @ProfileName
--Print @AccountName
--PRINT @Sub
--Print '------------------------------'

----------------------------------------------------------------------------------------------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
   @recipients = @recipients
  , @subject = @Sub
  , @body = @tableHTML
  , @profile_name = @ProfileName
 , @body_format = 'HTML' ;
 END

SET @cnt = @cnt + 1
END

END
------------------------------------------------------------------------------------------------------------------------------------
SET NOCOUNT OFF
END try

    BEGIN catch
        DECLARE @ErrorNumber INT;
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;
        DECLARE @ErrorLine INT;
        DECLARE @ErrorProcedure NVARCHAR(4000);
        DECLARE @ErrorMessage NVARCHAR(4000);

        SELECT @ErrorNumber = Error_number(),
               @ErrorSeverity = Error_severity(),
               @ErrorState = Error_state(),
               @ErrorLine = Error_line(),
               @ErrorProcedure = Error_procedure();

        SELECT @ErrorMessage =
               N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
               + 'Message: ' + Error_message();

        SELECT @ErrorMessage AS [Error_Message];

        SELECT @ErrorProcedure AS [Error_Procedure];

        PRINT 'Error '
              + CONVERT(VARCHAR(50), Error_number())
              + ', Severity '
              + CONVERT(VARCHAR(5), Error_severity())
              + ', State '
              + CONVERT(VARCHAR(5), Error_state())
              + ', Procedure '
              + Isnull(Error_procedure(), '-') + ', Line '
              + CONVERT(VARCHAR(5), Error_line());

        PRINT Error_message();
    END catch

    SET nocount OFF
END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
 

No comments:

Post a Comment