求助:关于DTS发送MAILE的问题 有一个DTS,已经实现:执行“Select * from Customers”之后,若成功无操作,若失败则发送邮件到指定邮箱×××◎163.com。现在怎么修改这个DTS,使得在执行“Select * from Customers”之后,若有返回值(至少返回一跳记录)则发送邮件,若无返回记录则无操作呢? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 use hhif 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 datetimeselect 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 --存储过程的执行时间(包括时间信息)asdeclare @dbname sysname,@jobname sysname ,@date int,@time intselect @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)' gouse MasterGOsp_configure 'show advanced options',1; GORECONFIGURE; GOsp_configure 'Database Mail XPs', 1; GORECONFIGURE GOEXEC msdb.dbo.sp_send_dbmail @profile_name = '配置1', @recipients = '[email protected]', @body = 'The stored procedure finished successfully.', @subject = 'Automated Success Message zhai 13.35' ;select getdate()drop table sendemailCreate table SendEmail(Id int identity,Email varchar(50),Title varchar(100),Content varchar(8000),SendTime datetime,Createtime datetime)insert into sendEmail select '[email protected]','测试邮件','你好!欢迎到Dfooter网站来注册,有很多意想不到的惊喜等着你!','2007-11-01 14:59:49.967',getdate()insert into sendEmail select '[email protected]','测试邮件','你好!欢迎到Dfooter网站来注册,有很多意想不到的惊喜等着你!','2007-11-01 14:43:49.967',getdate()insert into sendEmail select '[email protected]','测试邮件','你好!欢迎到Dfooter网站来注册,有很多意想不到的惊喜等着你!','2007-11-01 14:53:49.967',getdate()insert into sendEmail select '[email protected]','测试邮件','你好!欢迎到Dfooter网站来注册,有很多意想不到的惊喜等着你!','2007-11-01 14:53:49.967',getdate()insert into sendEmail select '[email protected]','测试邮件','你好!欢迎到Dfooter网站来注册,有很多意想不到的惊喜等着你!','2007-11-01 14:59:49.967',getdate()select * from sendemailAlter proc addEmail asbegindeclare @r_email varchar(50)declare @title varchar(100)declare @body varchar(8000)--declare @Senttime datetimedeclare roy cursor for select email,title,content from sendemail--where email='[email protected]'--and convert(varchar(16),getdate(),120)=convert(varchar(16),sendtime,120)open royfetch next from roy into @r_email,@title,@bodywhile @@fetch_status=0beginEXEC msdb.dbo.sp_send_dbmail @profile_name = '定时发送电子邮件', @recipients = @r_email, @body = @body, @subject = @title;fetch next from roy into @r_email,@title,@bodyendclose roydeallocate royendcreate proc p_JobSet@prorcname sysname, --要调用定时调用的存储过程名,如果不在当前库中,则用:库名.所有者名.存储过程名@job_date datetime --存储过程的执行时间(包括时间信息)asdeclare @time datetimedeclare roy cursor for select sendtime from sendemailopen royfetch next from roy into @timewhile @@fetch_status=0beginexec P_jobset 'addemail',@timefetch next from roy into @timeendclose roydeallocate roy 做个Job,然后使用存储过程判断是否有记录,如果有记录,则调用Job发送邮件,如果没有记录,则不发送. 对对 。就请问这个JOB怎么做呢?我知道的只有成功或者失败发送邮件 C# 中使用Access 的Update 语法 求一sql 高手帮忙 在线等 为什么存储过程中累加后变为null了? 统计某条新闻的点击率,用触发器实现,大虾请进来看一下,指点一下迷津。 急求:提取所有 数据类型为 int或double型的列,求和显示出来 SQL如何小时转换为分钟 求一条随机抽题的SQL语句!!!急!急! 请教一个基础的数据库问题 可用分有点多,散分! 插入记录时如何得知具有identity PRIMARY KEY属性字段的值? 抓取数据表创建语句 我要做一组数据?不知道怎么弄请各位哥哥姐姐帮个忙!先谢谢了
use hhif 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 intselect @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
GOEXEC msdb.dbo.sp_send_dbmail
@profile_name = '配置1',
@recipients = '[email protected]',
@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 '[email protected]','测试邮件','你好!欢迎到Dfooter网站来注册,有很多意想不到的惊喜等着你!','2007-11-01 14:59:49.967',getdate()
insert into sendEmail select '[email protected]','测试邮件','你好!欢迎到Dfooter网站来注册,有很多意想不到的惊喜等着你!','2007-11-01 14:43:49.967',getdate()
insert into sendEmail select '[email protected]','测试邮件','你好!欢迎到Dfooter网站来注册,有很多意想不到的惊喜等着你!','2007-11-01 14:53:49.967',getdate()
insert into sendEmail select '[email protected]','测试邮件','你好!欢迎到Dfooter网站来注册,有很多意想不到的惊喜等着你!','2007-11-01 14:53:49.967',getdate()
insert into sendEmail select '[email protected]','测试邮件','你好!欢迎到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 datetimedeclare roy cursor for select email,title,content from sendemail
--where email='[email protected]'
--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 royendcreate 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
beginexec P_jobset 'addemail',@timefetch next from roy into @time
end
close roy
deallocate roy
然后使用存储过程判断是否有记录,
如果有记录,则调用Job发送邮件,
如果没有记录,则不发送.