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
2376
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
