SBX - Search With Button

SBX - Forum Post Title

GP 2015 R2 errors during upgrade & possible solutions

Microsoft Dynamics GP Forum

Jorge Mejia asked a question on 2 Jun 2015 4:41 AM
My Badges

Question Status

Verified

I came across 2 errors while I was trying to upgrade GP 2015 (14.0.661) to GP 2015 R2 (14.0.725).

After running the Dynamics GP 2015 R2 Utilities got this error related to table ASIEXP86 - SmartList Favorites Columns.

 


And from the DEXSQL.LOG

Cannot insert duplicate key row in object 'dbo.ASIEXP86' with unique index 'AK2ASIEXP86'. The duplicate key value is…

I resolved the problem related to table ASIEXP86 by creating a table backup

SELECT * INTO ASIEXP86Backup FROM ASIEXP86

And then deleting all rows:

DELETE FROM ASIEXP86

The plan was to restore the data from table ASIEXP86Backup into table ASIEXP86 after the Dynamics GP 2015 R2 upgrade using this TSQL

 

INSERT INTO ASIEXP86 (ASI_Favorite_Dict_ID, ASI_Favorite_Type, ASI_Favorite_Save_Level, CMPANYID, USRCLASS, USERID, ASI_Favorite_Name, ASI_Field_Sequence, ASI_Include_Column, ASI_Display_Column, ASI_Field_Number_Dict_ID, ASI_Field_Number, ASI_Column_Display_Name, ASI_Display_Column_Width)

SELECT ASI_Favorite_Dict_ID, ASI_Favorite_Type, ASI_Favorite_Save_Level, CMPANYID, USRCLASS, USERID, ASI_Favorite_Name, ASI_Field_Sequence, ASI_Include_Column, ASI_Display_Column, ASI_Field_Number_Dict_ID, ASI_Field_Number, ASI_Column_Display_Name, ASI_Display_Column_Width FROM ASIEXP86Backup

 

After deleting all rows from table ASIEXP86 y run Dynamics GP2015 R2 Utilities, I didn’t get an error on table ASIEXP86. Success!!! Unfortunately I claimed victory too early…I came across a second error related to table SY07240 - List View Action Pane

 

  

And from the DEXSQL.LOG

Cannot insert the value NULL into column 'CmdSequence', table 'DYNAMICS.dbo.SY07240'; column does not allow nulls. INSERT fails

I hope Microsoft will release a hotfix soon.

Reply
Tim Foster responded on 2 Jun 2015 7:27 AM
My Badges
Suggested Answer

I had the same problems yesterday as well.  My solutions were slightly different.

In the case of ASIEXP86, I deleted the offending (duplicate key) rows.

For SY07240, I forced the SQL INSERTS that the R2 Upgrade wanted to do.

There is another thread on this.

community.dynamics.com/.../162174

A Hotfix can't come soon enough.

TIm

Reply
Jorge Mejia responded on 2 Jun 2015 7:35 AM
My Badges

I also looked for duplicates in table ASIEXP86 for both indexes but I couldn't find any.

Did you upgrade run successfully after running the updated SQL for table SY07240?

Reply
Tim Foster responded on 2 Jun 2015 7:38 AM
My Badges

Yes, it did.  No error in the Company upgrades.

I am getting a trigger registration error on startup.  I haven't determined if it is GP or a third party.

Tim

Reply
Jorge Mejia responded on 2 Jun 2015 7:45 AM
My Badges

I think all these workarounds are ok only for testing purposes and for exploring GP2015 R2, but I wouldn't risk to install it in a production environment.

I haven't tried installing GP2015 R2 using the full installer 'MDGP2015_R2_DVD_ENUS' yet, it might work better.

Reply
Bridget Sandlin responded on 2 Jun 2015 3:21 PM

For the ASIEXP86 error, once I dropped and recreated the non-clustered index [AK2ASIEXP86] to include only these fields I was okay.

[ASI_Favorite_Dict_ID] ASC,
[ASI_Favorite_Type] ASC,
[ASI_Favorite_Save_Level] ASC,
[CMPANYID] ASC,
[USRCLASS] ASC,
[USERID] ASC,
[ASI_Favorite_Name] ASC,
[ASI_Field_Sequence] ASC

 

Still haven't been able to get past the SY07240 issue.

Reply
Jorge Mejia responded on 2 Jun 2015 4:43 PM
My Badges

Hi Bridget, Microsoft team is looking into this, I hope we will have a fix soon.

Reply
Nick Wilson responded on 3 Jun 2015 4:04 AM

Have the same problems, I managed to fix the ASIEXP86 by removing the duplicates by comparing the ASIEXP86 and ASIEXP86T tables. Also get the SY07240 error and after some troubleshooting can't work out how to fix the problem.

Can someone from Microsoft confirm a bug or a fix/workaround to get the SY07240 through the upgrade

Reply
Jorge Mejia responded on 3 Jun 2015 5:19 AM
My Badges
Verified Answer

Problem solved! Just follow these steps:

1. Take a backup of table SY07240

SELECT * INTO SY07240Backup FROM SY07240

2. Delete all rows from SY07240

DELETE FROM SY07240

3. Run Dynamics GP Utilities. You will get this error:

4. Click “Close” on the above window. You will presented with the following window:

5. Restore rows into table SY07240 by running this SQL script:

INSERT INTO SY07240 (ListDictID, ListID, ViewID, CmdParentDictID, CmdParentFormID, CmdParentCmdID, CmdSequence, CmdDictID, CmdFormID, CmdID, Priority, ButtonSize, CmdCaption, Visible)
SELECT ListDictID, ListID, ViewID, CmdParentDictID, CmdParentFormID, CmdParentCmdID, CmdSequence, CmdDictID, CmdFormID, CmdID, Priority, ButtonSize, CmdCaption, Visible
FROM SY07240Backup

6. Click “Update” in the window from step 4.

7. That’s it

Thanks

Jorge Mejia

Reply
Bridget Sandlin responded on 3 Jun 2015 8:48 AM

I should add that I was updating from the latest build of GP2010 to GP2015 R2 using the full installer and even with an empty SY07240 table to start, I could not get it to upgrade successfully (despite the table definitions being identical between versions). I ended up updating the DU000030 directly to set the table upgrade status. The table populates in GP2015 R2 without issue when clicking on the navigation pane objects.

Also, I did not have any duplicates in the ASIEXP86 table but got the error because the non-clustered index did not match the primary keys.

Reply
Tim Foster responded on 3 Jun 2015 11:05 AM
My Badges

I disagree (respectfully) with your approach to ASIEXP86.

You will find no duplicate records (pre-upgrade) because of the Primary Key/Index applied to the table.

The "duplicate" records are being inserted by the upgrade script.  This problem seems to  affects only "default" Smartlist favorites (i.e. the one with the stars at the end that are included with a default install on the product).  It looks like the upgrade assumes that no changes were made to these column objects.  In my system, someone may have changed the default or my default doesn't meet the expectation because of the version hops.

Redefining the Index may have got you past the upgrade challenge, but may cause you some real grief during UAT.

Tim

Reply
Jorge Mejia responded on 3 Jun 2015 11:57 AM
My Badges
Suggested Answer

I agree with Tim, there cannot be duplicates in table ASIEXP86 because of the primary key and index as I already mentioned.

One way to overcome this problem is by taking a backup of table ASIEXP86, deleting all records from it and after the upgrade restoring table ASIEXP86 from the table backup.

Reply
Peter J Kwasnicki responded on 3 Jun 2015 4:26 PM
My Badges

Removing the records from ASIEXP86 appears to have worked for me, however, there are now new records in ASIEXP86 so I'm hesitant to copy the records from the backup table back into this table. The backup table has 1383 records whereas the newly inserted table has 876 records. I'm not sure if this should be considered valid or not. I know MS was looking into this issue as well, has anyone heard confirmation that the above is a supported solution?

Note that I did not seem to run into the issues others have reported with the SY07240 table.

Thanks,

Peter

Reply
Jorge Mejia responded on 3 Jun 2015 5:47 PM
My Badges
Verified Answer

Hi Peter,

Compare the column ASI_Favorite_Dic_ID from ASIEXP86 and its backup. You will notice that there are products missing in the newly inserted records e.g. 949 (Product ID for Field Service) and any other 3rd party products.

Restoring the backup table shouldn't cause you problems. Worse case you can delete again all records from ASIEXP86 and next time you log into GP, you will get your 876 records back.

Reply
Chrystal Chambers responded on 4 Jun 2015 10:19 AM
My Badges
Verified Answer

We were faced with both of these errors.  Microsoft support says they don't have a fix as of today and unfortunately none of the responses here have gotten us passed them.  Here is what we had to do to get passed the errors.  Note that we implemented these fixes in a test upgrade environment, I will update this post if we come across issues during testing we think are related to these workarounds.

TO FIX THE ASIEXP86 ERROR

Drop the AK2ASIEXP86 index from ASIEXP86: 

DROP INDEX [AK2ASIEXP86] ON [DYNAMICS].[dbo].[ASIEXP86]

Run utilities again, should get passed the error

Remove duplicates from table:

begin tran
WITH CTE AS(
SELECT [ASI_Favorite_Dict_ID],
[ASI_Favorite_Type],
[ASI_Favorite_Save_Level],
[CMPANYID],
[USRCLASS],
[USERID],
[ASI_Favorite_Name],
[ASI_Field_Number_Dict_ID],
[ASI_Field_Number],
[ASI_Field_Sequence],
RN = ROW_NUMBER()OVER(PARTITION BY
[ASI_Favorite_Dict_ID],
[ASI_Favorite_Type],
[ASI_Favorite_Save_Level],
[CMPANYID],
[USRCLASS],
[USERID],
[ASI_Favorite_Name],
[ASI_Field_Number_Dict_ID],
[ASI_Field_Number]
ORDER BY [ASI_Favorite_Dict_ID],
[ASI_Favorite_Type],
[ASI_Favorite_Save_Level],
[CMPANYID],
[USRCLASS],
[USERID],
[ASI_Favorite_Name],
[ASI_Field_Number_Dict_ID],
[ASI_Field_Number], [ASI_Field_Sequence])
FROM dbo.ASIEXP86
)
DELETE FROM CTE WHERE RN > 1
commit

Run this to add index back: 

USE [DYNAMICS]
GO

/****** Object: Index [AK2ASIEXP86] ******/
CREATE UNIQUE NONCLUSTERED INDEX [AK2ASIEXP86] ON [dbo].[ASIEXP86]
(
[ASI_Favorite_Dict_ID] ASC,
[ASI_Favorite_Type] ASC,
[ASI_Favorite_Save_Level] ASC,
[CMPANYID] ASC,
[USRCLASS] ASC,
[USERID] ASC,
[ASI_Favorite_Name] ASC,
[ASI_Field_Number_Dict_ID] ASC,
[ASI_Field_Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

----------------------------------------------------------------------------------------------------

TO FIX LIST VIEW ACTION PANE ERROR

Add this trigger:
Create trigger ctrig_SY07240_ii on [DYNAMICS].[dbo].[SY07240] instead of insert as
begin

insert [DYNAMICS].[dbo].[SY07240] (ListDictID, ListID, ViewID, CmdParentDictID, CmdParentFormID, CmdParentCmdID, CmdSequence, CmdDictID, CmdFormID, CmdID, Priority, ButtonSize, CmdCaption, Visible)
select ListDictID, ListID, ViewID, CmdParentDictID, CmdParentFormID, CmdParentCmdID, isnull(CmdSequence, 1), CmdDictID, CmdFormID, CmdID, Priority, ButtonSize, CmdCaption, Visible
from inserted

end

Run utilities, you should get passed the error
Drop the trigger:
DROP TRIGGER [ctrig_SY07240_ii] ON [DYNAMICS].[dbo].[SY07240]

Thanks,
Chrystal Chambers

Kraft Enterprise Systems

Reply
Tim Foster responded on 4 Jun 2015 10:46 AM
My Badges

Chrystal,

Love the trigger for SY07240!

Fantastic solution.

Tim

Reply
Jorge Mejia responded on 3 Jun 2015 5:19 AM
My Badges
Verified Answer

Problem solved! Just follow these steps:

1. Take a backup of table SY07240

SELECT * INTO SY07240Backup FROM SY07240

2. Delete all rows from SY07240

DELETE FROM SY07240

3. Run Dynamics GP Utilities. You will get this error:

4. Click “Close” on the above window. You will presented with the following window:

5. Restore rows into table SY07240 by running this SQL script:

INSERT INTO SY07240 (ListDictID, ListID, ViewID, CmdParentDictID, CmdParentFormID, CmdParentCmdID, CmdSequence, CmdDictID, CmdFormID, CmdID, Priority, ButtonSize, CmdCaption, Visible)
SELECT ListDictID, ListID, ViewID, CmdParentDictID, CmdParentFormID, CmdParentCmdID, CmdSequence, CmdDictID, CmdFormID, CmdID, Priority, ButtonSize, CmdCaption, Visible
FROM SY07240Backup

6. Click “Update” in the window from step 4.

7. That’s it

Thanks

Jorge Mejia

Reply
Jorge Mejia responded on 3 Jun 2015 5:47 PM
My Badges
Verified Answer

Hi Peter,

Compare the column ASI_Favorite_Dic_ID from ASIEXP86 and its backup. You will notice that there are products missing in the newly inserted records e.g. 949 (Product ID for Field Service) and any other 3rd party products.

Restoring the backup table shouldn't cause you problems. Worse case you can delete again all records from ASIEXP86 and next time you log into GP, you will get your 876 records back.

Reply
Chrystal Chambers responded on 4 Jun 2015 10:19 AM
My Badges
Verified Answer

We were faced with both of these errors.  Microsoft support says they don't have a fix as of today and unfortunately none of the responses here have gotten us passed them.  Here is what we had to do to get passed the errors.  Note that we implemented these fixes in a test upgrade environment, I will update this post if we come across issues during testing we think are related to these workarounds.

TO FIX THE ASIEXP86 ERROR

Drop the AK2ASIEXP86 index from ASIEXP86: 

DROP INDEX [AK2ASIEXP86] ON [DYNAMICS].[dbo].[ASIEXP86]

Run utilities again, should get passed the error

Remove duplicates from table:

begin tran
WITH CTE AS(
SELECT [ASI_Favorite_Dict_ID],
[ASI_Favorite_Type],
[ASI_Favorite_Save_Level],
[CMPANYID],
[USRCLASS],
[USERID],
[ASI_Favorite_Name],
[ASI_Field_Number_Dict_ID],
[ASI_Field_Number],
[ASI_Field_Sequence],
RN = ROW_NUMBER()OVER(PARTITION BY
[ASI_Favorite_Dict_ID],
[ASI_Favorite_Type],
[ASI_Favorite_Save_Level],
[CMPANYID],
[USRCLASS],
[USERID],
[ASI_Favorite_Name],
[ASI_Field_Number_Dict_ID],
[ASI_Field_Number]
ORDER BY [ASI_Favorite_Dict_ID],
[ASI_Favorite_Type],
[ASI_Favorite_Save_Level],
[CMPANYID],
[USRCLASS],
[USERID],
[ASI_Favorite_Name],
[ASI_Field_Number_Dict_ID],
[ASI_Field_Number], [ASI_Field_Sequence])
FROM dbo.ASIEXP86
)
DELETE FROM CTE WHERE RN > 1
commit

Run this to add index back: 

USE [DYNAMICS]
GO

/****** Object: Index [AK2ASIEXP86] ******/
CREATE UNIQUE NONCLUSTERED INDEX [AK2ASIEXP86] ON [dbo].[ASIEXP86]
(
[ASI_Favorite_Dict_ID] ASC,
[ASI_Favorite_Type] ASC,
[ASI_Favorite_Save_Level] ASC,
[CMPANYID] ASC,
[USRCLASS] ASC,
[USERID] ASC,
[ASI_Favorite_Name] ASC,
[ASI_Field_Number_Dict_ID] ASC,
[ASI_Field_Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

----------------------------------------------------------------------------------------------------

TO FIX LIST VIEW ACTION PANE ERROR

Add this trigger:
Create trigger ctrig_SY07240_ii on [DYNAMICS].[dbo].[SY07240] instead of insert as
begin

insert [DYNAMICS].[dbo].[SY07240] (ListDictID, ListID, ViewID, CmdParentDictID, CmdParentFormID, CmdParentCmdID, CmdSequence, CmdDictID, CmdFormID, CmdID, Priority, ButtonSize, CmdCaption, Visible)
select ListDictID, ListID, ViewID, CmdParentDictID, CmdParentFormID, CmdParentCmdID, isnull(CmdSequence, 1), CmdDictID, CmdFormID, CmdID, Priority, ButtonSize, CmdCaption, Visible
from inserted

end

Run utilities, you should get passed the error
Drop the trigger:
DROP TRIGGER [ctrig_SY07240_ii] ON [DYNAMICS].[dbo].[SY07240]

Thanks,
Chrystal Chambers

Kraft Enterprise Systems

Reply
Tim Foster responded on 2 Jun 2015 7:27 AM
My Badges
Suggested Answer

I had the same problems yesterday as well.  My solutions were slightly different.

In the case of ASIEXP86, I deleted the offending (duplicate key) rows.

For SY07240, I forced the SQL INSERTS that the R2 Upgrade wanted to do.

There is another thread on this.

community.dynamics.com/.../162174

A Hotfix can't come soon enough.

TIm

Reply
Jorge Mejia responded on 3 Jun 2015 11:57 AM
My Badges
Suggested Answer

I agree with Tim, there cannot be duplicates in table ASIEXP86 because of the primary key and index as I already mentioned.

One way to overcome this problem is by taking a backup of table ASIEXP86, deleting all records from it and after the upgrade restoring table ASIEXP86 from the table backup.

Reply
olegvi responded on 7 Jun 2015 11:10 AM
My Badges
Suggested Answer

Thank you Chrystal. The trigger saved the day.

Reply
Tim Foster responded on 17 Jun 2015 3:56 PM
My Badges
Suggested Answer

Bill,

Please look at:

community.dynamics.com/.../draft-known-upgrade-issues-when-upgrading-to-microsoft-dynamics-gp-2015-r2

There are scripts there that I believe should help.

Tim

Reply
TerryP2a responded on 6 Jul 2015 10:36 AM
Suggested Answer

Worked like a charm..

thanx

Reply

SBX - Two Col Forum

SBX - Migrated JS