DB Mail Configuration

Database Mail Configuration in SQL Server 2005

-- Enabling Database Mail
sp_configure 'show advanced', 1;
GO
RECONFIGURE;
GO

sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE;
GO

-- Checking Database Mail is running or not
EXEC msdb.dbo.sysmail_help_status_sp;

-- Starting Database Mail
EXEC msdb.dbo.sysmail_start_sp;

-- Stopping Database Mail
EXEC msdb.dbo.sysmail_stop_sp;

-- Checking Mail Queue Status
-- The state of mail queue should be 'RECEIVES_OCCURRING'
-- The column length gives the number of mails in the queue
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';

-- Adding Database Mail Account
-- Add valid smtp mailserver_name
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Production Database Administration Profile',
@description = 'Administrative e-mail account',
@email_address = 'dfsafasfddffasd@yahoo.com',
@display_name = 'Automated Admin Mailer',
@mailserver_name = 'mail.yahoo.com' ;

-- Adding Database Mail Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Production Database Administration Profile',
@description = 'Administrative mail profile' ;

-- Adding Database Mail Account to Profile
/* Add unique integer Sequence number for respective account in a profile. Mail is sent through the account based on the order of sequence number. */
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Production Database Administration Profile',
@account_name = 'Production Database Administration Account',
@sequence_number = 1 ;

-- Grant access to the profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Production Database Administration Profile',
@principal_name = 'public',
@is_default = 1

-- Listing all Database Mail Accounts
EXECUTE msdb.dbo.sysmail_help_account_sp ;

-- Listing all Database Mail Profiles
EXECUTE msdb.dbo.sysmail_help_profile_sp;

-- Listing all Database Mail Profiles with their respective Accounts
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

--To find out the profile name
SELECT [profile_id]
,[name]
,[description]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_profile]

-- Deleting Database Mail Account
-- Pass Account name or account id as the parameter
EXECUTE msdb.dbo.sysmail_delete_account_sp
@account_name = 'Production Database Administration Account' ;

EXECUTE msdb.dbo.sysmail_delete_account_sp
@account_id = 1;

-- Deleting Database Mail Profile
-- Pass Profile name or profile id as the parameter
EXECUTE msdb.dbo.sysmail_delete_profile_sp
@profile_name = 'Production Database Administration Profile' ;

EXECUTE msdb.dbo.sysmail_delete_profile_sp
@profile_id = 1;

-- Deleting Database Mail Account from Profile
EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = 'Production Database Administration Profile',
@account_name = 'Production Database Administration Account' ;

-- Updating Database Mail Account
EXECUTE msdb.dbo.sysmail_update_account_sp
@account_id = 1,
@account_name = 'DBA',
@email_address = 'dsafffffewew@yahoo.com',
@display_name = 'Admin';

-- Updating Database Mail Account
EXECUTE msdb.dbo.sysmail_update_profile_sp
@profile_id = 1,
@profile_name = 'DBA'

-- Sending Database Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Production Database Administration Profile',
@recipients = 'dafskjkj@yahoo.com',
@copy_recipients = 'Abcjkdsfah@yahoo.com',
@blind_copy_recipients ='dalasldkfa@yahoo.com',
@body_format = 'TEXT',
@body = 'Test Email',
@subject = 'Automated Test Mail';

-- Send file attachments
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Production Database Administration Profile',
@recipients = 'dsafsdfasdf@yahoo.com',
@file_attachments = 'D:\File1.txt;D:\File2.txt'
@body = 'Test File Attachments',
@subject = 'Files Attached';

-- Send mail with query results
/*Specify value 0 for @attach_query_result_as_file for including the contents in body and specify value 1 to send the results as attachment*/
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Production Database Administration Profile',
@recipients = 'asdfjjhdsaf@yahoo.com',
@query = 'SELECT COUNT(*) FROM msdb.dbo.DTA_input' ,
@subject = 'Imported Records Count',
@attach_query_result_as_file = 1 ;

-- Sending HTML Mail
DECLARE @tableHTML VARCHAR(MAX) ;

SET @tableHTML =
'

HTML Mail

' +
'' +
'' +
'

Table Header

' ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Production Database Administration Profile',
@recipients='fsdfgdsgds@yahoo.com',
@subject = 'HTML mail',
@body = @tableHTML,
@body_format = 'HTML' ;

-- Checking Mail Delivery status
-- Delivery Status can be viewed from column sent_status
-- Check all mails AND Clear histroy or allitems
select * from msdb.dbo.sysmail_allitems order by last_mod_date desc
-- DELETE from msdb.dbo.sysmail_allitems

-- Check sent mails and clear
select * from msdb.dbo.sysmail_sentitems order by last_mod_date desc
-- DELETE from msdb.dbo.sysmail_sentitems
-- Check failed mails
select * from msdb.dbo.sysmail_faileditems order by last_mod_date desc
-- If you want to clear run this script
-- DELETE from msdb.dbo.sysmail_faileditems
-- Checking Failed Mail Reason
-- Column Description returns the Failure reason
select * from msdb.dbo.sysmail_event_log where mailitem_id ='1507'
DELETE from msdb.dbo.sysmail_event_log

-- Monitoring Database Mail run msdb DB
SELECT * FROM msdb.dbo.sysmail_allitems where recipients='dsaffsdfds@yahoo.com'
--delete FROM msdb.dbo.sysmail_allitems where recipients='dsafsadf@yahoo.com'
SELECT * FROM msdb.dbo.sysmail_event_log
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT * FROM msdb.dbo.sysmail_faileditems
SELECT * FROM msdb.dbo. sysmail_mailattachments
SELECT count(*) FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT count(*) FROM msdb.dbo.sysmail_unsentitems
SELECT * FROM msdb.dbo.sysmail_unsentitems

-- Troubleshooting DB Mail --

/*
Msg 14650, Level 16, State 1, Procedure sp_send_dbmail, Line 72
Service Broker message delivery is not enabled in this database. Use the ALTER DATABASE statement to enable Service Broker message delivery.
*/
/* A value of 0 indicates that Service Broker message delivery is not activated in the msdb database */
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'
--- check members of the DatabaseMailUserRole
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole'
EXEC msdb.dbo.sysmail_help_principalprofile_sp
--- check status of Database Mail
EXECUTE msdb.dbo.sysmail_help_status_sp ;
---start Database Mail in a mail host database
EXECUTE msdb.dbo.sysmail_start_sp

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail'
-- Kill all connection to msdb database and run:
-- stop the agent service, run the query, and then restart the service.
ALTER DATABASE msdb SET ENABLE_BROKER

-- ALTER DATABASE MSDB SET NEW_BROKER

-- You can set is_broker_enabled column value to 1 by executing the following command
ALTER DATABASE Employee SET NEW_BROKER

ALTER DATABASE Employee SET ENABLE_BROKER

ALTER DATABASE Employee SET ERROR_BROKER_CONVERSATIONS

ALTER DATABASE Employee SET TRUSTWORTHY ON

ALTER DATABASE Employee SET DISABLE_BROKER