Introduction

This article explains how to select data from a table and bind that data to an email and send a mail using SQL Server 2008.

Step 1

Log in to SQL Server 2008 with a correct user name and password.

Step 2

Click on the database and click on New query.

In this example, I have a student table named tblstudent and some test data in that table.

CREATE TABLE [dbo].[tblStudents](  
    [Id] [int] IDENTITY(1,1) NOT NULL,  
    [StudentName] [varchar](50) NOT NULL,  
    [RollNo] [varchar](20) NOT NULL,  
    [Add] [varchar](50) NULL,  
    [MobileNo] [varchar](10) NOT NULL,  
 CONSTRAINT [PK_tblStudents] PRIMARY KEY CLUSTERED   
(  
    [Id] ASC  
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON

) ON [PRIMARY]  
) ON [PRIMARY]   
GO

Step 3

Now, I will select data from the student table data and bind that data to an email. The following tabular data should be in the mail.

student information

Step 4

To design the preceding table, we need the following HTML table kind of structure.

<h3>Students Information</h3>  
        <table border="1">  
            <tr>  
                <th>  
                    Roll No   
                </th>  
                <th>  
                    Student Name  
                </th>  
                <th>  
                    Address  
                </th>  
                <th>  
                    Mobile No</th>  
            </tr>  
            <tr>  
                <td>  
                    1  
                </td>  
                <td>  
                    Manish kumar  
                </td>  
                <td>  
                    Hydrabad</td>  
                <td>  
                    0000000000  
                </td>  
            </tr>  
            <tr>  
                <td>  
                    2  
                </td>  
                <td>  
                    Venkat</td>  
                <td>  
                    Pune</td>  
                <td>  
                    111111111  
                </td>  
            </tr>  
            </table>

Step 5

Now write the following query and bind that query to the msdb.dbo.sp_send_dbmail Stored Procedure.

DECLARE @TabulerData NVARCHAR(MAX)  
DECLARE @TablrBodyData NVARCHAR(MAX)  
SET @TabulerData = CAST(( SELECT [RollNo] AS 'td','',[StudentName] AS 'td','',  
       [Add] AS 'td','', [MobileNo] AS 'td'  
FROM  tblStudents FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))    
  
SET @TablrBodyData ='<html><body><H4>Students Information</H4>  
<table border = 1>   
<tr>  
<th> Roll No </th> <th>  Student Name </th> _
<th> Address </th> <th> Mobile No </th></tr>'      
  
   
SET @TablrBodyData = @TablrBodyData + @TabulerData +'</table></body></html>'

Step 6

Now bind @TablrBodyData to the body of the msdb.dbo.sp_send_dbmail Stored Procedure.

EXEC msdb.dbo.sp_send_dbmail @profile_name='MyTestMail',  
@recipients='manishki@live.com',   
@subject='My Test Mail Service with student data.',  
@body=@TablrBodyData,   
@body_format ='HTML'

Output

Mail queued. Check the email; it will show as follows:

test mail service

Summary

In this illustration, you learned how to select data from a table and send that data to an email using SQL Server 2008. Please provide your valuable comments about this article.

How To Send Mail Using SQL Server: Part 2