CREATE PROCEDURE [dbo].[usp_CheckPrice]
as
declare @strsql varchar(2000)
set @strsql='select distinct convert(char(20),ppo_no) as ppono,'
+'convert(char(5),usage) as usage,'
+'convert(char(40),combo) as combo,'
+'convert(char(5),size) as size,'
+'convert(char(8),price,2) as price,'
+'convert(char(5),currency) as unit ,'
+'convert(char(10),delivery_date,120) as deldate from planningdb..ppo '
+ ' where isnull(lot_status,'''')<>''CANCEL'' and lot_no=original_lot_no and '
+'left(ppo_no,3) in (''KGK'',''PKG'',''PGK'') and ((isnull(price,0)=0) or (isnull(currency,'''')='''') ) '
+'and delivery_date<=dateadd(day,10,getdate()) '
+'order by ppono '
EXEC master.dbo.xp_sendmail
@recipients='genx@esquel.com;WangLin@esquel.com', //收件人
@copy_recipients ='tiany@esquel.com;chenjh@esquel.com', //抄送
@message='附件中的订单为交期在十天之内缺单价或币种的订单!请尽快补入单价!', //内容
@subject='订单价格报警', //主题
@query = @strsql, //结果作为附件
@attach_results ='true',
@no_header= 'false' ,@width=200
GO
配置 SQL 邮件配置文件
邮件配置文件一经配置,即专门用于 Microsoft® Windows NT® 4.0 或 Windows® 2000 用户域帐户,当用户成功登录到 Windows NT 4.0 或 Windows 2000 时,此用户帐户被激活。SQL 邮件必须在启动 Microsoft SQL Server™ 实例时所使用的同一用户域帐户或环境中创建邮件配置文件。当一个邮件存储过程执行时,SQL 邮件将在触发该过程的域帐户中查找已定义的邮件配置文件。