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
