How to trigger and test daily alerts in Sharepoint

In Sharepoint it is possible to create alerts with different frequency:

• immediate – sent immediately when next time immediate alerts job will run
• daily – sent daily also by immediate alerts job
• weekly – sent weekly

If you create new daily alert and want to see whether it will work or not it is not very convenient to wait 24 day until Sharepoint will sent them next time. In this post I will show several ways to trigger summary alerts and send them when you need.

Method 1. When you add a new daily alert, new row is added to the SchedSubscriptions table into Sharepoint content database. This is the key element of this method. We are interesting in the following 2 columns in this table:

1. NotifyTime
2. NotifyTimeUNC (NotifyTime minus 3 hours)

In these columns Sharepoint stores time when next time daily alert for particular list will be sent. So first of all determine row which corresponds to your list:

   1: SELECT * FROM SchedSubscriptions

Table contains SiteUrl, WebUrl, ListUrl columns. Using them you will be able to find needed row. Copy Id (uniqueidentifier) and execute the following SQL query:

   1: declare @s datetime

   2: declare @u datetime

   3: set @s = CAST('2012-06-24 12:00:00.000' as datetime)

   4: set @u = CAST('2012-06-24 09:00:00.000' as datetime)

   5:  

   6: update dbo.SchedSubscriptions

   7: set NotifyTime = @s, NotifyTimeUTC = @u

   8: where Id = '...'

In this example in Id you should specify value which you copied from previous query’s result, @s corresponds to NotifyTime, @u to NotifyTimeUNC (NotifyTime minus 3 hours). Time should be in past (comparing with current datetime) – only in this case Sharepoint will send daily alerts.

After that wait some time. Exact time of waiting depends on the job-immediate-alerts property which can be determined by the following command:

   1: stsadm -o getproperty -pn job-immediate-alerts -url http://example.com

for testing you can set it to 1 minute:

   1: stsadm -o setproperty -pn job-immediate-alerts -url http://example.com -pv "every 1 minutes between 0 and 59"

but after testing it is better to revert it e.g. to 5 minutes:

   1: stsadm -o setproperty -pn job-immediate-alerts -url http://example.com -pv "every 5 minutes"

So after this time if you will check SchedSubscriptions table you will see that time which you updated is increased by 1 day: in out example it will be “2012-06-25 12:00:00.000” for NotifyTime and “2012-06-25 09:00:00.000” for NotifyTimeUNC. It means that Sharepoint processed daily alert and it was sent. If everything is Ok, you or alert’s recipient should get email alert with daily summary.

Method 2. I found it in the following forum thread: SPAlertHandlerParams - not behaving correctly for daily alerts, but didn’t test it by myself. May be it will be useful for you as well:

   1: SPSite site = new SPSite("http://example.com");

   2: SPWeb web = site.OpenWeb();

   3: SPAlert alert = web.Alerts[new Guid("...")];

   4: alert.AlertFrequency = SPAlertFrequency.Daily; 

   5: alert.AlertTime = DateTime.Now.AddMinutes(1);

   6: alert.Update();

For general alerts troubleshooting I recommend the following articles: The Truth About How Daily SharePoint Alerts Actually Work, Troubleshooting Alerts. They will economy some time for you. Possibility to trigger daily alerts is very important for troubleshooting. It helped me in my work, hope it will be helpful for you as well.