Tim chapman是在位于路易斯维尔一家银行工作的SQL Server数据库管理员,有7年多的IT经验,同时,他还获得了微软SQL Server 2000 和SQL Server 2005认证。
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 ='<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'chapman.tim@gmail.com',
@body = @body,
@body_format ='HTML',
@subject ='Message Subject',
@profile_name ='DatabaseMailProfile'