Bug in NS 2005?

We have rolled out a solution on SQL 2005 and NS for 2005. As we have been looking into reporting notifications from our system I came across that a column that is created out of the box does not make sense.

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.

Answer this question

Bug in NS 2005?

xesteban
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.
PedroMarques
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.
Scott Anderson

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.
wackoyacky
What about SSIS'ing the notifications periodically - before vacuuming

HTH...

--
Joe Webb
SQL Server MVP
http://www.sqlns.com
codekaizen
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
veeruu
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.
pablog

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.
DearDearDear

Yes, I think we did meet - if I recall Shyam covered debugging/diagnosing NS applications on the whiteboard.< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

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.
Niro
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.
Netwrkr
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.
Bug in NS 2005?

Answer this question