求解：DTS发送MAIL问题

[kid_1982]

已经有一个DTS，已经实现：执行“Select * from Customers”之后，若成功无操作，若失败则发送邮件到指定邮箱×××◎163.com。
现在怎么修改这个DTS，使得在执行“Select * from Customers”之后，若有返回值（至少返回一跳记录）则发送邮件，若无返回记录则无操作呢？

[utpcb]

你要建立操作员阿
设置EMAIL

[kid_1982]

设置好了，能够实现查询失败则发送MAIL。但是现在的需求是查询获得记录则发送MAIL。请指教

[pgy8288]

假设已配置了数据库邮件.

首先取得油箱地址

declare @StrEmail NVARCHAR(MAX)
SET @StrEmail = ''
SELECT @StrMail = @StrMail + EmailAddress + ';' 
FROM T

然后

Declare @todayDate nvarchar(50)				/* 昨天的日期 */
	, @BodyDesc	NVARCHAR(1000)				/*  邮件内容 */
	, @RecipientsMails	VARCHAR(300)		/* 收件人邮件列表 */
	, @SubjectTitle		NVARCHAR(50)		/* 邮件标题 */
	, @file				NVARCHAR(100)		/* 附件 */
	, @ProfileConfigName	NVARCHAR(50)	/* 邮件配置名 */

set @todayDate=(select convert(char(10),dateADD(day,0,getdate()),21))
SELECT @SubjectTitle = @todayDate + '标题'
	, @BodyDesc = N'Dear all:'
	, @RecipientsMails = @StrMail
	, @file = N'E:\1.xls'
	, @ProfileConfigName = '测试数据库邮件配置名'

EXEC msdb.dbo.sp_send_dbmail
	@Recipients = @RecipientsMails
	, @body = @BodyDesc
	, @Subject = @SubjectTitle
	, @Profile_Name = @ProfileConfigName
	, @File_Attachments = @File

[zhiguo2008]

....学习

[kid_1982]

一定要用SQL吗？不能在DTS里设置？

[ojuju10]

use hh

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_JobSet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 drop procedure [dbo].[p_JobSet]
GO

/*--指定时间调用存储过程

 创建一个在指定时间,调用指定存储过程的作业
 作业执行完成后会自动删除

*/
select dateadd(mi,1,getdate())
declare @time datetime
select replace(convert(varchar,getdate(),108),':','')

/*--调用示例

 declare @dt datetime
 set @dt=dateadd(mi,1,getdate()) --当前时间1分钟后执行
 exec p_JobSet 'master.dbo.xp_cmdshell ''dir c:\*.*''',@dt
--*/
create proc p_JobSet
@prorcname sysname, --要调用定时调用的存储过程名,如果不在当前库中,则用:库名.所有者名.存储过程名
@job_date datetime --存储过程的执行时间(包括时间信息)
as
declare @dbname sysname,@jobname sysname
 ,@date int,@time int

select @jobname='临时作业_'+cast(newid() as varchar(36))
 ,@date=convert(varchar,@job_date,112)
 ,@time=replace(convert(varchar,@job_date,108),':','')

if exists(select 1 from msdb..sysjobs where name=@jobname)
 exec msdb..sp_delete_job @job_name=@jobname 

--创建作业
exec msdb..sp_add_job @job_name=@jobname,@delete_level=1

--创建作业步骤
declare @sql varchar(800)
select @sql='exec '+@prorcname
 ,@dbname=db_name()

exec msdb..sp_add_jobstep @job_name=@jobname,
 @step_name = '处理步骤',
 @subsystem = 'TSQL',
 @database_name=@dbname,
 @command = @sql,
 @retry_attempts = 5,   --重试次数
 @retry_interval = 5    --重试间隔

--创建调度
EXEC msdb..sp_add_jobschedule @job_name = @jobname, 
 @name = '时间安排',
 @enabled = 1, 
 @freq_type = 1, 
 @active_start_date = @date,
 @active_start_time = @time

-- 添加目标服务器
EXEC msdb.dbo.sp_add_jobserver 
 @job_name = @jobname ,
 @server_name = N'(local)' 
go


use Master
GO
sp_configure 'show advanced options',1; 
GO
RECONFIGURE; 
GO
sp_configure 'Database Mail XPs', 1; 
GO
RECONFIGURE 
GO

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '配置1',
    @recipients = '[e-mail]',
    @body = 'The stored procedure finished successfully.',
    @subject = 'Automated Success Message zhai 13.35' ;
select  getdate()
drop table sendemail
Create table SendEmail(Id int identity,Email varchar(50),Title varchar(100),Content varchar(8000),SendTime datetime,Createtime datetime)
insert into sendEmail select '[e-mail]','测试邮件','你好！欢迎到Dfooter网站来注册，有很多意想不到的惊喜等着你！','2007-11-01 14:59:49.967',getdate()
insert into sendEmail select '[e-mail]','测试邮件','你好！欢迎到Dfooter网站来注册，有很多意想不到的惊喜等着你！','2007-11-01 14:43:49.967',getdate()
insert into sendEmail select '[e-mail]','测试邮件','你好！欢迎到Dfooter网站来注册，有很多意想不到的惊喜等着你！','2007-11-01 14:53:49.967',getdate()
insert into sendEmail select '[e-mail]','测试邮件','你好！欢迎到Dfooter网站来注册，有很多意想不到的惊喜等着你！','2007-11-01 14:53:49.967',getdate()
insert into sendEmail select '[e-mail]','测试邮件','你好！欢迎到Dfooter网站来注册，有很多意想不到的惊喜等着你！','2007-11-01 14:59:49.967',getdate()

select * from sendemail
Alter proc addEmail 
as
begin
declare @r_email varchar(50)
declare @title varchar(100)
declare @body varchar(8000)
--declare @Senttime datetime

declare roy cursor for select email,title,content  from sendemail
--where email='[e-mail]'
--and convert(varchar(16),getdate(),120)=convert(varchar(16),sendtime,120)

open roy
fetch next from roy into @r_email,@title,@body
while @@fetch_status=0
begin
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '定时发送电子邮件',
    @recipients = @r_email,
    @body = @body,
    @subject = @title;

fetch next from roy into @r_email,@title,@body
end
close roy
deallocate roy

end



create proc p_JobSet
@prorcname sysname, --要调用定时调用的存储过程名,如果不在当前库中,则用:库名.所有者名.存储过程名
@job_date datetime --存储过程的执行时间(包括时间信息)
as


declare @time datetime
declare roy cursor for select sendtime from sendemail
open roy
fetch next from roy into @time
while @@fetch_status=0
begin

exec P_jobset 'addemail',@time



fetch next from roy into @time
end
close roy
deallocate roy

[律己修心]

up