|
|
You're in the
sql server notification services
group:Scheduled notification
sql server notification services:
I am able to generate event based notifications. But I am not getting mails from scheduled notification subscriptions. What could be the problem.? Here goes the ADF file......... <SubscriptionClass> <ScheduledRules> <ScheduledRule> <RuleName>CenterMetricsWeeklyReportRule</RuleName> <Action> SELECT dbo.CenterMetricsNotificationNotify( s.SubscriberId,s.DeviceName,s.SubscriberLocale, 'name1','id1','age1' FROM CenterMetricsSubscriptions s </Action> </ScheduledRule> </ScheduledRules> </SubscriptionClass> <NotificationClass> <NotificationClassName>CenterMetricsNotification</NotificationClassName> <Schema> <Fields> <Field> <FieldName>name1</FieldName> <FieldType>varchar(20)</FieldType> </Field> <Field> <FieldName>id1</FieldName> <FieldType>varchar(20)</FieldType> </Field> <Field> <FieldName>age1</FieldName> <FieldType>varchar(20)</FieldType> </Field> </Fields> </Schema> <ContentFormatter> <ClassName>XsltFormatter</ClassName> <Arguments> <Argument> <Name>XsltBaseDirectoryPath</Name> <Value>%_BaseDirectoryPath_%\Subscribe\Include</Value> </Argument> <Argument> <Name>XsltFileName</Name> <Value>CenterMetrics.xslt</Value> </Argument> </Arguments> </ContentFormatter> <DigestDelivery>true</DigestDelivery> <Protocols> <Protocol> <ProtocolName>SMTP</ProtocolName> <Fields> <Field> <FieldName>Subject</FieldName> <SqlExpression>'test'</SqlExpression> </Field> <Field> <FieldName>BodyFormat</FieldName> <SqlExpression>'html'</SqlExpression> </Field> <Field> <FieldName>From</FieldName> <SqlExpression>'test@test.com'</SqlExpression> </Field> <Field> <FieldName>Priority</FieldName> <SqlExpression>'Normal'</SqlExpression> </Field> <Field> <FieldName>To</FieldName> <SqlExpression>DeviceAddress</SqlExpression> </Field> </Fields> </Protocol> </Protocols> </NotificationClass> when I run the select on the view CenterMetricsSubscriptions it returns 0 rows. I created some subscribers with proper time zones and subscribers are getting created. Instead of the CenterMetricsSubscriptions view, If I give the table NSCenterMetricsSubscriptionsSubscriptions, then I am getting notifications. But it generates notifications for all subscribers at same time which is wrong. What I am missing? Thanks for your help. Regards, R.Balaji
Hi R. Balaji -
Looks like the matching rule in your scheduled subscription is the culprit. It should join the event data with the subscription data. Something like this: SELECT dbo.CenterMetricsNotificationNotify( s.SubscriberId,s.DeviceName,s.SubscriberLocale, e.Name, e.Id, e.Age) FROM EventChron e, CenterMetricsSubscriptions s WHERE e.Id = s.Id Since your posting didn't include the full ADF, I cannot tell exactly what your Chron table looks like so the above matching rule is only an approximation. HTH... Joe Webb SQL Server MVP ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 [quoted text, click to view] Balaji Ramachandran wrote:
> Hi, > I am able to generate event based notifications. > > But I am not getting mails from scheduled notification subscriptions. > > What could be the problem.? > > Here goes the ADF file......... > > <SubscriptionClass> > <ScheduledRules> > <ScheduledRule> > <RuleName>CenterMetricsWeeklyReportRule</RuleName> > <Action> > SELECT dbo.CenterMetricsNotificationNotify( > s.SubscriberId,s.DeviceName,s.SubscriberLocale, > 'name1','id1','age1' > FROM CenterMetricsSubscriptions s > </Action> > </ScheduledRule> > </ScheduledRules> > </SubscriptionClass> > > > <NotificationClass> > <NotificationClassName>CenterMetricsNotification</NotificationClassName> > <Schema> > <Fields> > <Field> > <FieldName>name1</FieldName> > <FieldType>varchar(20)</FieldType> > </Field> > <Field> > <FieldName>id1</FieldName> > <FieldType>varchar(20)</FieldType> > </Field> > <Field> > <FieldName>age1</FieldName> > <FieldType>varchar(20)</FieldType> > </Field> > </Fields> > </Schema> > <ContentFormatter> > <ClassName>XsltFormatter</ClassName> > <Arguments> > <Argument> > <Name>XsltBaseDirectoryPath</Name> > <Value>%_BaseDirectoryPath_%\Subscribe\Include</Value> > </Argument> > <Argument> > <Name>XsltFileName</Name> > <Value>CenterMetrics.xslt</Value> > </Argument> > </Arguments> > </ContentFormatter> > <DigestDelivery>true</DigestDelivery> > <Protocols> > <Protocol> > <ProtocolName>SMTP</ProtocolName> > <Fields> > <Field> > <FieldName>Subject</FieldName> > <SqlExpression>'test'</SqlExpression> > </Field> > <Field> > <FieldName>BodyFormat</FieldName> > <SqlExpression>'html'</SqlExpression> > </Field> > <Field> > <FieldName>From</FieldName> > <SqlExpression>'test@test.com'</SqlExpression> > </Field> > <Field> > <FieldName>Priority</FieldName> > <SqlExpression>'Normal'</SqlExpression> > </Field> > <Field> > <FieldName>To</FieldName> > <SqlExpression>DeviceAddress</SqlExpression> > </Field> > </Fields> > </Protocol> > </Protocols> > </NotificationClass> > > > when I run the select on the view CenterMetricsSubscriptions it returns 0 > rows. > I created some subscribers with proper time zones and subscribers are > getting created. > > Instead of the CenterMetricsSubscriptions view, If I give the table > NSCenterMetricsSubscriptionsSubscriptions, then I am getting notifications. > But it generates notifications for all subscribers at same time which is > wrong. > > What I am missing? > > Thanks for your help. > > Regards, > R.Balaji
Whoops! Fired off the prior response before including the following comment.
I'm assuming the <SubscriptionClass> node has a <SubscriptionClassName> element that was omitted for brevity like so: <SubscriptionClassName>CenterMetricsSubscriptions</SubscriptionClassName> If not, it should and the SubscriptionClassName value is the table name used in the matching rule in my prior post. HTH... Joe Webb SQL Server MVP ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 [quoted text, click to view] Joe Webb wrote:
> Hi R. Balaji - > > Looks like the matching rule in your scheduled subscription is the > culprit. It should join the event data with the subscription data. > Something like this: > > SELECT dbo.CenterMetricsNotificationNotify( > s.SubscriberId,s.DeviceName,s.SubscriberLocale, > e.Name, e.Id, e.Age) > FROM EventChron e, CenterMetricsSubscriptions s > WHERE e.Id = s.Id > > Since your posting didn't include the full ADF, I cannot tell exactly > what your Chron table looks like so the above matching rule is only an > approximation. > > > > > HTH... > Joe Webb > SQL Server MVP > > ~~~ > Get up to speed quickly with SQLNS > http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > > > > Balaji Ramachandran wrote: > >> Hi, >> I am able to generate event based notifications. >> >> But I am not getting mails from scheduled notification subscriptions. >> >> What could be the problem.? >> >> Here goes the ADF file......... >> >> <SubscriptionClass> >> <ScheduledRules> >> <ScheduledRule> >> <RuleName>CenterMetricsWeeklyReportRule</RuleName> >> <Action> >> SELECT dbo.CenterMetricsNotificationNotify( >> s.SubscriberId,s.DeviceName,s.SubscriberLocale, >> 'name1','id1','age1' >> FROM CenterMetricsSubscriptions s >> </Action> >> </ScheduledRule> >> </ScheduledRules> >> </SubscriptionClass> >> >> >> <NotificationClass> >> <NotificationClassName>CenterMetricsNotification</NotificationClassName> >> <Schema> >> <Fields> >> <Field> >> <FieldName>name1</FieldName> >> <FieldType>varchar(20)</FieldType> >> </Field> >> <Field> >> <FieldName>id1</FieldName> >> <FieldType>varchar(20)</FieldType> >> </Field> >> <Field> >> <FieldName>age1</FieldName> >> <FieldType>varchar(20)</FieldType> >> </Field> >> </Fields> >> </Schema> >> <ContentFormatter> >> <ClassName>XsltFormatter</ClassName> >> <Arguments> >> <Argument> >> <Name>XsltBaseDirectoryPath</Name> >> <Value>%_BaseDirectoryPath_%\Subscribe\Include</Value> >> </Argument> >> <Argument> >> <Name>XsltFileName</Name> >> <Value>CenterMetrics.xslt</Value> >> </Argument> >> </Arguments> >> </ContentFormatter> >> <DigestDelivery>true</DigestDelivery> >> <Protocols> >> <Protocol> >> <ProtocolName>SMTP</ProtocolName> >> <Fields> >> <Field> >> <FieldName>Subject</FieldName> >> <SqlExpression>'test'</SqlExpression> >> </Field> >> <Field> >> <FieldName>BodyFormat</FieldName> >> <SqlExpression>'html'</SqlExpression> >> </Field> >> <Field> >> <FieldName>From</FieldName> >> <SqlExpression>'test@test.com'</SqlExpression> >> </Field> >> <Field> >> <FieldName>Priority</FieldName> >> <SqlExpression>'Normal'</SqlExpression> >> </Field> >> <Field> >> <FieldName>To</FieldName> >> <SqlExpression>DeviceAddress</SqlExpression> >> </Field> >> </Fields> >> </Protocol> >> </Protocols> >> </NotificationClass> >> >> >> when I run the select on the view CenterMetricsSubscriptions it >> returns 0 rows. >> I created some subscribers with proper time zones and subscribers are >> getting created. >> >> Instead of the CenterMetricsSubscriptions view, If I give the table >> NSCenterMetricsSubscriptionsSubscriptions, then I am getting >> notifications. >> But it generates notifications for all subscribers at same time which >> is wrong. >> >> What I am missing? >> >> Thanks for your help. >> >> Regards, >> R.Balaji
Balaji,
Your match rule looks correct to me. Most likely there's something wrong with the schedule times specified in your subscriptions. If the subscriptions view doesn't show any rows, it's because NS doesn't think any subscriptions are due to fire at that time. My suggestion is to look at the NSScheduledSubscriptionDetails and NSScheduledSubscriptionList stored procedures - they can usually help track down schedule issues. These are described in Books Online. If you have my book, you'll find an explanation of these procedures in Chapter 11. Thanks -shyam -- Learn more about SQL-NS: http://www.amazon.com/exec/obidos/tg/detail/-/0672326647/ --------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm. --------------------------------------------- [quoted text, click to view] "Balaji Ramachandran" <BalajiRamachandran@discussions.microsoft.com> wrote in message news:F3BBE076-B34A-42E0-A595-5AFDFB441D3C@microsoft.com... > Hi, > I am able to generate event based notifications. > > But I am not getting mails from scheduled notification subscriptions. > > What could be the problem.? > > Here goes the ADF file......... > > <SubscriptionClass> > <ScheduledRules> > <ScheduledRule> > <RuleName>CenterMetricsWeeklyReportRule</RuleName> > <Action> > SELECT dbo.CenterMetricsNotificationNotify( > s.SubscriberId,s.DeviceName,s.SubscriberLocale, > 'name1','id1','age1' > FROM CenterMetricsSubscriptions s > </Action> > </ScheduledRule> > </ScheduledRules> > </SubscriptionClass> > > > <NotificationClass> > <NotificationClassName>CenterMetricsNotification</NotificationClassName> > <Schema> > <Fields> > <Field> > <FieldName>name1</FieldName> > <FieldType>varchar(20)</FieldType> > </Field> > <Field> > <FieldName>id1</FieldName> > <FieldType>varchar(20)</FieldType> > </Field> > <Field> > <FieldName>age1</FieldName> > <FieldType>varchar(20)</FieldType> > </Field> > </Fields> > </Schema> > <ContentFormatter> > <ClassName>XsltFormatter</ClassName> > <Arguments> > <Argument> > <Name>XsltBaseDirectoryPath</Name> > <Value>%_BaseDirectoryPath_%\Subscribe\Include</Value> > </Argument> > <Argument> > <Name>XsltFileName</Name> > <Value>CenterMetrics.xslt</Value> > </Argument> > </Arguments> > </ContentFormatter> > <DigestDelivery>true</DigestDelivery> > <Protocols> > <Protocol> > <ProtocolName>SMTP</ProtocolName> > <Fields> > <Field> > <FieldName>Subject</FieldName> > <SqlExpression>'test'</SqlExpression> > </Field> > <Field> > <FieldName>BodyFormat</FieldName> > <SqlExpression>'html'</SqlExpression> > </Field> > <Field> > <FieldName>From</FieldName> > <SqlExpression>'test@test.com'</SqlExpression> > </Field> > <Field> > <FieldName>Priority</FieldName> > <SqlExpression>'Normal'</SqlExpression> > </Field> > <Field> > <FieldName>To</FieldName> > <SqlExpression>DeviceAddress</SqlExpression> > </Field> > </Fields> > </Protocol> > </Protocols> > </NotificationClass> > > > when I run the select on the view CenterMetricsSubscriptions it returns 0 > rows. > I created some subscribers with proper time zones and subscribers are > getting created. > > Instead of the CenterMetricsSubscriptions view, If I give the table > NSCenterMetricsSubscriptionsSubscriptions, then I am getting notifications. > But it generates notifications for all subscribers at same time which is > wrong. > > What I am missing? > > Thanks for your help. > > Regards, > R.Balaji >
Joe,
I don't think this is the problem - not joining an events chron and filtering on an event condition just means the rule will generate notifications for all subcriptions in scope. As I said in my previous post, I think it's a schedule problem. -shyam -- Learn more about SQL-NS: http://www.amazon.com/exec/obidos/tg/detail/-/0672326647/ --------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm. --------------------------------------------- [quoted text, click to view] "Joe Webb" <joew@webbtechsolutions.com> wrote in message news:#A8EDzATFHA.2336@TK2MSFTNGP12.phx.gbl... > Hi R. Balaji - > > Looks like the matching rule in your scheduled subscription is the > culprit. It should join the event data with the subscription data. > Something like this: > > SELECT dbo.CenterMetricsNotificationNotify( > s.SubscriberId,s.DeviceName,s.SubscriberLocale, > e.Name, e.Id, e.Age) > FROM EventChron e, CenterMetricsSubscriptions s > WHERE e.Id = s.Id > > Since your posting didn't include the full ADF, I cannot tell exactly > what your Chron table looks like so the above matching rule is only an > approximation. > > > > > HTH... > Joe Webb > SQL Server MVP > > ~~~ > Get up to speed quickly with SQLNS > http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > > > > Balaji Ramachandran wrote: > > Hi, > > I am able to generate event based notifications. > > > > But I am not getting mails from scheduled notification subscriptions. > > > > What could be the problem.? > > > > Here goes the ADF file......... > > > > <SubscriptionClass> > > <ScheduledRules> > > <ScheduledRule> > > <RuleName>CenterMetricsWeeklyReportRule</RuleName> > > <Action> > > SELECT dbo.CenterMetricsNotificationNotify( > > s.SubscriberId,s.DeviceName,s.SubscriberLocale, > > 'name1','id1','age1' > > FROM CenterMetricsSubscriptions s > > </Action> > > </ScheduledRule> > > </ScheduledRules> > > </SubscriptionClass> > > > > > > <NotificationClass> > > <NotificationClassName>CenterMetricsNotification</NotificationClassName> > > <Schema> > > <Fields> > > <Field> > > <FieldName>name1</FieldName> > > <FieldType>varchar(20)</FieldType> > > </Field> > > <Field> > > <FieldName>id1</FieldName> > > <FieldType>varchar(20)</FieldType> > > </Field> > > <Field> > > <FieldName>age1</FieldName> > > <FieldType>varchar(20)</FieldType> > > </Field> > > </Fields> > > </Schema> > > <ContentFormatter> > > <ClassName>XsltFormatter</ClassName> > > <Arguments> > > <Argument> > > <Name>XsltBaseDirectoryPath</Name> > > <Value>%_BaseDirectoryPath_%\Subscribe\Include</Value> > > </Argument> > > <Argument> > > <Name>XsltFileName</Name> > > <Value>CenterMetrics.xslt</Value> > > </Argument> > > </Arguments> > > </ContentFormatter> > > <DigestDelivery>true</DigestDelivery> > > <Protocols> > > <Protocol> > > <ProtocolName>SMTP</ProtocolName> > > <Fields> > > <Field> > > <FieldName>Subject</FieldName> > > <SqlExpression>'test'</SqlExpression> > > </Field> > > <Field> > > <FieldName>BodyFormat</FieldName> > > <SqlExpression>'html'</SqlExpression> > > </Field> > > <Field> > > <FieldName>From</FieldName> > > <SqlExpression>'test@test.com'</SqlExpression> > > </Field> > > <Field> > > <FieldName>Priority</FieldName> > > <SqlExpression>'Normal'</SqlExpression> > > </Field> > > <Field> > > <FieldName>To</FieldName> > > <SqlExpression>DeviceAddress</SqlExpression> > > </Field> > > </Fields> > > </Protocol> > > </Protocols> > > </NotificationClass> > > > > > > when I run the select on the view CenterMetricsSubscriptions it returns 0 > > rows. > > I created some subscribers with proper time zones and subscribers are > > getting created. > > > > Instead of the CenterMetricsSubscriptions view, If I give the table > > NSCenterMetricsSubscriptionsSubscriptions, then I am getting notifications. > > But it generates notifications for all subscribers at same time which is > > wrong. > > > > What I am missing? > > > > Thanks for your help. > > > > Regards, > > R.Balaji > >
Hi Shyam -
No doubt; without joining to the events table, every subscription in the scope will receive a notification. But given the OP's ADF snippet, I suspect that the app is not going to produce the results he's expecting. For instance, the notification class is looking for fields called name1, id1, and age1, yet in the matching rule it's passing literals. Maybe I'm reading too much into this and making some assumptions I shouldn't, but I think if this were my app to debug, I'd likely start with the matching rule and go from there. Of course, the OP didn't really share what the app is designed to do so I may be leaping to invalid conclusions here. I'd certainly agree that it is often the case that when scheduled subscriptions don't appear as expected, it's because no scheduled subscriptions exist that meet the matching criteria. Thanks Shyam! I'm glad you monitor this group! Joe Webb SQL Server MVP ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 [quoted text, click to view] Shyam Pather [MSFT] wrote:
> Joe, > I don't think this is the problem - not joining an events chron and > filtering on an event condition just means the rule will generate > notifications for all subcriptions in scope. > > As I said in my previous post, I think it's a schedule problem. > -shyam
Hi Balaji -
I've developed a C# application that I use to create test subscribers, devices, and subscriptions for the SQLNS apps that I develop. It's written so that it works with all instances and applications on the local machine. If you're interested, it's available for free on the downloads page of my web site. http://www.webbtechsolutions.com. HTH... Joe Webb SQL Server MVP ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 [quoted text, click to view] Shyam Pather [MSFT] wrote:
> Balaji, > Your match rule looks correct to me. Most likely there's something wrong > with the schedule times specified in your subscriptions. If the > subscriptions view doesn't show any rows, it's because NS doesn't think any > subscriptions are due to fire at that time. > > My suggestion is to look at the NSScheduledSubscriptionDetails and > NSScheduledSubscriptionList stored procedures - they can usually help track > down schedule issues. These are described in Books Online. If you have my > book, you'll find an explanation of these procedures in Chapter 11. > > Thanks > -shyam
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |