Uncategorized

Help, RecID is jumping

At a customer site, we sometimes saw that the recid is “jumping” in Dynamics AX 2009. We cannot explain why, but it seems to happen a few times every year. To deal with this, we created the following SQL trigger :

Thanks to FSB-development that came up with this. I share it to the community, because it would be easy to modify for other purposes.

I appreciate that I can be a part of this worldwide blog community—as a consultant working from Norway, the blog lets me share more than 20 years of experience with Microsoft Dynamics 365. Along the way, I participated in developing retail, PDA/RF, barcoding, master data, kitting and WMS-solutions for Dynamics. My blog focuses on my deepest interests and expertise: along with a 360 degree view of digital transformation topics, I welcome opportunities to dive into retail and intercompany supply chain automation, logistics, and production—everything that is moving around in a truly connected enterprise. As Enterprise Architect on Dynamics 365, I specialize in strategic development and planning for corporate vertical solutions and works to build international networks that increase knowledge and understanding for Dynamics 365. As an advocate for both providers and customers, I'm committed to ensure that customers constantly changing needs are meet, and I see community as key for increasing expertise. I welcome you to connect with me.

3 comments on “Help, RecID is jumping

  1. Mykola Galak

    Hi Kurt,
    Having trigger on SystemSequences table won’t solve the issue. Moreover it is probably too late to turn trace on. To find source of these “jumps” I’d advise searching AOT for calls to reservation of RecId range like SystemSequence.suspendRecIds(Table.TableId) or direct update of SystemSequences table.

    Mykola Galak

    Like

    • Hy Mykola. Yes, the triger will not solve the issue, but I liked the idea to add triggers to spesific changes directly on the database, and I therefore wanted to share the ability 🙂
      //kurt

      Like

  2. Here as a script for copy-paste :
    USE [AXDBMS2009]
    GO
    /****** Object: Trigger [dbo].[RecIdJumpMonitor] Script Date: 10/21/2011 19:57:59 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER [dbo].[RecIdJumpMonitor] ON [dbo].[SYSTEMSEQUENCES] after UPDATE AS

    DECLARE @oldVal bigint
    DECLARE @newVal bigint

    SET @oldVal = (SELECT nextval FROM deleted)
    SET @newVal = (SELECT nextval FROM inserted)

    IF @newVal-@oldVal >= 100000
    BEGIN
    DECLARE @msg varchar(500)
    SET @msg = ‘RecId jumped with more than 100.000, please start AOS Tracing now.’
    –CHANGE THE VALUE FOR @recipients and @profile_name
    EXEC msdb.dbo.sp_send_dbmail @recipients=N’xxx@xxxx.xx’, @body= @msg, @subject = ‘SQL Server Trigger Mail’, @profile_name = ‘zulu’
    EXEC msdb.dbo.sp_send_dbmail @recipients=N’yyy@yyyyyy.yy’, @body= @msg, @subject = ‘SQL Server Trigger Mail At Customer XX, @profile_name = ‘zulu’
    END

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: