IV00101 Dex_Row_ID reset

  • IV00101 Dex_Row_ID reset

    Posted by Jeff Roe on February 1, 2017 at 3:41 pm
    • Jeff Roe

      Member

      February 1, 2017 at 3:41 PM

      I was looking at the data in my IV00101 Item Master and noticed that all the dex_row_ID’s were reset from 1 to …. and the dex_row_ts was set to todays date at 11:00am.

      I have never noticed that before.

      I assume there is some GP process doing that, does anyone know?

      ——————————
      Jeff Roe
      Kele Inc
      Bartlett TN
      ——————————

    • Dustin Jones

      Member

      February 1, 2017 at 11:51 PM


      Hi Jeff,
      DEX_ROW_TS is updated by a database trigger attached to the table.

      DEX_ROW_ID should only ever change if the record either has been deleted and re-inserted or if someone has chosen to manually update the identity values which really is a no no unless you are resequencing them.

      I would suggest looking through your SQL backups to see when an update was done against the table. You could then inspect transaction logs with something like ApexSQL to see who made the change and when assuming you are in FULL database recovery mode.

      To re-seed the identity value use the following if required (If the values are not unique would be the most obvious reason):

      IF OBJECT_ID( ‘tempdb.dbo.#tmp’ ) IS NOT NULL
      DROP TABLE #tmp;

      BEGIN TRANSACTION;

      BEGIN TRY
      DISABLE TRIGGER ALL ON dbo.IV00101;

      SELECT ITEMNMBR
      , ITEMDESC
      , NOTEINDX
      , ITMSHNAM
      , ITEMTYPE
      , ITMGEDSC
      , STNDCOST
      , CURRCOST
      , ITEMSHWT
      , DECPLQTY
      , DECPLCUR
      , ITMTSHID
      , TAXOPTNS
      , IVIVINDX
      , IVIVOFIX
      , IVCOGSIX
      , IVSLSIDX
      , IVSLDSIX
      , IVSLRNIX
      , IVINUSIX
      , IVINSVIX
      , IVDMGIDX
      , IVVARIDX
      , DPSHPIDX
      , PURPVIDX
      , UPPVIDX
      , IVRETIDX
      , ASMVRIDX
      , ITMCLSCD
      , ITMTRKOP
      , LOTTYPE
      , KPERHIST
      , KPTRXHST
      , KPCALHST
      , KPDSTHST
      , ALWBKORD
      , VCTNMTHD
      , UOMSCHDL
      , ALTITEM1
      , ALTITEM2
      , USCATVLS_1
      , USCATVLS_2
      , USCATVLS_3
      , USCATVLS_4
      , USCATVLS_5
      , USCATVLS_6
      , MSTRCDTY
      , MODIFDT
      , CREATDDT
      , WRNTYDYS
      , PRCLEVEL
      , LOCNCODE
      , PINFLIDX
      , PURMCIDX
      , IVINFIDX
      , INVMCIDX
      , CGSINFLX
      , CGSMCIDX
      , ITEMCODE
      , TCC
      , PriceGroup
      , PRICMTHD
      , PRCHSUOM
      , SELNGUOM
      , KTACCTSR
      , LASTGENSN
      , ABCCODE
      , Revalue_Inventory
      , Tolerance_Percentage
      , Purchase_Item_Tax_Schedu
      , Purchase_Tax_Options
      , ITMPLNNNGTYP
      , STTSTCLVLPRCNTG
      , CNTRYORGN
      , INACTIVE
      , MINSHELF1
      , MINSHELF2
      , INCLUDEINDP
      , LOTEXPWARN
      , LOTEXPWARNDAYS
      , LASTGENLOT
      , Lot_Split_Quantity
      , UseQtyOverageTolerance
      , UseQtyShortageTolerance
      , QtyOverTolerancePercent
      , QtyShortTolerancePercent
      , IVSCRVIX
      , DEX_ROW_TS
      INTO #tmp
      FROM IV00101;

      DELETE FROM IV00101;

      DBCC CHECKIDENT(IV00101, RESEED, 1);

      INSERT INTO IV00101 (ITEMNMBR
      , ITEMDESC
      , NOTEINDX
      , ITMSHNAM
      , ITEMTYPE
      , ITMGEDSC
      , STNDCOST
      , CURRCOST
      , ITEMSHWT
      , DECPLQTY
      , DECPLCUR
      , ITMTSHID
      , TAXOPTNS
      , IVIVINDX
      , IVIVOFIX
      , IVCOGSIX
      , IVSLSIDX
      , IVSLDSIX
      , IVSLRNIX
      , IVINUSIX
      , IVINSVIX
      , IVDMGIDX
      , IVVARIDX
      , DPSHPIDX
      , PURPVIDX
      , UPPVIDX
      , IVRETIDX
      , ASMVRIDX
      , ITMCLSCD
      , ITMTRKOP
      , LOTTYPE
      , KPERHIST
      , KPTRXHST
      , KPCALHST
      , KPDSTHST
      , ALWBKORD
      , VCTNMTHD
      , UOMSCHDL
      , ALTITEM1
      , ALTITEM2
      , USCATVLS_1
      , USCATVLS_2
      , USCATVLS_3
      , USCATVLS_4
      , USCATVLS_5
      , USCATVLS_6
      , MSTRCDTY
      , MODIFDT
      , CREATDDT
      , WRNTYDYS
      , PRCLEVEL
      , LOCNCODE
      , PINFLIDX
      , PURMCIDX
      , IVINFIDX
      , INVMCIDX
      , CGSINFLX
      , CGSMCIDX
      , ITEMCODE
      , TCC
      , PriceGroup
      , PRICMTHD
      , PRCHSUOM
      , SELNGUOM
      , KTACCTSR
      , LASTGENSN
      , ABCCODE
      , Revalue_Inventory
      , Tolerance_Percentage
      , Purchase_Item_Tax_Schedu
      , Purchase_Tax_Options
      , ITMPLNNNGTYP
      , STTSTCLVLPRCNTG
      , CNTRYORGN
      , INACTIVE
      , MINSHELF1
      , MINSHELF2
      , INCLUDEINDP
      , LOTEXPWARN
      , LOTEXPWARNDAYS
      , LASTGENLOT
      , Lot_Split_Quantity
      , UseQtyOverageTolerance
      , UseQtyShortageTolerance
      , QtyOverTolerancePercent
      , QtyShortTolerancePercent
      , IVSCRVIX
      , DEX_ROW_TS)
      SELECT ITEMNMBR
      , ITEMDESC
      , NOTEINDX
      , ITMSHNAM
      , ITEMTYPE
      , ITMGEDSC
      , STNDCOST
      , CURRCOST
      , ITEMSHWT
      , DECPLQTY
      , DECPLCUR
      , ITMTSHID
      , TAXOPTNS
      , IVIVINDX
      , IVIVOFIX
      , IVCOGSIX
      , IVSLSIDX
      , IVSLDSIX
      , IVSLRNIX
      , IVINUSIX
      , IVINSVIX
      , IVDMGIDX
      , IVVARIDX
      , DPSHPIDX
      , PURPVIDX
      , UPPVIDX
      , IVRETIDX
      , ASMVRIDX
      , ITMCLSCD
      , ITMTRKOP
      , LOTTYPE
      , KPERHIST
      , KPTRXHST
      , KPCALHST
      , KPDSTHST
      , ALWBKORD
      , VCTNMTHD
      , UOMSCHDL
      , ALTITEM1
      , ALTITEM2
      , USCATVLS_1
      , USCATVLS_2
      , USCATVLS_3
      , USCATVLS_4
      , USCATVLS_5
      , USCATVLS_6
      , MSTRCDTY
      , MODIFDT
      , CREATDDT
      , WRNTYDYS
      , PRCLEVEL
      , LOCNCODE
      , PINFLIDX
      , PURMCIDX
      , IVINFIDX
      , INVMCIDX
      , CGSINFLX
      , CGSMCIDX
      , ITEMCODE
      , TCC
      , PriceGroup
      , PRICMTHD
      , PRCHSUOM
      , SELNGUOM
      , KTACCTSR
      , LASTGENSN
      , ABCCODE
      , Revalue_Inventory
      , Tolerance_Percentage
      , Purchase_Item_Tax_Schedu
      , Purchase_Tax_Options
      , ITMPLNNNGTYP
      , STTSTCLVLPRCNTG
      , CNTRYORGN
      , INACTIVE
      , MINSHELF1
      , MINSHELF2
      , INCLUDEINDP
      , LOTEXPWARN
      , LOTEXPWARNDAYS
      , LASTGENLOT
      , Lot_Split_Quantity
      , UseQtyOverageTolerance
      , UseQtyShortageTolerance
      , QtyOverTolerancePercent
      , QtyShortTolerancePercent
      , IVSCRVIX
      , DEX_ROW_TS
      FROM #tmp;

      ENABLE TRIGGER ALL ON dbo.IV00101;

      COMMIT TRANSACTION;
      END TRY
      BEGIN CATCH
      IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION;

      SET @ErrorNumber = ERROR_NUMBER();
      SET @ErrorLine = ERROR_LINE();
      SET @ErrorMessage = ERROR_MESSAGE();
      SET @ErrorSeverity = ERROR_SEVERITY();
      SET @ErrorState = ERROR_STATE();

      PRINT ‘Actual error number: ‘ + CAST(@ErrorNumber AS VARCHAR(10));
      PRINT ‘Actual line number: ‘ + CAST(@ErrorLine AS VARCHAR(10));

      RAISERROR( @ErrorMessage, @ErrorSeverity, @ErrorState );
      END CATCH;

      ——————————
      Dustin Jones
      SharePoint & Database Administrator
      Emeco International Pty Ltd
      Osborne Park
      ——————————
      ——————————————-

    • Dustin Jones

      Member

      February 2, 2017 at 12:04 AM

      The update trigger for DEX_ROW_TS looks like this:

      CREATE TRIGGER dbo.zDT_IV00101U
      ON dbo.IV00101
      AFTER UPDATE
      AS
      SET NOCOUNT ON;

      BEGIN
      UPDATE dbo.IV00101
      SET DEX_ROW_TS = GETUTCDATE()
      FROM dbo.IV00101
      , inserted
      WHERE IV00101.ITEMNMBR = Inserted.ITEMNMBR;
      END;

      SET NOCOUNT OFF;

      Found via commands:
      EXEC sp_helptrigger ‘IV00101’

      EXEC sp_helptext ‘zDT_IV00101U’

      There is no unique index or pk constraint on DEX_ROW_ID – By itself – (there is however one combining DexRow & ItemCode or DexRow & UoMSchedule) so you could technically put identity_insert on for the table and overwrite all DEX_ROW_ID values with some constant by using the same script in the previous post just adding on DEX_ROW_ID.

      ——————————
      Dustin Jones
      SharePoint & Database Administrator
      Emeco International Pty Ltd
      Osborne Park
      ——————————
      ——————————————-

    • David Musgrave

      Member

      February 2, 2017 at 1:14 AM

      Hi

      Did you do any maintenance or table upgrades?

      If the data was backed up and the table recreated and then the data restored, it will reset the DEX_ROW_ID values.

      David

      ——————————
      David Musgrave MVP, GPUG All-Star

      Managing Director
      Winthrop Development Consultants

      Perth, Western Australia

      http://www.winthropdc.com
      ——————————
      ——————————————-

    • David Morinello

      Member

      February 2, 2017 at 8:48 AM

      Just a fun add-on (for those strange people like myself that find this fun), here is a little useful SQL for the DEX_ROW_TS field. This field is a UTC date time and it can be converted to your local time. We have sales info that come in from all over the US and some from outside. I find this very useful in reporting.

      Select DEX_ROW_TS, DATEADD(hh, 6, DEX_ROW_TS) AS [CST], …

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

    Jeff Roe replied 7 years, 5 months ago 1 Member · 0 Replies
  • 0 Replies

Sorry, there were no replies found.

The discussion ‘IV00101 Dex_Row_ID reset’ is closed to new replies.

Start of Discussion
0 of 0 replies June 2018
Now

Welcome to our new site!

Here you will find a wealth of information created for people  that are on a mission to redefine business models with cloud techinologies, AI, automation, low code / no code applications, data, security & more to compete in the Acceleration Economy!