Database Email Example

Tim Chapman is an SQL Server database administrator working in a bank in Louisville. He has more than seven years of it experience and also obtained Microsoft SQL Server 2000 and SQL Server 2005 certification.

IF EXISTS(SELECT name from master..sysdatabases where name = 'SalesDB')
      DROP DATABASE SalesDB

CREATE DATABASE SalesDB;

USE SALESDB;
CREATE TABLE SalesHistory
(
      SaleID INT IDENTITY(1,1),
      Product VARCHAR(30), 
      SaleDate SMALLDATETIME, 
      SalePrice MONEY
)

DECLARE @i SMALLINT

SET @i = 1

WHILE (@i <=100)

BEGIN
      INSERT INTO SalesHistory
      (Product, SaleDate, SalePrice)
      VALUES
      ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57) )


      INSERT INTO SalesHistory
      (Product, SaleDate, SalePrice)
      VALUES
      ('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13) )


      INSERT INTO SalesHistory
      (Product, SaleDate, SalePrice)
      VALUES
      ('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29) )


      SET @i = @i + 1

END

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'chapman.tim@gmail.com',
@body='Message Body', 
@subject ='Message Subject',
@profile_name ='DatabaseMailProfile',
@query ='SELECT Product FROM sb2..SalesHistory GROUP BY Product HAVING COUNT(*) > 3', 
@attach_query_result_as_file = 1,
@query_attachment_filename ='Results.txt'
 
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml =CAST(( SELECT Product AS 'td','',SUM(SalePrice) AS 'td' 
FROM SalesHistory GROUP BY Product FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='SET @body = @body + @xml +'</table></body>
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'chapman.tim@gmail.com',
@body = @body,
@body_format ='HTML',
@subject ='Message Subject',
@profile_name ='DatabaseMailProfile'