sql server notification services: I am building an app using Shyam Pather's book as a reference. I set up my
Scheduled Subscription Processing Times chronicle table no problem.

At the beginning of the <ScheduledRule> <Action>, the T-SQL is supposed to
insert a record into the chron table for any NEW subscriptions.

I can watch what is returned by the subscription view as the schedule come
around and while the quantum is still active, I can manually run the script
to insert the appropriate record into the chron table from a SSMS window.
BUT, when that SAME script is used near the top of the <Action> for the
scheduled subscription, it does NOT insert any rows, even though they are
available.

Does this make sense? Any ideas out there?

TIA
--

[quoted text, click to view]


Can you post the code?

I don't have his book in front of me, but I seem to recall that the rule
only updated a row in the chron table. The row was placed there during
when the table was created.

HTH...

Joe


--
Joe Webb
SQL Server MVP
http://www.sqlns.com

Joe:
It's kind of lengthy, but both scripts are posted. Basically, the
Subscription <Action> does three things: 1) Add rows to the chron table for
any NEW subscriptions that were added since the last time; 2) Process the
matching logic; 3) Update the subscription chronicle table for any
subscription just processed.

Shyam Pather's code, taken EXACTLY from his sample scripts on the book's CD
is as follows:

************** Start of Shyam's Code *******
<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 [SongAlerts].[NewSongByGenreSubscriptionProcessingTimes]
(SubscriptionId, LastProcessingTime)
SELECT subscriptions.SubscriptionId, subscriptions.Created
FROM [SongAlerts].[NewSongByGenre] subscriptions
WHERE NOT EXISTS (
SELECT chron.SubscriptionId
FROM [SongAlerts].[NewSongByGenreSubscriptionProcessingTimes] chron
WHERE chron.SubscriptionId = subscriptions.SubscriptionId
)

--Do the match.** (Matching logic removed for brevity) **

--Update Processing Time Chronicle for those
--subscriptions just processed.
UPDATE [SongAlerts].[NewSongByGenreSubscriptionProcessingTimes]
SET LastProcessingTime = @CurrentProcessingTime
FROM [SongAlerts].[NewSongByGenreSubscriptionProcessingTimes] chron
JOIN [SongAlerts].[NewSongByGenre] subscriptions
ON chron.SubscriptionId = subscriptions.SubscriptionId

</Action>
**************** END***************

And my code is:
************** Start *********
<Action>
--Store the current Processing Time for later use

DECLARE @CurrentProcessingTime DATETIME
SELECT @CurrentProcessingTime = GETUTCDATE()


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

-- Matching logic goes here


--Update Processing Time Chronicle for those
--subscriptions just processed.
UPDATE [JOS].[Invoice_Changes_Processing_Times]
SET LastProcessingTime = @CurrentProcessingTime
FROM [JOS].[Invoice_Changes_Processing_Times] chron
JOIN [JOS].[NewSongByGenre] subscriptions
ON chron.SubscriptionId = subscriptions.SubscriptionId

</Action>

************ END *********

I have even gone so far as to copy and paste sections of his code into mine
and just do a Find and Replace to get the correct schema and table names, but
even THAT does not work.

I'm at my wit's end on this!

Thanks
--
Todd Chittenden



[quoted text, click to view]