Update notifications with SQL Server

, ,
1

I have an issue with update notification with JDBC Adapter and Microsoft SQL Server database. If I update more than 1 row in a table with one SQL statement only the 1st row updated is notified to webMethods.
I looked into the buffer table from webMethods (WMBxxxx table) and I can see that there are many rows (as many as my SQL statement updated) but all have the same WM_ROWID value. This is causing the problem.

Did anyone see this before?

Is this a bug in the way webMethods implemented the trigger or is this a feature of Microsoft SQL server.

The solution I have so far is to avoid updating multiple rows in 1 statement. If I update row by row all is ok.

I would appreciate if you have any suggestions about the real cause of this issue.

TIA.

2

Can you publish an update trigger created by webMethods?

3

Here it is:

CREATE TRIGGER WMTbbec1haupd ON GOODS_RECEIPT for update as begin INSERT INTO WMSbbec1ha default values; INSERT INTO WMBbbec1haupd (g_ordernumber, g_create_date, wm_rowid) SELECT inserted.g_ordernumber, inserted.g_create_date, @@IDENTITY FROM inserted end

I simplified it a little to make it more readable.
The table WMBbbec1haupd is the buffer. The WMSbbec1ha is a table containing just one column called id of type numeric(14) identity.
I guess this is the way to generate unique ids on SQL server as there is nothing like sequences in Oracle.

4

I think it is necessary to add one more thing.

This is not a simple Update Notification. This is rather OrderedNotification, where we defined notification on INSERT and also on UPDATE. In fact there is also similar trigger created for INSERT.
I think you don’t get this funny table with id column if you just use regular Update Notification. This get only created with Ordered Notification.

So maybe this issue only exists with OrderedNotification.

5

Using @@identity in the trigger insert statement produces duplicate wm_rowid for the buffer table.
The @@identity function returns the last-inserted identity value.
This is clearly not applicable in case of update (no identity even is generated during update). Moreover, even if it is, the last generated value will be used for all records in the update.
The Ordered Notification is supposed to take care of this by creating special sequence table which is specified in the Notification Configure tab in the Developer.

6

Greg,
Thanks for the input.
I did more test. This time with regular UpdateNotificationa and in this case this works ok. The WM_ROWID in the buffer table, which is identity type, contains different values for each row.

So this problem is only with OrderedNotification. After I run an SQL to update many rows in my table the sequence table, in my case WMSbbec1ha, contains only one row, while the buffer table WMBbbec1haupd contains many rows, all have the same WM_ROWID.

Do you have an idea why this problem happens?

Was there any fix for this? Or will I always have to use my workaround of updating row by row, when using OrderedNotification?

Thanks.