[Jeff Roe]

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]

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]

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]

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]

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
——————————
——————————————-