ArcherPoint Dynamics NAV Developer Digest - vol 288
The Dynamics NAV and Business Central community, including the ArcherPoint technical staff, is made up of developers, project managers, and consultants who are constantly communicating, with the common goal of sharing helpful information with one another to help customers be more successful.
As they run into issues and questions, find the answers, and make new discoveries, they post them on blogs, forums, social media…so everyone can benefit. We in Marketing watch these interactions and never cease to be amazed by the creativity, dedication, and brainpower we’re so fortunate to have in this community—so we thought, wouldn’t it be great to share this great information with everyone who might not have the time to check out the multitude of resources out there? So, the ArcherPoint Microsoft Dynamics NAV Developer Digest was born. Each week, we present a collection of thoughts and findings from NAV/BC experts and devotees around the world. We hope these insights will benefit you, too.
Millions of Records in Dynamics NAV 2018 Table 5151, Related to CRM
Faithie presents an issue to the tribe: “Has anyone encountered a situation where Table 5151 Integration Record grew to hold millions of records? All I find when I look it up is that it’s related to CRM, but there are records for Modified, Updated, and Deleted records from all sorts of tables. It’s growing so large the database isn’t operating properly. And Table 5152 (archive version) is growing as well. The client is on NAV 2018 with CU 17.”
Kyle responds: “Yes, I’m having the same problem in BC15 on premises. No CRM is turned on. Two million records were created in three months!”
Faithie asks: “Is there a solution for this? Are we safe to remove records from the tables? I’ve searched and I find nothing. I did find this article on the Integration Record Table, which I’ve compared to the client data and come up with no reason this is happening. Here’s the reply from Microsoft support, which is at least reassuring that there is a problem: ‘In NAV 2017, these records didn’t get created until the CRM integration was enabled.’”
“My understanding is that it was such a rough process that it may have enabled it by default. I can see in Cronus databases that all NAV 2018s have integration records regardless of whether or not I ever set up the CRM integration, and the NAV2017 databases are mostly empty. There was a bug on this that might not be helping matters any. ‘Application build 24232 (NAV2018CU9) Configuration Package creates unwanted integration record rows.’”
“I think it is code unit 5150 and the IsIntegrationRecord function (the DATABASE::xxxx determine which tables we monitor for the integration) or even prior to that the InitializeIntegrationRecords function. The OnDatabaseDelete moves them to the archived table 5152. This data is only used for the CRM integration, so it’s really not necessary. You could have a developer review that code unit and make a few modifications, then TEST the process in a TEST database. I hesitate to provide exact steps as this definitely isn’t something my product developers would really support.”
Bill W offers: “I’d try to see how or why it’s making it through this function. These only get created if you have CRM Integration, Graph API, or some other EDM Odata sync turned on. I’m not even sure those work in 2018 (graph and EDM). I think knowing why it’s believing it’s enabled will lead back to the functionality not being used. My guess is that you could delete all of the records.”
CU 5150
LOCAL PROCEDURE GetIntegrationActivated@20() : Boolean;
VAR
GraphSyncRunner@1000 : Codeunit 5452;
IsSyncEnabled@1001 : Boolean;
IsSyncDisabled@1002 : Boolean;
BEGIN
OnGetIntegrationDisabled(IsSyncDisabled);
IF IsSyncDisabled THEN
EXIT(FALSE);
IF NOT IntegrationIsActivated THEN BEGIN
OnGetIntegrationActivated(IsSyncEnabled);
IF IsSyncEnabled THEN
IntegrationIsActivated := TRUE
ELSE
IntegrationIsActivated := IsCRMConnectionEnabled OR GraphSyncRunner.IsGraphSyncEnabled;
END;
EXIT(IntegrationIsActivated);
END;
Kyle responds: “I tried this and was not successful. But I think I am on the right path.
Codeunit 50143 ‘ARC Codeunit 5150 Subscribers:’”
// Shut off Integration Record creation regardless of where it comes from
// Note that this breaks CRM integration
{
[EventSubscriber(ObjectType::Codeunit, Codeunit::”Integration Management”, ‘OnGetIntegrationDisabled’, ”, false, false)]
local procedure OnGetIntegrationDisabled(var IsSyncDisabled: Boolean)
begin
IsSyncDisabled := false;
end;
}
Kyle then adds: “This looks like it works:”
codeunit 50143 “ARC Codeunit 5150 Subscribers”
// Shut off Integration Record creation regardless of where it comes from
// Note that this breaks CRM integration
{
[EventSubscriber(ObjectType::Codeunit, Codeunit::”Integration Management”, ‘OnGetIntegrationDisabled’, ”, false, false)]
local procedure OnGetIntegrationDisabled(var IsSyncDisabled: Boolean)
begin
IsSyncDisabled := true;
end;
}
Kyle continues: “I put my fix into production this morning and deleted all records from 5151 and 5152. In 15.4, they have marked 5151 and 5152 as obsolete. Apparently, they know there is a problem and are re-working synchronization.”
Bill W responds: “The CRM integration relied on a linked table that I think made web service calls back to CRM to get the data. I think they’re altering the underlying technology…which requires a total re-write of the functionality into some more complicated code base. Also…what’s the point of extensions if they’re just going to be obsoleting all existing functionality to something else?”
Faithie provides an update: “Support sent me a link to vote on having this controlled by an option. You can all go to this link to vote.“
Saurav continues the conversation: “Integration Records are also created for API. If you use API, records are updated in this table about Table Record GUID.
Faithie asks: “Is that true in NAV 2018? And what APIs? Support didn’t mention that, and all I can see in the code is use from CRM. It is called in COD 1.”
Michael Heydasch: “Faithie, I had a similar issue with an add-on and the Session Event table, which grew to hundreds of millions of records. Simply deleting records wasn’t an option, because records would be added noting the deletion. I ended up using a SQL table trigger and a Job Queue routine to periodically delete records from the table and bypass the NAV delete table trigger.”
Faithie responds: “Sounds much like what we were seeing. I did what MS ‘sort of’ said to do and bypassed the entry of records in the codeunit. No more records—not a problem.
Saurav adds: “APIs did exist in NAV 2018; that was when Microsoft introduced API. NAV 2018 will offer a total of 44 APIs out of the box in the areas of company, finance, sales, purchasing, and reports. New APIs can be created as well. See this blog post on What’s New in Dynamics NAV 2018.“
Figure 1 – API in Microsoft Dynamics NAV 2018
Who else has come across this issue? Share your experience in the comments section below.
If you are interested in Dynamics NAV and Business Central development, be sure to see our collection of NAV/BC Development Blogs.
Read the “How To” blogs from ArcherPoint for practical advice on using Microsoft Dynamics NAV and Dynamics 365 Business Central.