locked
Notification Services - Chronicle does not get updated RRS feed

  • Question

  • Hi,

    I'm currently developing a NS application warning subscribers when their pay day is due for paying the rent of their student home.

    I have an external database containing contract data. My application should monthly send an email to the student 10 days before he/she should pay the monthly fee.

    Following the example provided in the book of Shyam Pather, I created a Chronicle, making sure that only contract data of newly inserted contracts is added to the events table.

    The service can run, I dont get any error reports but the Chronicle table (just one value containing a date and time) is never updated.

    Can someone tell me why it doens't do this ?

     Here's the code for my ADF:

    <?xml version="1.0" encoding="utf-8" ?>

    <Application xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

    xmlns="http://www.microsoft.com/MicrosoftNotificationServices/ApplicationDefinitionFileSchema">

    <EventClasses>

    <EventClass>

    <EventClassName>ContractAdded</EventClassName>

    <Schema>

    <Field>

    <FieldName>ContractId</FieldName>

    <FieldType>BIGINT</FieldType>

    <FieldTypeMods>NOT NULL</FieldTypeMods>

    </Field>

    </Schema>

    <Chronicles>

    <Chronicle>

    <ChronicleName>SQLProviderChronicle</ChronicleName>

    <SqlSchema>

    <SqlStatement>

    IF OBJECT_ID(N'SQLProviderRunTime') IS NOT NULL

    DROP TABLE SQLProviderRunTime

    CREATE TABLE SQLProviderRunTime

    (

    LastRunTime DATETIME NOT NULL

    )

    </SqlStatement>

    <SqlStatement>

    INSERT INTO SqlProviderRunTime(LastRunTime)

    VALUES (GETDATE())

    </SqlStatement>

    </SqlSchema>

    </Chronicle>

    </Chronicles>

    </EventClass>

    </EventClasses>

    <SubscriptionClasses></SubscriptionClasses>

    <NotificationClasses></NotificationClasses>

    <Providers>

    <HostedProvider>

    <ProviderName>ContractAddedSqlProvider</ProviderName>

    <ClassName>SQLProvider</ClassName>

    <SystemName>Home</SystemName>

    <Schedule>

    <Interval>P0DT00H00M30S</Interval>

    </Schedule>

    <Arguments>

    <Argument>

    <Name>EventsQuery</Name>

    <Value>

    DECLARE @lastRunTime DATETIME

    DECLARE @currentRunTime DATETIME

    SELECT @lastRunTime= LastRunTime

    FROM SQLProviderRunTime

    SELECT @currentRunTime= GETDATE()

    UPDATE SQLProviderRunTime

    SET LastRunTime= @currentRunTime

    SELECT tblCtrct.ContractId

    FROM [Quartier Latin].dbo.tblContract tblCtrct

    WHERE tblCtrct.InsertionDate &gt; @lastRunTime AND tblCtrct.InsertionDate &lt; @currentRunTime

    </Value>

    </Argument>

    </Arguments>

    </HostedProvider>

    </Providers>

    <Generator>

    <SystemName>Home</SystemName>

    </Generator>

    <Distributors>

    <Distributor>

    <SystemName>Home</SystemName>

    </Distributor>

    </Distributors>

    </Application>

    Can anyone see an error ?

    Thanks,

    Jeke 

    Friday, May 12, 2006 8:18 AM

Answers

  • Hi,

    Sorry Joe, I had already found the error, the answer was in your book and confirmed in the book of Shyam Pather.

    Problem was I forgot the EventClassName argument. And that's where the T-SQL statement is to update my Chron table...

    My application is up and running ;-)

    Thanks,

    Jeke

    Wednesday, May 24, 2006 11:49 AM

All replies

  • Where is your ChronicleRule node? That's where you define the T-SQL to populate the Chron table.

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/ntsrvpg9/html/3c00a391-dd08-4f7c-b424-0111524881b4.htm

    HTH...

    Joe

     

    Wednesday, May 24, 2006 11:40 AM
  • Hi,

    Sorry Joe, I had already found the error, the answer was in your book and confirmed in the book of Shyam Pather.

    Problem was I forgot the EventClassName argument. And that's where the T-SQL statement is to update my Chron table...

    My application is up and running ;-)

    Thanks,

    Jeke

    Wednesday, May 24, 2006 11:49 AM