|
|
You're in the
sql server notification services
group:Multi-Valued Subscription Parameters
sql server notification services:
I am setting up a Subscription class with two parameters; let's call them P1 and P2. However, the subscribers would like to have multiple combinations of these two parameters. In other words, if they want 4 possible values for P1 and 3 possible values for P2, then they would need to submit 12 (4 P1 times 3 P2) unique combinations, each one a new subscription. What I am looking for is a way to have just one Subscription record with each Parameter being a string of comma separated values so that P1 would be 'AAA, BBB, CCC, DDD' and P2 would be '11, 22, 33' etc. I have played around with a stored procedure that would parse the two parameters into two temp tables then do a FULL OUTER JOIN to bring them back together with the correct number of rows. This SP works but I am having trouble joining its results back into the SELECT criteria for the subscription. I don't think my way could handle multiple subscriptions being processed in the same batch. Is there an ELEGANT way to parse out these strings in NS? Thanks in advance --
[quoted text, click to view]
On Fri, 13 Apr 2007 05:30:02 -0700, Todd C wrote: > Hello All > > I am setting up a Subscription class with two parameters; let's call them P1 > and P2. However, the subscribers would like to have multiple combinations of > these two parameters. In other words, if they want 4 possible values for P1 > and 3 possible values for P2, then they would need to submit 12 (4 P1 times 3 > P2) unique combinations, each one a new subscription. > > What I am looking for is a way to have just one Subscription record with > each Parameter being a string of comma separated values so that P1 would be > 'AAA, BBB, CCC, DDD' and P2 would be '11, 22, 33' etc. > > I have played around with a stored procedure that would parse the two > parameters into two temp tables then do a FULL OUTER JOIN to bring them back > together with the correct number of rows. This SP works but I am having > trouble joining its results back into the SELECT criteria for the > subscription. > > I don't think my way could handle multiple subscriptions being processed in > the same batch. > > Is there an ELEGANT way to parse out these strings in NS? > > Thanks in advance See if either of these posts help: http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/87871744decee742/a76995956e9bcf76?lnk=gst&q=optional+subscription&rnum=1#a76995956e9bcf76 http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/1937323c4b8361fd/92bb28d6a17e2139?lnk=gst&q=optional+subscription&rnum=2#92bb28d6a17e2139 HTH... -- Joe Webb SQL Server MVP http://www.sqlns.com
Joe: Neither of those links is close to my issue.
Sorry. See my original post. I can't explain it any better. -- Todd Chittenden [quoted text, click to view] > > Hello All
> > > > I am setting up a Subscription class with two parameters; let's call them P1 > > and P2. However, the subscribers would like to have multiple combinations of > > these two parameters. In other words, if they want 4 possible values for P1 > > and 3 possible values for P2, then they would need to submit 12 (4 P1 times 3 > > P2) unique combinations, each one a new subscription. > > > > What I am looking for is a way to have just one Subscription record with > > each Parameter being a string of comma separated values so that P1 would be > > 'AAA, BBB, CCC, DDD' and P2 would be '11, 22, 33' etc. > > > > I have played around with a stored procedure that would parse the two > > parameters into two temp tables then do a FULL OUTER JOIN to bring them back > > together with the correct number of rows. This SP works but I am having > > trouble joining its results back into the SELECT criteria for the > > subscription. > > > > I don't think my way could handle multiple subscriptions being processed in > > the same batch. > > > > Is there an ELEGANT way to parse out these strings in NS? > > > > Thanks in advance > > > See if either of these posts help: > > http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/87871744decee742/a76995956e9bcf76?lnk=gst&q=optional+subscription&rnum=1#a76995956e9bcf76 > > http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/1937323c4b8361fd/92bb28d6a17e2139?lnk=gst&q=optional+subscription&rnum=2#92bb28d6a17e2139 > > HTH... > > -- > Joe Webb > SQL Server MVP > http://www.sqlns.com > > > >
Joe:
The "Subscription Management Application", at this point, is me and a couple of stored procedures I run from SSMS. The Subscribers have no direct interface YET. The goal is to make it easier for SOMEBODY (that sombody being me now and the users later on) if they want to edit their parameters. Managing one record of Subscription info per user seems easier than managing 15 or 20. I guess what I am really looking for is to have the Subscription matching logic dynamically parse out the Parameter values at "Match Time". This is my very "UN-ELEGANT" theory for a possible solution: Have a cursor step through each Subscription row and parse the two parameters into two respective temp tables, then do a FULL OUTER JOIN of those two temp tables, and insert the rows into a third table along with the SubscriberID, DeviceName, and Locale. The cursor would need to clear out the temp tables before it moved on to the next Subscription row. In the end, the 'match logic' would look at the final (third) table which would have a fully normalized set of subscription data in it. After all the subscription matching is done, the third table would need to be cleaned out to get ready for the next quantum run. Very brutish but I think it would work. Looking for any suggestions on a better way to do this. -- Todd Chittenden [quoted text, click to view] "Joe Webb" wrote:
> On Mon, 16 Apr 2007 08:04:06 -0700, Todd C wrote: > > > Joe: Neither of those links is close to my issue. > > Sorry. > > > > See my original post. I can't explain it any better. > > > The links I sent were for consideration if you wanted to broaden your > subscription from 2 to 12, with some of them being optional. After > re-reading your OP, perhaps I misunderstood what you're trying to > accomplish. > > Is the intent to make it easier for the subscriber? If so, what about > having your subscription management application pass in the delimited > string, parsing it, and then creating the 12 subscriptions. > > If that's not the reasoning, can you elaborate on it? > > -- > Joe Webb > SQL Server MVP > http://www.sqlns.com >
[quoted text, click to view]
On Mon, 16 Apr 2007 08:04:06 -0700, Todd C wrote: > Joe: Neither of those links is close to my issue. > Sorry. > > See my original post. I can't explain it any better. The links I sent were for consideration if you wanted to broaden your subscription from 2 to 12, with some of them being optional. After re-reading your OP, perhaps I misunderstood what you're trying to accomplish. Is the intent to make it easier for the subscriber? If so, what about having your subscription management application pass in the delimited string, parsing it, and then creating the 12 subscriptions. If that's not the reasoning, can you elaborate on it? -- Joe Webb SQL Server MVP http://www.sqlns.com
[quoted text, click to view]
On Tue, 17 Apr 2007 05:10:02 -0700, Todd C wrote: > Joe: > > The "Subscription Management Application", at this point, is me and a couple > of stored procedures I run from SSMS. The Subscribers have no direct > interface YET. > > The goal is to make it easier for SOMEBODY (that sombody being me now and > the users later on) if they want to edit their parameters. Managing one > record of Subscription info per user seems easier than managing 15 or 20. > > I guess what I am really looking for is to have the Subscription matching > logic dynamically parse out the Parameter values at "Match Time". > > This is my very "UN-ELEGANT" theory for a possible solution: > Have a cursor step through each Subscription row and parse the two > parameters into two respective temp tables, then do a FULL OUTER JOIN of > those two temp tables, and insert the rows into a third table along with the > SubscriberID, DeviceName, and Locale. The cursor would need to clear out the > temp tables before it moved on to the next Subscription row. > > In the end, the 'match logic' would look at the final (third) table which > would have a fully normalized set of subscription data in it. After all the > subscription matching is done, the third table would need to be cleaned out > to get ready for the next quantum run. > > Very brutish but I think it would work. Looking for any suggestions on a > better way to do this. I would definitely try to dissuade you from that implementation. As clever and creative as it is, performance would likely be very, very bad. Cursors, as I'm sure you already know, are resource intensive and should be used only as a last resort. Using them in the match rule could cause some unintended results. If you are managing subscription with scripts (currently), and later with a subscription management application, I would suggest that you keep the 12-20 subscriptions per user so that SSNS can do its thing well. You can write scripts (and later on the SMA) to allow you to pass in the delimited string, XML doc, whatever, to the scripts and have them parse out the subscriptions. Just my two cents... HTH... Joe -- Joe Webb SQL Server MVP http://www.sqlns.com
Like I said, my proposed solution would be very brutish and unelegant.
I think I'll look for another way to handle this one ... Thanks -- Todd C [quoted text, click to view] "Joe Webb" wrote:
> On Tue, 17 Apr 2007 05:10:02 -0700, Todd C wrote: > > > Joe: > > > > The "Subscription Management Application", at this point, is me and a couple > > of stored procedures I run from SSMS. The Subscribers have no direct > > interface YET. > > > > The goal is to make it easier for SOMEBODY (that sombody being me now and > > the users later on) if they want to edit their parameters. Managing one > > record of Subscription info per user seems easier than managing 15 or 20. > > > > I guess what I am really looking for is to have the Subscription matching > > logic dynamically parse out the Parameter values at "Match Time". > > > > This is my very "UN-ELEGANT" theory for a possible solution: > > Have a cursor step through each Subscription row and parse the two > > parameters into two respective temp tables, then do a FULL OUTER JOIN of > > those two temp tables, and insert the rows into a third table along with the > > SubscriberID, DeviceName, and Locale. The cursor would need to clear out the > > temp tables before it moved on to the next Subscription row. > > > > In the end, the 'match logic' would look at the final (third) table which > > would have a fully normalized set of subscription data in it. After all the > > subscription matching is done, the third table would need to be cleaned out > > to get ready for the next quantum run. > > > > Very brutish but I think it would work. Looking for any suggestions on a > > better way to do this. > > > I would definitely try to dissuade you from that implementation. As clever > and creative as it is, performance would likely be very, very bad. > Cursors, as I'm sure you already know, are resource intensive and should > be used only as a last resort. Using them in the match rule could cause > some unintended results. > > If you are managing subscription with scripts (currently), and later with > a subscription management application, I would suggest that you keep the > 12-20 subscriptions per user so that SSNS can do its thing well. You can > write scripts (and later on the SMA) to allow you to pass in the delimited > string, XML doc, whatever, to the scripts and have them parse out the > subscriptions. > > Just my two cents... > > HTH... > > Joe > > > -- > Joe Webb > SQL Server MVP > http://www.sqlns.com > > > >
[quoted text, click to view]
On Wed, 18 Apr 2007 05:42:01 -0700, Todd C wrote: > Like I said, my proposed solution would be very brutish and unelegant. > > I think I'll look for another way to handle this one ... > > Thanks Well, I wouldn't be that harsh; it is very creative. Feel free to post more proposed solutions. -- Joe Webb SQL Server MVP http://www.sqlns.com
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 |