All Forums -> Forum: Dynamics GP Adapter for Insight -> Topic: Each update in GP (SOP10100) creates ...
Forum Topic: "Each update in GP (SOP10100) creates TWO records in the SCRIBEPUBLISHERQUEUE - why?"

When updating records in GP (e.g., customer in RM00101 or order in SOP10100), each update creates TWO records in the SCRIBEPUBLISHERQUEUE table. Why TWO records, rather than just one? This occurs even when using an update query at the database level (rather than throug the GP interface).

Posted by Michael Cross (michael.cross@tribridge.com) on Oct 14, 2010 11:59PM Return to top

 

I discovered additional triggers on the GP tables that update the DEX_ROW_TS field. For example, on RM00101 is this, zDT_RM00101U. So it appears that an update triggers the SCRIBEPUB trigger, and it also triggers the zDT_RM00101U trigger, which then triggers the SCRIBEPUB trigger again.

 

Are these triggers part of GP 10 SP4?


/****** Object: Trigger [dbo].[zDT_SOP10100U] Script Date: 10/18/2010 19:44:43 ******/

SET

ANSI_NULLS ON

GO

SET

QUOTED_IDENTIFIER OFF
GO

 

CREATE TRIGGER [dbo].[zDT_SOP10100U] ON [dbo].[SOP10100] AFTER UPDATE AS set nocount on BEGIN UPDATE dbo.SOP10100 SET DEX_ROW_TS = GETUTCDATE() FROM dbo.SOP10100, inserted WHERE SOP10100.SOPTYPE = inserted.SOPTYPE AND SOP10100.SOPNUMBE = inserted.SOPNUMBE END set nocount off<font> </font>

 

Posted by Michael Cross (michael.cross@tribridge.com) on Oct 18, 2010 07:45PM Return to top
endorsed

 Yes. 10.4 added a number of triggers, with the result that when you save a record via the UI, often that same record gets saved multiple times in the database, resulting in multiple messages picked up by the publisher. We believe Microsoft added these triggers for their own CRM-GP Connector product, but the result is multiple hits to the database for everyone.

Posted by Bob Sturim (bsturim@scribesoft.com) on Oct 19, 2010 06:58AM Return to top

Thanks for the confirmation, Bob.

The extra messages might be causing a problem in my client's system with the integrations that delete line items (e.g., GP Order to CRM Order and GP Invoice to CRM Invoice), where we get many execution failures with the message "Server unable to process the request.". All the failures are on the Delete operation. The failures happen inconsistently (making it really hard to troubleshoot).

I'm wondering if the failures occur when many transactions are being processed at once (e.g., EDI orders imported into GP, orders transferred to invoices, etc.). I'm thinking that Scribe might be processing multiple messages for the same order or invoice and trying to delete line items multiple times.

Where this seems to be causing a problem is that orders are ending up in CRM with duplicate line items.

Does this make sense at all? Have you encountered this behavior at all in your testing? Any thoughts on how to bullet-proof the process to avoid orders/invoices with duplicate line items?

Posted by Michael Cross (michael.cross@tribridge.com) on Oct 19, 2010 02:36PM Return to top
endorsed

Please take a look at http://blog.scribesoft.com/2010/06/insight-70-makes-multi-threaded-integrations-safer.html.

The feature discussed in that blog article was driven in part due to the change in behavior in GP 10.4. It provides a means to single thread integrations operating on the same source record without sacrificing concurrency in general.

Posted by Bob Sturim (bsturim@scribesoft.com) on Oct 19, 2010 03:40PM Return to top

I remember seeing this post, but had forgotten about it. Since the lookup variable is maintained by the Cross Reference Key settings, and there appears to be no way to indicate the use of the LOCK formula in the Cross Reference Keys settings, I'm assuming I can safely change the variable formula directly to use the XREFLOOKUPLOCK_AKEY() and XREFLOOKUPLOCK_BKEY() formulas?

Posted by Michael Cross (michael.cross@tribridge.com) on Oct 19, 2010 04:16PM Return to top
endorsed

 That should be the case.

Posted by Bob Sturim (bsturim@scribesoft.com) on Oct 19, 2010 04:52PM Return to top

A colleague of mine suggested modifying the Scribe triggers so that they run only when the value of the DEX_ROW_TS field does NOT change (see below for a suggested method). Does this make sense? What impact might this change have?

declare @DEX_ROW_TS_NEW datetime, @DEX_ROW_TS_OLD datetime

select @DEX_ROW_TS_NEW = DEX_ROW_TS from inserted
select @DEX_ROW_TS_OLD = DEX_ROW_TS from deleted

if @DEX_ROW_TS_OLD = @DEX_ROW_TS_NEW
begin
<>
end

Posted by Michael Cross (michael.cross@tribridge.com) on Oct 19, 2010 11:17PM Return to top
endorsed

Would require some testing but I thought the problem was opposite.  That is, I thought GP had triggers that fire when anything happens to the table in order to update the DEX_ROW_TS field and that we were unable to pre-empt that from happening.  In fact, I also believe that our triggers are already ignoring changes to that field, but could be wrong about that.  If they're not, it would be worth a try, I can't see any issue with it, but I did think we went down that route.

Posted by Michael B on Nov 02, 2010 05:07PM Return to top

I am struggling with this issue as well.  I made a change to a SOP Header record and I got 2 messages published to the queue.  I made a change to a SOP Line record and I got 4 messages published to the queue. I understand that by adding a row-level Lock to the record being updated I can prevent data errors from happening, but this duplication of messages causes a lot of extra activity in the system which is unnecessary. 

I have an environment that has GP 10 and Scribe 6.5 and the "extra" trigger on the SOP10100 table exists.  However, when I make a change to a sales order, only one message is published.  In that environment, the GP Publisher is a query publisher not a queue publisher.  That environment does not create duplicate messages even though it has the DEX_ROW_TS trigger in place.  So it doesn't seem like the blame should be pushed entirely onto the Dynamics GP product.  It's also due in part to the change made to the Scribe GP Publisher. 

I have a question in to Microsoft in regards to this trigger...to see if it truly is related to the CRM Adapter for GP and to see if it can be disabeld, but is there no way that Scribe can try to address this issue as well?  The solution suggesed seems very inefficient. 

Posted by Sandy W on Dec 01, 2010 01:38PM Return to top
endorsed

Yes, it is possible for Scribe to attempt to optimize the behavior of the publisher.

Let me provide a bit of context. The potential for duplicate messages to run into race conditions can exist for a number of situations. The change in behavior of the Microsoft GP to update the same record multiple times, cause Scribe triggers to detect multiple updates is one such condition. The locking solution is therefore an important capability that was added to Scribe Insight to make multi-threaded integrations safer, and I would strongly suggest using it in many scenarios, including and extending beyond GP integrations.

That said, we have considered making the Scribe triggers more intelligent to only write rows to the change log when need be. Unfortunately, the GP Service Pack  which drove this new behavior was released late in the development cycle for the 4.0 release of the Dynamics GP Adapter, and we did not feel that we could safely make the required changes at that time without impacting the release date for that release of the adapter for Dynamics GP.

Your suggestion is a reasonable enhancement request. I have created OpenMind idea idea #444: Optimize The Triggers Created By The GP Publisher Not Log Multiple Changes For The Same Update to record this feature request. If you would value this enhancement, I encourage you to vote for this idea.

 

Posted by Bob Sturim (bsturim@scribesoft.com) on Dec 01, 2010 02:26PM Return to top

Thanks for the follow-up and for adding this as an idea. 

I had opened a case with Microsoft to ask about the trigger, to describe the problem is was causing and to ask if disabling it would be an option.  Here is their response:

"The DEX_ROW_TS trigger was introduced in Dynamics GP 10 for Workflow. It is used with Workflow, Web Services, and the CRM Adapter. If you are not using any of these products, you can disable the trigger. If you are using any of these products, Scribe will need to modify their code to take this trigger into consideration when updating Sales Orders."

Posted by Sandy W on Dec 01, 2010 04:10PM Return to top
endorsed

That's good information. Thanks for looping back on this.

Posted by Bob Sturim (bsturim@scribesoft.com) on Dec 01, 2010 04:36PM Return to top

The client I'm working with does not want to use the queue publisher in light of the duplicate messages being created.  Is it possible, and is there any documentation on using the older query publisher rather than the queue publisher with Scribe 7.0.  The client does not use one of the templates; they just need to publish changes from GP to another application.

Posted by Sandy W on Dec 02, 2010 08:25AM Return to top
endorsed

It is possible to use a query publisher to detect changes in CRM online...though doing so would probably require reworking of the DTSes as well as the format of the XML mesages published to the InQueue would likely be different. There is no documentation of which I am aware on how to do this.

Posted by Bob Sturim (bsturim@scribesoft.com) on Dec 02, 2010 10:57AM Return to top
Add Comment Back to Topics