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 = 'KadetEmailAlertAccount',
@description = 'Mail account for sending email alert for Kadet.',
@email_address = 'prachi_purwar@keane.com',
@replyto_address = 'prachi_purwar@keane.com',
@display_name = 'Prachi Purwar',
@mailserver_name = 'MAIL101.KDS.KEANE.COM'



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



-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'KadetEmailAlertProfile',
@account_name = 'KadetEmailAlertAccount',
@sequence_number = 1



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



-- 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 = 'KadetEmailAlertProfile',
@recipients='emailid1@yahoo.com;emailid2@yahoo.com',
@body = 'Hello',
@body_format = 'TEXT',
@subject = 'Wishing'


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: