In the generated table NS<NotificationClassName>Notifications there is a column called SubscriptionID, it is set as nvarchar(255), but in the NS<SubscriptionClassName>Subscriptions the SubscriptionID is BIGINT. Why would then be different when the refer to the same thing?
What we have done for our solution is not read from the NS table directly, since we do have vacuuming enabled, but we trigger all notifications to another table so we can build reports off the data.
It would be more efficient in our queries to have the data as BIGINT, but I guess it was not an intention to use the tables for reporting inside of NS. We can change our table we are dumping into, but we just did not catch this prior to going live.
I believe this is a bug and thought I would throw it out there.
Hi Andy,
I know this doesn't answer your question, but I would suggest that using triggers or any mechanism that depends on the proprietary implementation schema of Notification Services is unwise and dangerous.
Instead, perhaps you could write a custom delivery protocol to do your reporting. This approach would eliminate any dependence on Microsoft's implentation detail that is subject to change without notice.
Marcus
|||I agree that is a solution, but we plan to test everything before upgrading in the future. We are doing it there so we can collect information about the default protocols, SMTP. Our custom protocol would be easy to populate the table, but then we would have to also make a change for SMTP to use our own process instead of what comes out of the box.Our app was stood up very quickly, no excuse, but we did not have any past experience with the product to know what issues we might have.|||
I believe you can use the Microsoft SMTP delivery protocol alongside a custom protocol. Admittedly, this would be more of a hassle, but it might free you from your MS schema dependency.
|||In NS 2005, we provide a set of views you should use rather than using tables directly.
For the inventory Tracker sample, the views are:
NS_InventoryTrackerApplication.InventoryTrackerEvents
NS_InventoryTrackerApplication.InventoryTrackerSubscriptions
NS_InventoryTrackerApplication.InventoryTrackerNotifications
These have the correct data types.
Hope that helps,
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
The problem is, we have developed a history of notifications database, so we can go back later to see what kind of volume we have sent and who we sent it to.
Since we assume the vacuuming process will delete out old notifications, we need to move those off to somewhere. Our solution was to add a trigger on the table itself, we felt more comfortable with that than altering the view you created, since we were not sure how an UPDATE to the ADF would affect your trigger. We can test that too I guess, but our table that we buit schema from was based on the notification table, it still seems like a bug that your are changing datatypes between them, or was that by design?
Did we meet when I was out in Redmond in October? Your last name seems familiar.
|||What about SSIS'ing the notifications periodically - before vacuuming?HTH...
--
Joe Webb
SQL Server MVP
http://www.sqlns.com|||
Yes, I think we did meet - if I recall Shyam covered debugging/diagnosing NS applications on the whiteboard.
What do we mean by ‘bug’? Typically, it’s something that impairs intended functionality. Right now, since we don't expect users to work directly against the tables (they are not documented), and since no actual functionality seems to be impaired by this, it seems like an implementation decision. Whether it was the right decision is unknown - there might have been a compelling reason for making this choice. On the face of it, it seems wrong to have done the conversion, but I don’t know the history of this implementation. Now whether we'd change the data type in future – two views on that: 1) if it ain't broken... 2) it would probably be cleaned up if we were to do some work on code that touches this area.
Hope that helps,
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
Hi Andy,
Double-check the notification schema specification in your application definition file. Notification Services does not automatically generate a "SubscriptionID" field in the notifications table, so it sounds like a custom field has been defined in your application.
For a list of fields that are generated by NS (some optionally) in the notifications table, see:
http://msdn2.microsoft.com/en-us/library/ms146568.aspx
-Colin
This posting is provided "AS IS" with no warranties, and confers no rights.
|||
My appologies. I did have it defined in my <NotificationClassName> schema fields. It was a slip on our part. We want to track where received notifications and missed the data type error when we created it.
Thank you for the reminder of what to look at. This was not a NS problem, but our own.
|||Our solution is using MS Speech server as well and we had to build an engine to handle retries to speech if the phone was busy for instance. This engine is logging all call attempts back to the original notification, which we are not relying on NS table, but rather our copy of the data via the trigger.
Just thought I would give you a little more background.
Thanks for the post though.
No comments:
Post a Comment