microsoft.public.sqlserver.notificationsvcs
[Top] [All Lists]

NSRuleFiring_SPID Contains no data - No data in Notification View

Subject: NSRuleFiring_SPID Contains no data - No data in Notification View
From: raj
Date: Fri, 06 Jul 2007 12:17:15 -0700
Newsgroups: microsoft.public.sqlserver.notificationsvcs
Hi All,

We are facing a problem where no data is inserted into the
NotificationClass table from the Subscription Class. Due to this we do
not receive data in the email body. We are using a
CustomDelieveryProtocol. Everything is working fine but no data in
the
"[ListingStatusAlert].[StatusNotification]" table as mentioned below
in the app def file. The reason for no data in the
[ListingStatusAlert].[StatusNotification] view is that there is no
record in the [ListingStatusAlert].[NSRuleFiring_SPID] table. Because
of this the view "[ListingStatusAlert].[StatusNotification" does not
returns any data and as a result the email does not contain any custom
data.

We are not able to figure out as to why there is no data in the
[ListingStatusAlert].[NSRuleFiring_SPID] table.

Below is the Application Def File and also the Instance Config file.
The Instance Config file contains the detials of the 6 applications
but we have posted only one Application Def file.

Please let us know whats the reason behind this and is there any
config setting we are missing.

-----------------------------------------------------------------------------------------------------------------------------
The contents of the Application Definition File are:
-----------------------------------------------------------------------------------------------------------------------------
<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">

  <Database>
    <DatabaseName>ITM_NS_DEV</DatabaseName>
    <SchemaName>ListingStatusAlert</SchemaName>
  </Database>

  <EventClasses>
    <EventClass>
      <EventClassName>EvtListingStatusChanged</EventClassName>
      <Schema>
        <Field>
          <FieldName>listing_id</FieldName>
          <FieldType>INT</FieldType>
          <FieldTypeMods>NOT NULL</FieldTypeMods>
        </Field>
        <Field>
          <FieldName>listing_name</FieldName>
          <FieldType>NVARCHAR(255)</FieldType>
          <FieldTypeMods>NOT NULL</FieldTypeMods>
        </Field>
      </Schema>

      <Chronicles>
        <Chronicle>
          <ChronicleName>ListingStatusChronicle</ChronicleName>
          <SqlSchema>
            <SqlStatement>
              --Handle update by dropping the table
              --first if it exists.
              IF EXISTS (
              SELECT so.name
              FROM sys.objects so
              JOIN sys.schemas sc ON so.schema_id = sc.schema_id
              WHERE so.name = 'ListingStatusChangedLog'
              AND so.type = 'U'
              AND sc.name = 'ListingStatusAlert'
              )
              DROP TABLE [ListingStatusAlert].
[ListingStatusChangedLog]
            </SqlStatement>
            <SqlStatement>
              -- Create the table.
              CREATE TABLE [ListingStatusAlert].
[ListingStatusChangedLog]
              (
              listing_id      INT         NOT NULL,
              TimeAdded   DATETIME    NOT NULL
              )
            </SqlStatement>
          </SqlSchema>
        </Chronicle>
        <Chronicle>
          <ChronicleName>ListingStatusSQLProviderChronicle</
ChronicleName>
          <SqlSchema>
            <SqlStatement>
              --Handle update by dropping the table
              --first if it exists.
              IF EXISTS (
              SELECT so.name
              FROM sys.objects so
              JOIN sys.schemas sc ON so.schema_id = sc.schema_id
              WHERE so.name = 'ListingStatusSQLProviderRunTime'
              AND so.type = 'U'
              AND sc.name = 'ListingStatusAlert'
              )
              DROP TABLE [ListingStatusAlert].
[ListingStatusSQLProviderRunTime]
            </SqlStatement>
            <SqlStatement>
              CREATE TABLE [ListingStatusAlert].
[ListingStatusSQLProviderRunTime]
              (
              LastRunTime   DATETIME    NOT NULL
              )
            </SqlStatement>
            <SqlStatement>
              -- GRANT permissions on the table.
              GRANT SELECT ON [ListingStatusAlert].
[ListingStatusSQLProviderRunTime] TO [NSEventProvider]
              GRANT UPDATE ON [ListingStatusAlert].
[ListingStatusSQLProviderRunTime] TO [NSEventProvider]

              GRANT SELECT ON [ListingStatusAlert].
[ListingStatusSQLProviderRunTime] TO [NSRunService]
              GRANT UPDATE ON [ListingStatusAlert].
[ListingStatusSQLProviderRunTime] TO [NSRunService]
            </SqlStatement>
            <SqlStatement>
              INSERT INTO [ListingStatusAlert].
[ListingStatusSQLProviderRunTime](LastRunTime)
              VALUES (GETUTCDATE())
            </SqlStatement>
          </SqlSchema>
        </Chronicle>
      </Chronicles>
      <ChronicleRule>
        <RuleName>UpdateListingStatusChangedLog</RuleName>
        <Action>

          DELETE FROM [ListingStatusAlert].[ListingStatusChangedLog]

          INSERT INTO [ListingStatusAlert].[ListingStatusChangedLog]
(listing_id, TimeAdded)
          SELECT events.listing_id, GETUTCDATE()
          FROM   [ListingStatusAlert].[EvtListingStatusChanged] events
        </Action>
      </ChronicleRule>
    </EventClass>
  </EventClasses>

  <SubscriptionClasses>
    <SubscriptionClass>
      <SubscriptionClassName>SubListingStatusChange</
SubscriptionClassName>
      <Schema>

        <Field>
          <FieldName>UserName</FieldName>
          <FieldType>NVARCHAR(255)</FieldType>
          <FieldTypeMods>NOT NULL</FieldTypeMods>
        </Field>
        <Field>
          <FieldName>DeviceName</FieldName>
          <FieldType>nvarchar(255)</FieldType>
          <FieldTypeMods>not null</FieldTypeMods>
        </Field>
        <Field>
          <FieldName>SubscriberLocale</FieldName>
          <FieldType>nvarchar(10)</FieldType>
          <FieldTypeMods>not null</FieldTypeMods>
        </Field>

      </Schema>

      <EventRules>
        <EventRule>
          <RuleName>MatchListingsStatusChange</RuleName>
          <Action>
            -- Store the current processing time for use later.
            DECLARE @CurrentProcessingTime DATETIME
            SELECT @CurrentProcessingTime = GETUTCDATE()

            --Insert rows into Processing Times chronicle for new
subscriptions.
            INSERT  INTO [ListingStatusAlert].
[ListingStatusChangeProcessingTimes](SubscriptionId,
            LastProcessingTime)
            SELECT  subscriptions.SubscriptionId,
subscriptions.Created
            FROM    [ListingStatusAlert].[SubListingStatusChange]
subscriptions
            WHERE   NOT EXISTS (
            SELECT  chron.SubscriptionId
            FROM    [ListingStatusAlert].
[ListingStatusChangeProcessingTimes] chron
            WHERE   chron.SubscriptionId =
subscriptions.SubscriptionId
            )

            INSERT INTO [ListingStatusAlert].[StatusNotification]
 
(SubscriberId,DeviceName,subscriberlocale,listing_name,listing_id,subscriber_name)
            SELECT subscriptions.SubscriberId,
subscriptions.DeviceName,
            subscriptions.subscriberlocale,
            listing.listing_name,
            listing.listing_Id,
            subscriptions.UserName
            FROM        [ListingStatusAlert].[ListingStatusChangedLog]
eventlog
            INNER JOIN ITM_DEV.dbo.[LISTING] listing ON
eventlog.listing_id = listing.listing_id
            INNER JOIN ITM_DEV.dbo.[aspnet_Users] users ON
users.UserId = listing.UserId
            INNER JOIN [ListingStatusAlert].[SubListingStatusChange]
subscriptions ON
            subscriptions.UserName = users.UserName
            INNER JOIN   [ListingStatusAlert].
[ListingStatusChangeProcessingTimes] chron
            ON subscriptions.SubscriptionId = chron.SubscriptionId
            WHERE   eventlog.TimeAdded &gt;= chron.LastProcessingTime
            AND eventlog.TimeAdded &lt; @CurrentProcessingTime

            --Update Processing Time Chronicle for those
            --subscriptions just processed.
            UPDATE  [ListingStatusAlert].
[ListingStatusChangeProcessingTimes]
            SET     LastProcessingTime = @CurrentProcessingTime
            FROM    [ListingStatusAlert].
[ListingStatusChangeProcessingTimes] chron
            INNER JOIN [ListingStatusAlert].[SubListingStatusChange]
subscriptions
            ON chron.SubscriptionId = subscriptions.SubscriptionId

            -- Update the chronicle, setting the current run time
            -- as the last run time.
            UPDATE  [ListingStatusAlert].
[ListingStatusSQLProviderRunTime]
            SET     LastRunTime = @CurrentProcessingTime
            FROM    [ListingStatusAlert].
[ListingStatusChangeProcessingTimes] chron
            JOIN    [ListingStatusAlert].[SubListingStatusChange]
subscriptions
            ON chron.SubscriptionId = subscriptions.SubscriptionId

          </Action>
          <EventClassName>EvtListingStatusChanged</EventClassName>
        </EventRule>
      </EventRules>

      <Chronicles>
        <Chronicle>
          <ChronicleName>SubListingStatusChronicle</ChronicleName>
          <SqlSchema>
            <SqlStatement>
              --Handle update by dropping the table
              --first if it exists.
              IF EXISTS (
              SELECT so.name
              FROM sys.objects so
              INNER JOIN sys.schemas sc ON so.schema_id = sc.schema_id
              WHERE so.name = 'ListingStatusChangeProcessingTimes'
              AND so.type = 'U'
              AND sc.name = 'ListingStatusAlert'
              )
              DROP TABLE [ListingStatusAlert].
[ListingStatusChangeProcessingTimes]
            </SqlStatement>
            <SqlStatement>
              -- Create the table.
              CREATE TABLE [ListingStatusAlert].
[ListingStatusChangeProcessingTimes]
              (
              SubscriptionId      BIGINT      NOT NULL,
              LastProcessingTime  DATETIME    NOT NULL
              )
            </SqlStatement>
          </SqlSchema>
        </Chronicle>
      </Chronicles>
    </SubscriptionClass>
  </SubscriptionClasses>

  <NotificationClasses>
    <NotificationClass>
      <NotificationClassName>StatusNotification</
NotificationClassName>
      <Schema>
        <Fields>
          <Field>
            <FieldName>listing_name</FieldName>
            <FieldType>NVARCHAR(255)</FieldType>
          </Field>

          <Field>
            <FieldName>listing_id</FieldName>
            <FieldType>INT</FieldType>

          </Field>

          <Field>
            <FieldName>subscriber_name</FieldName>
            <FieldType>NVARCHAR(255)</FieldType>

          </Field>

        </Fields>
      </Schema>
      <ContentFormatter>
        <ClassName>XsltFormatter</ClassName>
        <Arguments>
          <Argument>
            <Name>XsltBaseDirectoryPath</Name>
            <Value>%_ApplicationBaseDirectoryPath_%\XslTransforms</
Value>
          </Argument>
          <Argument>
            <Name>XsltFileName</Name>
            <Value>ListingStatusChange.xslt</Value>
          </Argument>
        </Arguments>
      </ContentFormatter>
      <DigestDelivery>true</DigestDelivery>
      <Protocols>
        <Protocol>
          <ProtocolName>ITMCustomDeliveryProtocol</ProtocolName>
          <Fields>
            <Field>
              <FieldName>FromMember</FieldName>
 
<SqlExpression>&apos;[email protected]&apos;</
SqlExpression>
            </Field>
            <Field>
              <FieldName>Subject</FieldName>
              <SqlExpression>&apos;Listing Alert: &apos;+CONVERT
(NVARCHAR(30), GETDATE())</SqlExpression>
            </Field>
          </Fields>
        </Protocol>
      </Protocols>
    </NotificationClass>
  </NotificationClasses>

  <Providers>
    <HostedProvider>
      <ProviderName>ListingStatusSQLProvider</ProviderName>
      <ClassName>SQLProvider</ClassName>
      <SystemName>%_NSServer_%</SystemName>
      <Schedule>
        <Interval>PT60S</Interval>
        <!-- run every 60 seconds-->
      </Schedule>
      <Arguments>
        <Argument>
          <Name>EventsQuery</Name>
          <Value>
            -- Declare variables used to store the last and current
            -- run times.
            DECLARE @currentRunTime DATETIME
            DECLARE @lastRunTime DATETIME

            --Select the last run time from the chronicle table.
            SELECT @lastRunTime = LastRunTime
            FROM [ListingStatusAlert].
[ListingStatusSQLProviderRunTime]

            -- Obtain the current run time.
            SELECT  @currentRunTime = GETUTCDATE()

            -- Update the chronicle, setting the current run time
            -- as the last run time.

            UPDATE [ListingStatusAlert].
[ListingStatusSQLProviderRunTime]
            SET [email protected]

            -- Select those listing which are in pending state and
whose
            -- auction_begindatetime has reached to the current date.
            SELECT  l.listing_id, l.listing_name
            FROM    ITM_DEV.dbo.LISTING l
            WHERE l.status_id=ITM_DEV.dbo.fnEMG_GetStatusID('Pending',
'LISTING')
            AND l.active=1
            AND DATEDIFF(mi,CONVERT(SMALLDATETIME,@currentRunTime),
            CONVERT(SMALLDATETIME,l.auction_begin_datetime)) &lt;=0

            -- update status as Active of such listings
            UPDATE ITM_DEV.dbo.LISTING SET
status_id=ITM_DEV.dbo.fnEMG_GetStatusID('Active', 'LISTING')
            WHERE status_id=ITM_DEV.dbo.fnEMG_GetStatusID('Pending',
'LISTING')
            AND active=1
            AND DATEDIFF(mi,CONVERT(SMALLDATETIME,@currentRunTime),
            CONVERT(SMALLDATETIME,auction_begin_datetime)) &lt;=0
          </Value>
        </Argument>
        <Argument>
          <Name>EventClassName</Name>
          <Value>EvtListingStatusChanged</Value>
        </Argument>
      </Arguments>

    </HostedProvider>

  </Providers>

  <Generator>
    <SystemName>%_NSServer_%</SystemName>
  </Generator>

  <Distributors>
    <Distributor>
      <SystemName>%_NSServer_%</SystemName>
      <QuantumDuration>PT15S</QuantumDuration>
    </Distributor>
  </Distributors>

  <ApplicationExecutionSettings>
    <QuantumDuration>PT15S</QuantumDuration>
  </ApplicationExecutionSettings>

</Application>
-----------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------
The contents of the Instance Configuration File are:
-----------------------------------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<NotificationServicesInstance xmlns:xsd="http://www.w3.org/2001/
XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xmlns="http://www.microsoft.com/MicrosoftNotificationServices/
ConfigurationFileSchema">

  <InstanceName>TestNS</InstanceName>

  <ParameterDefaults>
                <Parameter>
                        <Name>_SQLServer_</Name>
                        <Value>NSServer</Value>
                </Parameter>
                <Parameter>
                        <Name>_NSServer_</Name>
                        <Value>NSServer</Value>
                </Parameter>
                <Parameter>
                        <Name>_InstanceBaseDirectoryPath_</Name>
                        <Value>C:\NS\ListingTasks</Value>
                </Parameter>
    <Parameter>
      <Name>_CustomDeliveryProtocolPath_</Name>
      <Value>C:\NS\CustomComponent\CorrespondenceDeliveryProtocol</
Value>
    </Parameter>
        </ParameterDefaults>

  <SqlServerSystem>%_SQLServer_%</SqlServerSystem>

  <Database>
    <DatabaseName>RD_NS</DatabaseName>
    <SchemaName>NSInstance</SchemaName>
  </Database>

  <Applications>
    <Application>
      <ApplicationName>ListingAlerts</ApplicationName>
      <BaseDirectoryPath>%_InstanceBaseDirectoryPath_%
\ListingTasksApp</BaseDirectoryPath>
      <ApplicationDefinitionFilePath>AppDefListingStatusChange.xml</
ApplicationDefinitionFilePath>
      <Parameters>
        <Parameter>
          <Name>_NSServer_</Name>
          <Value>%_NSServer_%</Value>
        </Parameter>
        <Parameter>
          <Name>_ApplicationBaseDirectoryPath_</Name>
          <Value>%_InstanceBaseDirectoryPath_%\ListingTasksApp</Value>
        </Parameter>
      </Parameters>


    </Application>

    <Application>
      <ApplicationName>ListingExpiredStatusAlerts</ApplicationName>
      <BaseDirectoryPath>%_InstanceBaseDirectoryPath_%
\ListingsExpired</BaseDirectoryPath>
      <ApplicationDefinitionFilePath>AppDefListingExpired.xml</
ApplicationDefinitionFilePath>
      <Parameters>
        <Parameter>
          <Name>_NSServer_</Name>
          <Value>%_NSServer_%</Value>
        </Parameter>
        <Parameter>
          <Name>_ApplicationBaseDirectoryPath_</Name>
          <Value>%_InstanceBaseDirectoryPath_%\ListingsExpired</Value>
        </Parameter>
      </Parameters>
    </Application>

    <Application>
      <ApplicationName>ListingBidExpiredStatusAlerts</ApplicationName>
      <BaseDirectoryPath>%_InstanceBaseDirectoryPath_%
\ListingBidExpired</BaseDirectoryPath>
      <ApplicationDefinitionFilePath>AppDefListingBidExpired.xml</
ApplicationDefinitionFilePath>
      <Parameters>
        <Parameter>
          <Name>_NSServer_</Name>
          <Value>%_NSServer_%</Value>
        </Parameter>
        <Parameter>
          <Name>_ApplicationBaseDirectoryPath_</Name>
          <Value>%_InstanceBaseDirectoryPath_%\ListingBidExpired</
Value>
        </Parameter>
      </Parameters>
    </Application>

    <Application>
      <ApplicationName>ListingBidClosedStatusAlerts</ApplicationName>
      <BaseDirectoryPath>%_InstanceBaseDirectoryPath_%
\ListingBidClosed</BaseDirectoryPath>
      <ApplicationDefinitionFilePath>AppDefListingBidClosed.xml</
ApplicationDefinitionFilePath>
      <Parameters>
        <Parameter>
          <Name>_NSServer_</Name>
          <Value>%_NSServer_%</Value>
        </Parameter>
        <Parameter>
          <Name>_ApplicationBaseDirectoryPath_</Name>
          <Value>%_InstanceBaseDirectoryPath_%\ListingBidClosed</
Value>
        </Parameter>
      </Parameters>
    </Application>

    <Application>
      <ApplicationName>ListingOrderStatusAlerts</ApplicationName>
      <BaseDirectoryPath>%_InstanceBaseDirectoryPath_%
\ListingOrderStatus</BaseDirectoryPath>
      <ApplicationDefinitionFilePath>AppDefListingOrderStatus.xml</
ApplicationDefinitionFilePath>
      <Parameters>
        <Parameter>
          <Name>_NSServer_</Name>
          <Value>%_NSServer_%</Value>
        </Parameter>
        <Parameter>
          <Name>_ApplicationBaseDirectoryPath_</Name>
          <Value>%_InstanceBaseDirectoryPath_%\ListingOrderStatus</
Value>
        </Parameter>
      </Parameters>
    </Application>

  </Applications>

  <DeliveryChannels>
    <DeliveryChannel>
      <DeliveryChannelName>RDCustomDeliveryChannel</
DeliveryChannelName>
      <ProtocolName>RDCustomDeliveryProtocol</ProtocolName>
      <Arguments>
        <Argument>
          <Name>Server</Name>
          <Value>%_SQLServer_%</Value>
        </Argument>
        <Argument>
          <Name>Database</Name>
          <Value>RD_DEV</Value>
        </Argument>
        <Argument>
          <Name>storedProc</Name>
          <Value>dbo.spRD_SendNotification</Value>
        </Argument>
        <Argument>
          <Name>CommandTimeout</Name>
          <Value>30</Value>
        </Argument>
      </Arguments>
    </DeliveryChannel>
  </DeliveryChannels>

  <Protocols>
    <Protocol>
      <ProtocolName>RDCustomDeliveryProtocol</ProtocolName>
 
<ClassName>RD.NS.CustomDeliveryProtocol.CorrespondenceDeliveryProtocol</
ClassName>
      <AssemblyName>%_CustomDeliveryProtocolPath_%\bin\Debug
\CorrespondenceDeliveryProtocol.dll</AssemblyName>
    </Protocol>
  </Protocols>

  <EncryptArguments>false</EncryptArguments>

</NotificationServicesInstance>
-----------------------------------------------------------------------------------------------------------------------------


<Prev in Thread] Current Thread [Next in Thread>
  • NSRuleFiring_SPID Contains no data - No data in Notification View, raj <=