I am encountering an issue with the ASIEXP86 table when upgrading to Microsoft Dynamics GP 2015 R2:
"The stored procedure SynchronizeTableData() of form duSQLAccountSynch : 27Pass Through SQL returned the following results: DBMS: 2601, Microsoft Dynamics GP: 0."
*This post is locked for comments
Thanks for the updates Rob,
It is true that we haven't been getting many reports of this particular issue because it is due to a specific data condition that few environments seem to have. I'm happy to hear that you're also seeing upgrades complete without any issues.
On a more positive note, I just did a 2013R2 to 2016 to 2018 upgrade today, and NO errors with the Smartlist tables, and they even use AA and Payroll. :)
Lucas, I stopped deleting duplicates after about 20 of them. It is a laborious process to delete them and go back and run the utilities again only to have it fail again and go back and look at the trace and continue deleting. I ended up just truncating the table. GP re-created it in utilities of course. Then I went into the backup of the table and copied all the missing records back in letting SQL assign a dex row ID. Their smartlists all look fine and no issues thus far, although they need to test further.
Rob,
That behavior is not consistent with the nature of the issue we've been discussing. How many duplicate records did you remove? Was it 350?
Also, missing records in the ASIEXP86 would not cause Favorites to disappear from the SmartList window. At most it would cause columns to stop appearing for those Favorites. The only way Favorites would disappear is if you removed them from the ASIEXP81 table.
Unless you want to try manually importing the data from your backup table to see how that works I would suggest that the best path forward is to restore DYNAMICS back and capture a SQL Profiler trace while upgrading it again so we can see exactly what your error is and then just delete the relevant record. It may be necessary to restore DYNAMICS back, remove that "duplicate" and run the upgrade again with a trace running so you can observe any other errors that might happen. Luckily the DYNAMICS database upgrades quick and it's easy to restart the upgrade in these sort or exploratory situations.
Lucas, I backed up the table before deleting the "duplicate" rows as mentioned. Now we have 350 rows that DONT exist in the new ASIEXP86 table that did before deletion, and they are all "user" favorites, which appear to have disappeared.
select * from dynamics..asiexp86backup WHERE
asi_favorite_name not in (select ASI_Favorite_Name from asiexp86)
How do I add those rows back into the ASIEXP86 table so the users don't loose their favorites?
Thanks Lucas, that does make more sense now. So, if I had deleted something from ASIEXP86 that happened to be part of someones actual columns in their favorites, would they just see the default column name? In another upgrade from 2010 to 2018 that I did (not R2), I deleted rows, and then when they ran their SmartList for some of the columns they got "Message 22786 Missing" instead of the name of the column. :) This was on the ASIEXP86 table it happens when you go on the hop from 2010 to 2015R2 (even with the latest service pack applied).
Here are a few others:
GP 2010 --> SP4 --> 2015R2 --> On the R2 hop with the latest service pack:
2010 SP4 Hop worked fine
2015R2: primary key violation
Had to run this against all dupes:
DELETE DYNAMICS..ASIEXP86
WHERE ASI_Favorite_Dict_ID = 0
AND ASI_Favorite_Type = 12
AND ASI_Favorite_Save_Level = 1
AND CMPANYID = 0
(below are the dupes I found)
0, 3, 1, 0
0, 9, 1, 0
0, 12, 1, 0
Here's another 2010 to 2015R2 upgrade (with latest SP):
"Conversion failed when converting the varchar value 'USD Credit' to data type int
(in this example upgrade I had to rename all the columns back to Debit or Credit, the client had renamed the default names Debit and Credit to USD Debit and USD Credit for some reason)
So, I've had a few upgrades, even after your 2015R2 hotfix that supposedly fixed the smartlist issue..
It seems on ALL Of them, the common denominator is that the user renamed some of the default columns.
Rob
Rob,
It's probably best to just explain what is going on during the upgrade of this table since it's different than some of the other modules:
1) First, a new table is created called ASIEXP86_T
2) The BULK INSERT command is run to populate ASIEXP86_T with all of the default column information for the default SmartList Favorites
3) Duplicate records (based on the PKASIEXP86 index) are deleted from the ASIEXP86
4) The default records from the ASIEXP86_T are inserted into ASIEXP86. This is where the error is happening, but it's on the AK2ASIEXP86 index rather than the Primary Key.
In my testing I only ever had it happen on the Employees SmartList when I added a new column to that, which adds a new record to the ASIEXP86. I couldn't repro with other SmartLists.
So, to answer you question in a roundabout way, no, deleting a duplicate from the ASIEXP86 table will not negatively change anything because that exact same record is being inserted back into the table by the above process. Also, the SmartList favorites themselves are actually stored in the ASIEXP81 table. The ASIEXP86 just holds the columns that are displayed on each of those favorites.
Lucas Miller By deleting the rows in ASIEXP86 causing the primary key violation, will the user loose all their custom Smartlist favorites upon upgrade?
@Rob Klaproth: Thanks for your feedback. To my knowledge this is the first time we've had a known issue with the ASIEXP86 since the previous issue in GP 2015 and that was resolved in 2015 R2. If you're still getting errors involving that table please do open support cases to let us troubleshoot and document the issue. Also, I asked around to see if anyone has had any upgrade cases where the cause was changing the Display Name of columns in a SmartList Favorite, but no one could recall such a situation. If you're seeing errors around that you should open a support case the next time it happens so we can investigate further.
@Andy Seymour: At this point the issue was first reported on Thursday evening and I tested and write it up for the Product Group on Friday. I imagine it will be addressed in the upcoming year-end patch for GP 2018. Also, you could work through removing any duplicates you're seeing and still successfully upgrade to GP 2018 R2. The blog article I linked above has the steps for you.
Let me know if you have any questions.
I just got this error upgrading to GP2018 R2 that was just released a week or so ago. When will there be a service pack as I will need to upgrade the Customer to an earlier version of GP 2018 to get round this problem and apply the Service Pack when released.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (