Wednesday, May 4, 2011

Sending E-Mail through SQL Server stored procedures

To send Database Mail using stored procedures, you must be a member of the DatabaseMailUserRole database role in the msdb database.

Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database.

Go to Surface Area configuration for features and expand Database engine option and select DatabaseMail option and checked Enable Database mail Stored procedure and click OK or Apply button.

The code sample below sends the e-mail through stored procedures:


-- Creates a new Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DatabaseEmailAlertAccount',
@description = 'Mail account for sending email alert for Viral.',
@email_address = 'mymail@gmai.com',
@replyto_address = 'mymail@gmai.com',
@display_name = 'My Display Name',
@mailserver_name = 'mail.server.name'
Go

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DatabaseEmailAlertProfile',
@description = 'Profile used for sending email alert for Viral.'
Go

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DatabaseEmailAlertProfile',
@account_name = 'DatabaseEmailAlertAccount',
@sequence_number = 1
Go

-- Grant access to the profile to all users in the msdb database
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'DatabaseEmailAlertProfile',
@principal_name = 'public',
@is_default = 1
Go

-- Send the mail to recipients
-- @body_format can have any one value TEXT or HTML
-- @copy_recipients as CC and @blind_copy_recipients as BCC are optional parameters
-- @file_attachments as file attachement is optional parameter
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DatabaseEmailAlertProfile',
@recipients='mail1@xxx.com;mail2@yyy.com;mail3@zzz.com',
@body = 'Hello',
@body_format = 'TEXT',
@subject = 'Viral test Database mail'
Go

No comments: