Agent Jobs

How To Configure SQL Database Mail With Only TSQL

SQL Database Mail sp_send_dbmail mid

Here’s some quick sql logic you can use to configure the SQL Database Mail aka sp_send_dbmail process.


-- configure sql database mail with the sql logic below.

-- enable sql database mail profile
exec master..sp_configure 'show advanced options',1
reconfigure;
exec master..sp_configure 'Database Mail XPs',1
reconfigure;
go

-- creating a profile
execute msdb.dbo.sysmail_add_profile_sp
	@profile_name		= 'SQLDatabaseMailProfile'
,	@description		= 'SQLDatabaseMail' ;

-- create a mail account.
execute msdb.dbo.sysmail_add_account_sp
	@account_name		= 'EmailAddressYouWantToAppearHere' <-- Does not have to be a real email aaddress.  Parameters are required for process, but are not needed to work.
,	@email_address		= 'EmailAddressYouWantToAppearHere'	<-- Does not have to be a real email aaddress.  Parameters are required for process, but are not needed to work.
,	@mailserver_name	= 'MySMTPServerNameGoesHere'  
--,	@port				= MyPort --optional
--,	@enable_ssl			= 1 --optional
--,	@username			='SQLDatabaseMailProfile' --optional
--,	@password			='MyPassword' --optional

-- adding the account to the profile
execute msdb.dbo.sysmail_add_profileaccount_sp
	@profile_name		= 'SQLDatabaseMailProfile'
,	@account_name		= 'sqldatabasemail@mydomain.com'
,	@sequence_number	= 1 ;

-- grant access to DatabaseMailUserRole
execute msdb.dbo.sysmail_add_principalprofile_sp
	@profile_name		= 'SQLDatabaseMailProfile'
,	@principal_id		= 0
,	@is_default			= 1 ;

-- send email.
EXEC msdb.dbo.sp_send_dbmail
	@profile_name		= 'SQLDatabaseMailProfile'
,	@recipients			= 'MyEmailAddress'  <-- Distribution group always preferred here.
,	@subject			= 'Database Mail Subject...'
,	@body				= 'Database Mail Body...';


-- check to see if mail is being qued up to be sent.  
-- several seconds after you see email sent to your inbox.
select * from msdb..sysmail_allitems 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s