SQL
USE
[
msdb
]
GO
/* ***** Object: Job [SendMainJob] Script Date: 03/12/2010 17:05:01 ***** */
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/* ***** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 03/12/2010 17:05:01 ***** */
IF NOT EXISTS ( SELECT name FROM msdb.dbo.syscategories WHERE name = N ' [Uncategorized (Local)] ' AND category_class = 1 )
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N ' JOB ' , @type = N ' LOCAL ' , @name = N ' [Uncategorized (Local)] '
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback
END
DECLARE @jobId BINARY ( 16 )
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N ' SendMainJob ' ,
@enabled = 1 ,
@notify_level_eventlog = 0 ,
@notify_level_email = 0 ,
@notify_level_netsend = 0 ,
@notify_level_page = 0 ,
@delete_level = 0 ,
@description = N ' No description available. ' ,
@category_name = N ' [Uncategorized (Local)] ' ,
@owner_login_name = N ' sa ' , @job_id = @jobId OUTPUT
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback
/* ***** Object: Step [one] Script Date: 03/12/2010 17:05:01 ***** */
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId , @step_name = N ' one ' ,
@step_id = 1 ,
@cmdexec_success_code = 0 ,
@on_success_action = 1 ,
@on_success_step_id = 0 ,
@on_fail_action = 2 ,
@on_fail_step_id = 0 ,
@retry_attempts = 0 ,
@retry_interval = 0 ,
@os_run_priority = 0 , @subsystem = N ' TSQL ' ,
@command = N ' DECLARE @table AS TABLE(UserID NVARCHAR(100))
INSERT INTO @table
SELECT UserID
FROM USR01 u
WHERE DATEDIFF(DAY,u.LoginDT,GETDATE()) > 83 AND u.Enabled=1 AND u.Locked=0
DECLARE @UserID NVARCHAR(100)
DECLARE @EmailAdd NVARCHAR(100)
WHILE EXISTS(SELECT 1 FROM @table)
BEGIN
SELECT TOP 1 @UserID=UserID FROM @table
SELECT @EmailAdd=EmailAdd FROM USR01
WHERE UserID=@UserID
EXEC msdb.dbo.sp_send_dbmail
@recipients=@EmailAdd,
@body= '' Your Password will be expiration in seven days! '' ,
@subject = '' Long time No Logon Notice '' ,
@profile_name = '' dba_profile '' , --DB SQL MAIL
@body_format = '' TEXT ''
DELETE @table WHERE UserID=@UserID
END
GO ' ,
@database_name = N ' ReportServer ' , -- DB NAME
@flags = 0
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId , @start_step_id = 1
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId , @name = N ' JobDaily ' ,
@enabled = 1 ,
@freq_type = 4 ,
@freq_interval = 1 ,
@freq_subday_type = 1 ,
@freq_subday_interval = 2 ,
@freq_relative_interval = 0 ,
@freq_recurrence_factor = 0 ,
@active_start_date = 20100312 ,
@active_end_date = 20100317 ,
@active_start_time = 160000 ,
@active_end_time = 235959
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId , @server_name = N ' (local) '
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF ( @@TRANCOUNT > 0 ) ROLLBACK TRANSACTION
EndSave:
GO
/* ***** Object: Job [SendMainJob] Script Date: 03/12/2010 17:05:01 ***** */
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/* ***** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 03/12/2010 17:05:01 ***** */
IF NOT EXISTS ( SELECT name FROM msdb.dbo.syscategories WHERE name = N ' [Uncategorized (Local)] ' AND category_class = 1 )
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N ' JOB ' , @type = N ' LOCAL ' , @name = N ' [Uncategorized (Local)] '
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback
END
DECLARE @jobId BINARY ( 16 )
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N ' SendMainJob ' ,
@enabled = 1 ,
@notify_level_eventlog = 0 ,
@notify_level_email = 0 ,
@notify_level_netsend = 0 ,
@notify_level_page = 0 ,
@delete_level = 0 ,
@description = N ' No description available. ' ,
@category_name = N ' [Uncategorized (Local)] ' ,
@owner_login_name = N ' sa ' , @job_id = @jobId OUTPUT
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback
/* ***** Object: Step [one] Script Date: 03/12/2010 17:05:01 ***** */
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId , @step_name = N ' one ' ,
@step_id = 1 ,
@cmdexec_success_code = 0 ,
@on_success_action = 1 ,
@on_success_step_id = 0 ,
@on_fail_action = 2 ,
@on_fail_step_id = 0 ,
@retry_attempts = 0 ,
@retry_interval = 0 ,
@os_run_priority = 0 , @subsystem = N ' TSQL ' ,
@command = N ' DECLARE @table AS TABLE(UserID NVARCHAR(100))
INSERT INTO @table
SELECT UserID
FROM USR01 u
WHERE DATEDIFF(DAY,u.LoginDT,GETDATE()) > 83 AND u.Enabled=1 AND u.Locked=0
DECLARE @UserID NVARCHAR(100)
DECLARE @EmailAdd NVARCHAR(100)
WHILE EXISTS(SELECT 1 FROM @table)
BEGIN
SELECT TOP 1 @UserID=UserID FROM @table
SELECT @EmailAdd=EmailAdd FROM USR01
WHERE UserID=@UserID
EXEC msdb.dbo.sp_send_dbmail
@recipients=@EmailAdd,
@body= '' Your Password will be expiration in seven days! '' ,
@subject = '' Long time No Logon Notice '' ,
@profile_name = '' dba_profile '' , --DB SQL MAIL
@body_format = '' TEXT ''
DELETE @table WHERE UserID=@UserID
END
GO ' ,
@database_name = N ' ReportServer ' , -- DB NAME
@flags = 0
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId , @start_step_id = 1
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId , @name = N ' JobDaily ' ,
@enabled = 1 ,
@freq_type = 4 ,
@freq_interval = 1 ,
@freq_subday_type = 1 ,
@freq_subday_interval = 2 ,
@freq_relative_interval = 0 ,
@freq_recurrence_factor = 0 ,
@active_start_date = 20100312 ,
@active_end_date = 20100317 ,
@active_start_time = 160000 ,
@active_end_time = 235959
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId , @server_name = N ' (local) '
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF ( @@TRANCOUNT > 0 ) ROLLBACK TRANSACTION
EndSave:
前两篇介绍如何配制与发送EMAIL.此篇用来根据数据库中某些条件,新建作业,定时检查发送E-MAIL.
作者:阿龍 From:http://s021368.cnblogs.com/