Post
Ask Question

How To Send Mail Using SQL Server: Part 2

There are the following three parts of this tutorial:

  1. Configure email profile and send test Email using SQL Server.
  2. Select and send data in mail.
  3. Schedule daily mail from SQL Server

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.

  1. CREATE TABLE [dbo].[tblStudents](  
  2.     [Id] [int] IDENTITY(1,1) NOT NULL,  
  3.     [StudentName] [varchar](50) NOT NULL,  
  4.     [RollNo] [varchar](20) NOT NULL,  
  5.     [Add] [varchar](50) NULL,  
  6.     [MobileNo] [varchar](10) NOT NULL,  
  7.  CONSTRAINT [PK_tblStudents] PRIMARY KEY CLUSTERED   
  8. (  
  9.     [Id] ASC  
  10. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  11. ) ON [PRIMARY]  
  12.   
  13. 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.

 

  1. <h3>Students Information</h3>  
  2.         <table border="1">  
  3.             <tr>  
  4.                 <th>  
  5.                     Roll No   
  6.                 </th>  
  7.                 <th>  
  8.                     Student Name  
  9.                 </th>  
  10.                 <th>  
  11.                     Address  
  12.                 </th>  
  13.                 <th>  
  14.                     Mobile No</th>  
  15.             </tr>  
  16.             <tr>  
  17.                 <td>  
  18.                     1  
  19.                 </td>  
  20.                 <td>  
  21.                     Manish kumar  
  22.                 </td>  
  23.                 <td>  
  24.                     Hydrabad</td>  
  25.                 <td>  
  26.                     0000000000  
  27.                 </td>  
  28.             </tr>  
  29.             <tr>  
  30.                 <td>  
  31.                     2  
  32.                 </td>  
  33.                 <td>  
  34.                     Venkat</td>  
  35.                 <td>  
  36.                     Pune</td>  
  37.                 <td>  
  38.                     111111111  
  39.                 </td>  
  40.             </tr>  
  41.             </table>  
Step 5

Now write the following query and bind that query to the msdb.dbo.sp_send_dbmail Stored Procedure.
  1. DECLARE @TabulerData NVARCHAR(MAX)  
  2. DECLARE @TablrBodyData NVARCHAR(MAX)  
  3. SET @TabulerData = CAST(( SELECT [RollNo] AS 'td','',[StudentName] AS 'td','',  
  4.        [Add] AS 'td','', [MobileNo] AS 'td'  
  5. FROM  tblStudents FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))  
  6.   
  7.   
  8. SET @TablrBodyData ='<html><body><H4>Students Information</H4>  
  9. <table border = 1>   
  10. <tr>  
  11. <th> Roll No </th> <th>  Student Name </th> <th> Address </th> <th> Mobile No </th></tr>'      
  12.   
  13.    
  14. SET @TablrBodyData = @TablrBodyData + @TabulerData +'</table></body></html>'  
Step 6

Now bind @TablrBodyData to the body of the msdb.dbo.sp_send_dbmail Stored Procedure.
  1. EXEC msdb.dbo.sp_send_dbmail @profile_name='MyTestMail',  
  2. @recipients='[email protected]',   
  3. @subject='My Test Mail Service with student data.',  
  4. @body=@TablrBodyData,   
  5. @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.


Similar Articles