SQL Database Mail , introduced  to SQL Server 2005 , allows more flexible approach to managing who sends emails and from what accounts. To Set up Database Mail , follow the steps below. These steps also work for SQL Server 2008

 Prior to implementing these scripts check that you have a valid SMTP server and valid security credential exist.

In summary , the Account holds the SMTP information , such as email address, mail server name, port number.

The Profile  references the account , with the potential benefit of allowing more than one account to be attached to an Account.

 For example , you could set up 2 accounts for  a Profile – so when an email every Account will be tried.

Part 1 – only use if Database Mail isn’t yet configured

Part 2 – Create account and Profile – change values on variables

Part 3 – Test email & monitoring

 /*PART1: enable if not already enabled- check with sp_configure*/

USE master;

go

SP_CONFIGURE 'show advanced options', 1;

go

RECONFIGURE;

go

SP_CONFIGURE 'Database Mail XPs',1;

go

RECONFIGURE;

go

/*PART2:start creating account and profile */

USE msdb

GO

DECLARE @profile_name NVARCHAR(255);

DECLARE @account_name NVARCHAR(255);

DECLARE @SMTP VARCHAR(255);

DECLARE @email_address NVARCHAR(128);

DECLARE @display_user NVARCHAR(128);

DECLARE @description NVARCHAR(256);

DECLARE @port_num  INT;

 

SET @profile_name = 'MyProfile1';

SET @account_name = 'System';

SET @SMTP = 'smtp.myserver.com';

SET @email_address = 'system@sqlserver-dba.com';

SET @display_user = 'SQL SERVER DB MAIL';

SET @description = 'System managed account';

SET @port_num = 25;

/*creates a new Database Mail Account re:SMTP account*/

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = @account_name,

@email_address = @email_address,

@display_name = @display_user,

@mailserver_name = @SMTP,

@description = @description,

@port = @port_num;

 

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = @profile_name;

 

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = @profile_name,

@account_name = @account_name,

@sequence_number = 1 ;

 

/*PART 3:this will allow a test email to be sent*/

 

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'JackVamvas@sqlserver-dba.com',

@body= 'Some text for body',

@subject = 'Some text for subject',

@profile_name = 'MyProfile1';

 

/*this will display emails sent

There is one row per each message processed.

*/

SELECT * FROM sysmail_allitems;

/* File Attachments

*/

The argument @file_attachments – will accept values in a semicolon-delimited list to and attach to a email message – with absolute paths.

Be careful of the seurity context used in Database Mail with attachments. In attempting to access a file to attach Database Mail will use the Windows Security Context of the current user. SQL Server Authenticated users will fail in attaching a file.  

An example of including of adding an attachment :

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'JackVamvas@sqlserver-dba.com',

@body= 'Some text for body',

@subject = 'Some text for subject',

@profile_name = 'MyProfile1',

@file_attachments = ’D:\myfiles\myfile.xls’;

/*Query Attachments

*/

Using @query and @attach_query_result_as_a_file will allow you to execute a query and attach the query as a file . You must use the value 1 , such as @attach_query_result_as_a_file = 1

Slightly adjusting the query from above :

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'JackVamvas@sqlserver-dba.com',

@body= 'Some text for body',

@subject = 'Some text for subject',

@profile_name = 'MyProfile1',

@query = ‘select name from sys.sysdatabases’,

@attach_query_result_as_file = 1;

 Read More

SQL Server – Send email using Powershell - SQL Server DBA

Send email from Powershell with attachment - SQL Server DBA

SQL Server - Sp_send_dbmail and bcc