How to send Email From Dataabse using Transact-SQL Part 1

home / software / tips and tricks / How to send Email From Dataabse using Transact-SQL Part 1

How to send Email From Dataabse using Transact-SQL Part 1

Category: SQL Server
view more software Tips and Tricks

Updated: 12/18/2011 08:12 AM
Author: Shiju Mathews

Status: Resolved.

sp_send_dbmail - will send email from database to the specified recipients. The message may include a query result set, file attachments, or both. When mail is successfully placed in the Database Mail queue, sp_send_dbmail returns the mailitem_id of the message. This stored procedure is in the msdb database.

But before sending database emails we need to Configure Database Mail on the SQL server

Then use the built in function

sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
[ , [ @recipients = ] 'recipients [ ; ...n ]' ]
[ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
[ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
[ , [ @from_address = ] 'from_address' ]
[ , [ @reply_to = ] 'reply_to' ] [ , [ @subject = ] 'subject' ] [ , [ @body = ] 'body' ] [ , [ @body_format = ] 'body_format' ]
[ , [ @importance = ] 'importance' ]
[ , [ @sensitivity = ] 'sensitivity' ]
[ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
[ , [ @query = ] 'query' ]
[ , [ @execute_query_database = ] 'execute_query_database' ]
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
[ , [ @query_attachment_filename = ] query_attachment_filename ]
[ , [ @query_result_header = ] query_result_header ]
[ , [ @query_result_width = ] query_result_width ]
[ , [ @query_result_separator = ] 'query_result_separator' ]
[ , [ @exclude_query_output = ] exclude_query_output ]
[ , [ @append_query_error = ] append_query_error ]
[ , [ @query_no_truncate = ] query_no_truncate ]
[ , [ @query_result_no_padding = ] @query_result_no_padding ] [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

[ @profile_name= ] 'profile_name'

Is the name of the profile to send the message from. The profile_name is of type sysname, with a default of NULL. The profile_name must be the name of an existing Database Mail profile. When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user. If the user does not have a default private profile, sp_send_dbmail uses the default public profile for the msdb database. If the user does not have a default private profile and there is no default public profile for the database, @profile_name must be specified.

[ @recipients= ] 'recipients'

Is a semicolon-delimited list of e-mail addresses to send the message to. The recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.

[ @copy_recipients= ] 'copy_recipients'

Is a semicolon-delimited list of e-mail addresses to carbon copy the message to. The copy recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.

[ @blind_copy_recipients= ] 'blind_copy_recipients'

Is a semicolon-delimited list of e-mail addresses to blind carbon copy the message to. The blind copy recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.

[ @from_address= ] 'from_address'

Is the value of the 'from address' of the email message. This is an optional parameter used to override the settings in the mail profile. This parameter is of type varchar(MAX). SMTP security settings determine if these overrides are accepted. If no parameter is specified, the default is NULL.

[ @reply_to= ] 'reply_to'

Is the value of the 'reply to address' of the email message. It accepts only one email address as a valid value. This is an optional parameter used to override the settings in the mail profile. This parameter is of type varchar(MAX). SMTP secuirty settings determine if these overrides are accepted. If no parameter is specified, the default is NULL.

[ @subject= ] 'subject'

Is the subject of the e-mail message. The subject is of type nvarchar(255). If no subject is specified, the default is 'SQL Server Message'.

Indian Resources by Category

Indian Grocery Stores

Indian Jewelry Stores

Indian Hindu Temple

alt="Add your business to our listing Free"

Software Resources

Tips and Tricks

Interview Questions for C#

Interview Questions for ASP.Net

Interview Questions for SQL Server