This tutorial has three parts as follows:
- Configure email profile and send test email using SQL Server
- Select and send data in mail
- 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.
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.
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:
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.