Email database object maintenance
The following table lists the Email database objects.
Email database objects
dbo.SendEmailHistory
Type: Table
View of current and completed tasks. The tables is useful for tracking current and historical activity.
dbo.Schedule
Type: Table
Defines the task schedule.
dbo.SendEmailTask
Type: Table
Contains a full description of a task that is usable only by send email tasks.
dbo.ScheduleTask
Type: Table
Links tasks to schedules by defining an N:N relationship between rows in the Schedule table and rows in the SendEmailTask table. When a task in the Schedule table comes due, one or more tasks can be performed. A single task can be initiated by multiple Schedules.
IX_SendEmailHistory_ScheduleTaskId
Type: Index
Non-clustered index on dbo.SendEmailHistory.ScheduleTaskId.
IX_SendEmailHistory_BatchStatus
Type: Index
Non-clustered index on dbo.SendEmailHistory.BatchStatus.
IX_Schedule_EndTime
Type: Index
Non-clustered index on dbo.Schedule.EndTime.
IX_ScheduleTask_TaskId
Type: Index
Non-clustered index on dbo.ScheduleTask.TaskId.
IX_ScheduleTask_ScheduleId
Type: Index
Non-clustered index on dbo.ScheduleTask.ScheduleId.
IX_ScheduleTask_Status
Type: Index
Non-clustered index on dbo.ScheduleTask.Status.
IX_ScheduleTask_NextDueTime
Type: Index
Non-clustered index on dbo.ScheduleTask.NextDueTime.
IX_ScheduleTask_LastKeepAliveTime
Type: Index
Non-clustered index on dbo.ScheduleTask.LastKeepAliveTime.
dbo.EmailInsertSendEmailHistory
Type: Procedure
PaticipantEmailer inserts a new record into the table when a task is started.
dbo.EmailUpdateSendEmailHistory
Type: Procedure
ParticipantEmailer updates the record (BatchStatus, BatchEndTime, SuccessfulEmailCount, FailedEmailCount) when the task is finishes, fails, or the task is not started and is disposed.
dbo.EmailGetAllSendEmailTasks
Type: Procedure
Used by IEmailer.Tasks interface to retrieve all send email schedule tasks.
dbo.EmailGetSendTask
Type: Procedure
Used by IEmailer.GetNextTask, to retrieve a next send email schedule task. The procedure is also used to validate a task from IEmailer.Tasks (when a user starts a task).
dbo.SchedUpdateScheduleTask
Type: Procedure
ParticipantsEmailer updates the dbo.ScheduleTask table (Status, ExecuteCount, NextDueTime, LastKeepAliveTime) when a scheduled task is finishes or fails.
dbo.SchedGetScheduleTask
Type: Procedure
Used by the IEmailTask.Dispose interface. The procedure is called to change the task status when a task is not started, when a user calls Dispose, or when the task is released by GC.
dbo.EmailCheckForAbandonedAndCompletedTasks
Type: Procedure
1. Called by ParticipantEmailer when retrieving the next task. The procedure checks for stuck records, or completed schedule task.
2. Reset stuck SendEmailHistory records for and update the batch status totsAbandoned.
Update the ScheduleTask from tsWaiting to tsComplete when theEndTime is reached.)
dbo.EmailUpdateSendStatus
Type: Procedure
When a task is started from, ParticipantEmailer the procedure updates the database at regular intervals. The procedure updates SuccessfulEmailCount, the SendEmailHistory table's FailedEmailCount, and the ScheduleTask table's LastKeepAliveTime.
dbo.EmailScheduleTaskUpdate
Type: Procedure
Adds or edits the Email activity's schedule task.
dbo.SchedGetScheduleTask
Type: Procedure
Retrieves the Email activity's schedule email record (for editing).
dbo.EmailGetJobStatus
Type: Procedure
Retrieves the current user interface's email job status.
Notes
The dbo.Schedule and dbo.ScheduleTask tables are designed to support the scheduling of emails and other schedule oriented activities. The dbo.SendEmailTask and dbo.SendEmailHistory tables are specific to the Email activity.
The email sending and task status are stored in dbo.SendEmailHistory.BatchStatus and dbo.ScheduleTask.Status indexes. The email sending history records are stored in the dbo.SendEmailHistory table.
It is possible to use the scheduling tables to check the current task status and history. When a project is deleted, the related schedule records are also deleted. For more information, see
mrInterviewData database stored procedures.
The following SQL statement can be used to delete old records (for example, records from the previous year):
-------------------------
DECLARE @DelYear INT
SET @DelYear = 1
DECLARE @DeletedScheduleTask TABLE
(
ScheduleTaskId INT
)
INSERT INTO @DeletedScheduleTask
SELECT DISTINCT ScheduleTaskId from dbo.SendEmailHistory WHERE BatchEndTime < DATEADD(year, -@DelYear, GETDATE())
-- Delete SendEmailHistory
DELETE FROM dbo.SendEmailHistory WHERE BatchEndTime < DATEADD(year, -@DelYear, GETDATE())
-- Delete ScheduleTask
DELETE FROM ST FROM dbo.ScheduleTask as ST
INNER JOIN @DeletedScheduleTask as DST
ON ST.Id = DST.ScheduleTaskId
WHERE ST.LastKeepAliveTime < DATEADD(year, -@DelYear, GETDATE())
-- Delete Schedule
DELETE FROM dbo.Schedule WHERE EndTime < DATEADD(year, -@DelYear, GETDATE())
----------------------------
See also