Database Mail is a SQL Server component that
provides developers and administrators with an enterprise solution for sending
e-mail messages from the SQL Server Database Engine. While the primary use of Database
Mail is to send alert messages to administrators, it can also be used to send
the results of a query to end users, use it in Transact-SQL code, or even
include file attachments from any available resource on the network.

Configure Database Mail - (Database
Mail Configuration Wizard)

Before using Database Mail, several
configuration tasks need to be carried out. This section describes the steps
necessary to successfully configure Database Mail which includes verifying that
the SQL Server Agent is running, verifying that the Service Broker is enabled
for the msdb database, enabling the Database Mail feature, and finally,
creating an e-mail Profile and SMTP mail account using the Database Mail
Configuration Wizard.

Step 1: Verify
SQL Server Agent is running

Messages in Database Mail are sent by the SQL
Server Agent. If the Agent is not running, the messages will be accumulated in
the msdb database and sent when the Agent service gets started again.

Use the master..xp_servicecontrol system
stored procedure to check the status of the SQL Server Agent process. If the
Agent is stopped, you will want to start it in order to send messages through
Database Mail.

Step 2: Verify
Service Broker is enabled for the msdb Database

Database Mail relies on Service Broker for queuing
e-mail messages. If Database Mail is stopped or if Service Broker message
delivery is not activated in the msdb database, Database Mail queues
messages in the database but cannot deliver the messages. In this case, the
Service Broker messages remain in the Service Broker Mail queue. Service Broker
does not activate the external program, so there are no log entries in sysmail_event_log
and no updates to the item status in sysmail_allitems and the related
views.

Verify that Service Broker is enabled for the msdb
database by running the following query.

Is_broker_enabled will be 1
if Service Broker is enabled for the given database (msdb), otherwise it
will be 0.

If the Service Broker is disabled (is_broker_enabled = 0), run the following T-SQL to enable Service Broker for the msdb
database:

Step 3: Enable Database Mail

Database Mail relies on a number of internal stored procedures. To reduce
the surface area, these stored procedures are disabled on new installations of
SQL Server which makes Database Mail not active by default. Users must
explicitly enable these Database Mail stored procedures by using either one of the following three methods.

1.    
sp_configure Stored Procedure

To enable Database Mail using the sp_configure
stored procedure, run the following.

  USE master

Go

EXEC sp_configure 'show advanced options', 1

Go

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

RECONFIGURE

Go

Command(s) completed successfully.

EXEC sp_configure 'Database Mail XPs', 1

Go

Configuration option 'Database Mail XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.

RECONFIGURE

Go

Command(s) completed successfully.

EXEC sp_configure 'show advanced options', 0

Go

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

RECONFIGURE

Go

2.    
Surface Area Configuration Facet of
Policy-Based Management

To enable Database Mail using the Surface Area
Configuration Facet of Policy-Based Management, launch SQL Server Management
Studio, right-click on the server that you intend to enable the Database Mail
feature for and select Facets.

On the Facets dialog box, select Surface Area
Configuration from the Facet drop down list.

Locate DatabaseMailEnabled in the Facet
properties section and change its value from False to True and
then click the Ok button.

3.    
Database Mail Configuration Wizard

The third method that can be used to enable the
Database Mail feature is to do nothing here and wait until you run the Database
Mail Configuration Wizard The Database Mail Configuration Wizard is used to
create e-mail Profiles and SMTP Accounts; both of which are
required in order to use Database Mail to send messages. If the Database Mail features
in not enabled, the Database Mail Configuration Wizard
will prompt you to enable it when attempting to
create the e-mail Profile and SMTP account.

Create E-Mail Profile
and SMTP Account

After enabling the Database Mail feature, the next step is to use the
Database Mail Configuration Wizard to create an e-mail Profile and an SMTP
Account through which to send emails. To start the Database Mail
Configuration Wizard, in Object Explorer, expand the Management
folder in SQL Server Management Studio, right-click Database Mail, and
select Configure Database Mail.

This will bring up the Database Mail Configuration Wizard welcome screen.
Click Next to continue.

On the Select Configuration Task screen, select Set up Database
Mail by performing the following tasks:. Click Next to continue.

If you haven't
previously enabled Database Mail then you will receive a message box asking if
you wish to enable the Database Mail feature. Click yes to enable it.

Next, on the New Profile screen, provide an
e-mail Profile Name and an optional description of the profile, and then
select the Add button to configure an SMTP Account.

On the New Database Mail Account screen, provide the required
information for the outgoing mail server (SMTP). Fill in the Account Name,
Description (optional), Email Address, Display name, Reply Email (optional),
and SMTP Server Name and Port number. Then select the appropriate SMTP authentication
for your server and click next to continue.

This will return you to the New Profile screen. Click Next to
continue.

The Manage Profile Security screen allows you to set this profile as
either public or private. A private profile is accessible only to specific
users or roles. A public profile allows any user or role with access to the
mail host database (msdb) to send e-mail using this profile. Note that
in order to send Database Mail; users must also be an added to the DatabaseMailUsersRole
database role in the msdb database.

You can also specify that this profile is a default profile. In this
case, users or roles can send e-mail using the profile without explicitly
specifying the profile name. If the user or role sending the e-mail message has
a default private profile (use the Private Profiles tab on this screen),
Database Mail uses that profile. If the user or role has no default private
profile, sp_send_dbmail uses the default public profile for the msdb
database. If there is no default private profile for the user or role and no
default public profile for the database, sp_send_dbmail returns an
error. Only one profile can be marked as the default profile.

Click Next to continue.  

Use the Configure System Parameters screen to specify Database Mail
system parameters such as retry attempts or types of attachments that you do
not want to send out by including the extensions of the files (Prohibited
Attachment File Extensions). Click Next to continue.

Finally, review the list of steps the wizard is about to perform, and then
click Finish to execute these actions.

Verify that all actions completed successfully. Click Close to exit
the Database Mail Configuration Wizard.

Perform a quick test of the new e-mail Profile within SQL Server Management
Studio. Right-click Database Mail and select Send Test E-Mail....

Supply a recipient email address and click Send Test E-Mail.

Check that you received the test email and click Ok to close the
dialog box; otherwise click the Troubleshoot button to investigate any
errors. In addition to the Troubleshooting button, you can also review the
contents of msdb.dbo.sysmail_event_log.

  SELECT * FROM msdb.dbo.sysmail_event_log;

Go

Step 4: Send an HTML E-Mail Message

Using
below HTML format mail example we can send a table format data or list to
business people for fill the

Empty
row information via running SQL Job Scheduler based on table count.

Here
I have made a check point is variable @row_Count
will check the empty count based on query

and mail will send only if the count is greater than zero.

DECLARE @row_Count INT

SELECT @row_Count=
COUNT(*) FROM

(

 SELECT
ImageName,LITM,AN8,LabelType,Format FROM [LMS].[dbo].[F55511_image] WHERE
LITM IS NULL OR LITM=''

 UNION ALL

 SELECT
ImageName,LITM,AN8,LabelType,Format FROM [LMS].[dbo].[F55511_image] WHERE
AN8 IS NULL OR AN8=''

 UNION ALL

 SELECT
ImageName,LITM,AN8,LabelType,Format FROM [LMS].[dbo].[F55511_image] WHERE
LabelType IS NULL
OR LabelType=''

 UNION ALL

 SELECT
ImageName,LITM,AN8,LabelType,Format FROM [LMS].[dbo].[F55511_image] WHERE
Format IS NULL OR Format=''

)A

IF(@row_Count>0)

 BEGIN

      DECLARE
@tableHTML  NVARCHAR(MAX);

      SET
@tableHTML = 

          N'<h1>F55511_Image Empty Row List</h1>'

        + N'<table border="1">'

        + N'<tr><th>ImageName</th><th>LITM</th>'

        + N'<th>AN8</th><th>LabelType</th><th>Format</th></tr>'+

SELECT ImageName,LITM,AN8,LabelType,Format FROM [LMS].[dbo].[F55511_image]           WHERE
LITM IS NULL OR LITM=''

      UNION ALL

SELECT ImageName,LITM,AN8,LabelType,Format FROM [LMS].[dbo].[F55511_image]  WHERE AN8 IS NULL OR AN8=''

      UNION ALL

SELECT ImageName,LITM,AN8,LabelType,Format FROM [LMS].[dbo].[F55511_image]
WHERE LabelType IS
NULL OR
LabelType=''

      UNION ALL

SELECT ImageName,LITM,AN8,LabelType,Format FROM [LMS].[dbo].[F55511_image]  WHERE Format IS NULL OR Format=''+

      N'<table>'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'iDevelopment.info Profile'

 --> here profile
name while we    create the SMTP profile
.

, @recipients = 'dba@idevelopment.info'

--- > here Business
people mail (i.e) the person who want to receive the list

, @subject = 'F55511_Image(LITM,AN8,LabelType,Format)Empty Row List'
--> Subject to mention regarding empty rows

, @body = @tableHTML --> here above query will execute and send as     table format

, @body_format = 'HTML'; --> here we need to mention body format  as HTML.

END