IF EXISTS(SELECT name from master..sysdatabases where name = 'SalesDB')
DROP DATABASE SalesDB
GO
CREATE DATABASE SalesDB
GO
USE SalesDB
GO
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'Dragon.Xiong@AsiaSoft.HK',
@body='Message Body',
@subject ='Message Subject',
@profile_name ='dba_profile',
@query ='SELECT Product FROM SalesDB..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 ='<html><H1>Sales Reports</H1><body bgcolor=yellow><table border = 2><tr><th>Product</th><th>SaleAmount</th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'Dragon.Xiong@AsiaSoft.HK',
@body = @body,
@body_format ='HTML',
@subject ='Message Subject',
@profile_name ='dba_profile'
DROP DATABASE SalesDB
GO
CREATE DATABASE SalesDB
GO
USE SalesDB
GO
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'Dragon.Xiong@AsiaSoft.HK',
@body='Message Body',
@subject ='Message Subject',
@profile_name ='dba_profile',
@query ='SELECT Product FROM SalesDB..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 ='<html><H1>Sales Reports</H1><body bgcolor=yellow><table border = 2><tr><th>Product</th><th>SaleAmount</th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'Dragon.Xiong@AsiaSoft.HK',
@body = @body,
@body_format ='HTML',
@subject ='Message Subject',
@profile_name ='dba_profile'
--profile_name is setted by last article
