Scheduled Rule Error Redux (Attn Diane)

Ok, I figured this whole issue out I think. The documentation isn't too clear on this, so it took trial and error to figure out. It appears to me that:

1. You cannot refer to the Event view in a <ScheduledRule/Action> node. The view is not available at the time a scheduled rule is fired. You must instead store all event information in a Chron table (using the ChronicleRule node) and reference the Chron table in this node.

2. Along the same lines, you cannot refer to a Subscription view in <ChronicleRule/Action> node. This is because this view references the CurrentSubscriptions table and this table may (more likely than not) not be populated at the particular time the rule is being fired.

In my case though, I have subscribers subscribed at different times and I want them to receive only for events they have not received yet. So, I had to fix the Chron table up a little bit by adding a sent flag and a subscriberID along with the other fields used by the notification class. I populate the Chron table with all the new events cross joined with all subscribers subscribed to this event class (I have to reference the subscription table for this, not the view). Then in the ScheduledRule node, after notifications have been generated, I update the sent flag. This allows me to track which events have been sent to whom and block people from getting duplicates.

Here's an example - the GUID I use here can be any unique value that you can compare against. This goes in the EventClass node:

<ChronicleRule><RuleName>MyEventsChronRule</RuleName><Action>
  INSERT INTO MyEventsChron(GUID, SubscriberID)
  SELECT e.GUID, s.SubscriberID
  FROM MyEvents e
  CROSS JOIN NSMySubscriptionsSubscriptions s
  WHERE e.GUID NOT IN (SELECT GUID FROM MyEventsChron)
  </Action></ChronicleRule><Chronicles><Chronicle><ChronicleName>MyEventsChron</ChronicleName><SqlSchema><SqlStatement>
   IF EXISTS(SELECT name FROM dbo.sysobjects WHERE name = 'MyEventsChron') DROP TABLE dbo.MyEventsChron
   CREATE TABLE MyEventsChron(
   [GUID] nvarchar(50),
   [SubscriberID] nvarchar(255),
   [Sent] bit DEFAULT(0)
   )
   </SqlStatement></SqlSchema></Chronicle></Chronicles>

This goes in the SubscriptionClass node:

<ScheduledRules><ScheduledRule><RuleName>MySubscriptionsRule</RuleName><Action>
    SELECT dbo.MyNotificationNotify(s.SubscriberId,
    s.DeviceName,
    s.SubscriberLocale,
    e.GUID
)
    FROM MyEventsChron e
    CROSS JOIN MySubscriptions s
    WHERE e.Sent = 0 and e.SubscriberID = s.SubscriberID

    UPDATE MyEventsChron
    SET Sent = 1
    WHERE SubscriberID IN (SELECT SubscriberID
    FROM MySubscriptions)
    </Action></ScheduledRule></ScheduledRules>

Hope this helps somebody... I sure had a hard time figuring it out...<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Scheduled Rule Error Redux (Attn Diane)