Wednesday, January 9, 2013

SharePoint Alerts - Force Scheduled Alerts, Remove Bad Alerts

--Push All Old Subscriptions
update SchedSubscriptions
  SET
        NotifyTime    = CASE WHEN (NotifyTime    IS NOT NULL) THEN NotifyTime    ELSE  @Now END - CASE WHEN (NotifyFreq = 1) THEN 1.0 ELSE 7.0 END,
        NotifyTimeUTC = CASE WHEN (NotifyTimeUTC IS NOT NULL) THEN NotifyTimeUTC ELSE  @Now END - CASE WHEN (NotifyFreq = 1) THEN 1.0 ELSE 7.0 END
  where Id in (select ss.Id
            FROM dbo.EventSubsMatches AS esm LEFT OUTER JOIN
            dbo.EventCache AS ec ON esm.EventId = ec.Id LEFT OUTER JOIN
            dbo.SchedSubscriptions AS ss ON esm.SubId = ss.Id LEFT OUTER JOIN
            dbo.EventLog AS el ON esm.EventId = el.Id
            WHERE (DATEDIFF(Hour,EventLog.eventtime,SchedSubscriptions.NotifyTimeUTC) > (24 * (CASE SchedSubscriptions.notifyfreq when 1 then 1.0 else 7.0 END))));
           
--Delete subscriptions with missing e-mail addresses
SET NOCOUNT ON;
DECLARE @sub uniqueidentifier;
DECLARE sub_cursor CURSOR FOR
    select ss.Id
    from UserInfo as ui inner join
    SchedSubscriptions as ss on ui.tp_ID = ss.UserId
    where ss.UserEmail = N'';
OPEN sub_cursor

FETCH NEXT FROM sub_cursor
INTO @sub
WHILE @@FETCH_STATUS = 0
BEGIN
    delete from SchedSubscriptions    where Id = @sub
    delete from EventSubsMatches    where SubId = @sub
    PRINT 'Deleting subscription with missing e-mail address ' + CAST(@sub as varchar(40))
    FETCH NEXT FROM sub_cursor
    INTO @sub
END
CLOSE sub_cursor;
DEALLOCATE sub_cursor;

No comments:

Post a Comment