SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Set up Database Mail on SQL Server 2005 and SQL Server 2008

01 January,2011 by Jack Vamvas

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 = '[email protected]';

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'[email protected]',

@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'[email protected]',

@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'[email protected]',

@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


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer