[David Morinello]

Does anyone know any details on these two Stored Procedures?

• ?DYNAMICS.dbo.TA_ParentInsertRM
• DYNAMICS.dbo.TA_ParentInsertAddressRM

Blocked Sessions
In the last week or so we are suddenly seeing these SPs becoming SQL level blockers for the whole 38 company GP 2013 R2 installation. We have many external data feeds including Scribe connections to CRM (multiple companies) and GP Web Services. These calls are in the Scribe jobs and pausing those jobs for 30 minutes or so resolves the blocks.

The CRM & GP Web Services feeds have been there for years. Data volume has increased, but I am trying to understand why now has this started being an problem for us.

The more I understand, not only on what these are, but also on why they are located at the Dynamics DB level, the better chance I have of unwinding our problem and coming to a permanent resolution.

1. Why are they in Dynamics instead of each company level database?
2. What do they do that is different from <taCreateParentID>, <taParentIDChild>, and <taCreateCustomerAddress>?
3. Why are they not listed in the documentation? (hahaha)
4. Is there an alternate method to do the same functionality?
5. History, speculation, gossip?

Thanks!

——————————
David Morinello
Senior Dynamics GP Systems Architect
Ascend Learning, LLC
Leawood KS
——————————

[David Morinello]

?I found the answers to my question and thought I would pass them along.
These Company Level  SQL Triggers are created by PSTL and are probably related to the combiner features. The PSTL SDK seems to hint at this.

• ‘taAccountInsert’
• ‘taCustomerAddressInsert’
• ‘taCustomerInsert’
• ‘taVendorAddressInsert’
• ‘taVendorInsert’

The two Customer triggers call

• ?DYNAMICS.dbo.TA_ParentInsertRM
• DYNAMICS.dbo.TA_ParentInsertAddressRM

There are similar Dynamics level SPs for the other 3 triggers as well.

Here is a potential gotcha for eConnect users with multiple companies. Once Customer Combiner is used, these triggers remain enabled. Use Combiner in multiple companies, these triggers are enabled in multiple companies.

If you have two or more eConnect streams updating Customer Master or Customer Address Master tables in different companies, you now have the potential to bottleneck on this one set of Dynamics level Stored Procedures. We saw this here as SQL Level Blocked Sessions. Disabling the 5 triggers listed above in all companies resolved many such blocked sessions. Of source the PSTL tools features could turn them back on, so you need to monitor it.

I would submit a fix request to Microsoft Collaborate (Microsoft Connect Has Been Retired) if there were a GP option. Anyone have any ideas there?

Suggestion
PSTL tools should enable it’s triggers when used, and disable them after the change is complete. I do not see a need to leave triggers like taCustomerInsert (RM00101) on all the time, to cause performance issues for GP Users and eConnect data streams.

This might be a job for  and GP Power Tools!
??

——————————
David Morinello
Senior Dynamics GP Systems Architect
Ascend Learning, LLC
Leawood KS
——————————
——————————————-

[Beat Bucher]

Hi ,

This is an interesting finding, and let me chime in on that comment:

SuggestionPSTL tools should enable it’s triggers when used, and disable them after the change is complete. I do not see a need to leave triggers like taCustomerInsert (RM00101) on all the time, to cause performance issues for GP Users and eConnect data streams.

David Morinello,  02-12-2018 10:51 AM

I had presented many times sessions about PSTL and yes this is a nice feature to replicate Account, Customer or Vendor data between companies in GP.. and yes, potentially this could become a disruptive situation when such a trigger is enabled on the RM00101 & PM00200 tables (and derivating ones) to replicate data..

I’m not sure how the triggers would be re-enabled in case you would disabled them thru SSMS or with some code, since the status of those data replication triggers are stored in the table DYNAMICS..TA_Map, where there would be an entry for each company pair to replicate and the different functions flagged with a 0 or 1.
Rather than disabling the triggers completly, I’d just alter the flag in the TA_Map table.. easier to handle IMHO.

I discovered with a similar issue with the ACTIVITY table where there was a trigger causing issues with inactivity timeouts..

In the end, it’s the GP system admin that should know and document all the ‘extra’ tidbits that are enabled in his/her system and that were not there by default. It’s part of a good DR plan anyway, and it becomes even more important when you start using integration tools, be that IM or any other 3rd-party products (or eConnect by that matter).

??

——————————
Beat Bucher
Business Analyst, Dynamics GP MVP
Ultra-Electronics Forensic Technology Inc.
Montreal QC/Canada
@GP_Beat http://dyngpbeat.wordpress.com/
Montreal QC GPUG Chapter Leader
GP2013R2 / MR2012 CU14
——————————
——————————————-