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.
Manish Kumar Choudhary is a passionate and pragmatic software engineer specializing in web application development with ASP.NET MVC, Web API, Entity Framework, NHibernet, Angular, Backbone, HTML5, and CSS. He started programming at the age of twelve on VB.Net and fell in love with it. His dream at the time was to become a software engineer so he pursues programming both academically and professionally. He has a Bachelor of Science in Computer Application and a Master of Science in Computer Application. He has over 3.5 years’ professional experience developing web applications. Outside the software world, he enjoys photography, swimming, cricket and football. Manish is based in Hyderabad, India.