error while configuring adapter notification for MS SQL serv

while configuring the adapter notification for the table in the MS SQL server I am getting the following error…

com.wm.app.b2b.server.ServiceException: [ART.114.243] Adapter Runtime (Metadata): Failed to run notificationLookupValues service.

Hi,

this is a known issue. MS SQL does not allow conditions on the Database trigger created by the notification.

There is no possibility to configure MS SQL to allow it and it is not a problem with wM Adapter.

If you need a condition you have to implement it as a branch in the flow service triggered by the notification.

Regards,
Holger

Hi,

I’m not sure if this is limitation on the MS SQL. I was able to edit the trigger on the Database and it is working fine.

  1. Create your trigger without any conditions from developer.
  2. Modify the trigger you created on the MS SQL DB.
  3. Here is the syntax for condition…
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

ALTER TRIGGER <[trigger name]=""> ON [dbo].<[table name]=""> for update as
if update <(field name)="">
begin INSERT INTO dbo. <(fields you="" need="" to="" extract="" to="" buffer="" table)="">
SELECT inserted., insertedinserted.,
FROM inserted, deleted WHERE deleted. =
end

Regards,
Chapa</(fields></(field></[table></[trigger>

We ran into this problem and were able to resolve it by rediting the Trigger just as Chapa described.

We created a trigger specifically just for when new items hit a particular table.

The trigger did not work properly until we did 2 things

  1. removed the deleted from the “FROM inserted,deleted” portion of the code. This is attemping an inner join and since we did not delete anything there was not matching portion to complete the join, thus no data returned.
  2. The buffer table was coming up with the format .. Since we did not have a schema called it was failing. Once we created a schema called we were up and running.