You’re offline. This is a read only version of the page.
70
Trying to upgrade from GP 9 to GP2010. I get everything upgraded and run the GP Utilities and get the following error : The stored procedure SynchronizeTableData() of form duSQLAccountSynch : 27Pass Through SQL returned the following results: DBMS 2601, MicrosoftDynamics GP: 0. From what I read this has something to do with the field service module. We do use that mod has anyone else seen this or have any ideas for me?
*This post is locked for comments
Hi Chris,
I'm not sure if you're still getting this error message or have resolved the issue and have not updated.
I had the same issue and this was caused by an orphaned record in the SVC30701 table.
You can run the following SQL Script to find out which Item Number is causing it.
select SVC30701.ORDDOCID, SVC30701.ITEMNMBR, SVC30701.LNSEQNBR, IV00101.DECPLCUR
from SVC30701 left join IV00101 on IV00101.ITEMNMBR = SVC30701.ITEMNMBR
where IV00101.ITEMNMBR is null
You can either verify if the ITEMNMBR exists in the IV00101 or possibly the Service Line information has an invalid entry. Check the result of the LNSEQNBR, if the value is 0.0000 the assumption is that it is an orphan record and can be deleted.
I hope this helps the others who encounter the same issue as mine.
Hi,
Has anyone created the DEXSQL log? If so, what does it say a few lines before it encounters the error? For the table missing the column if you back up your data and then drop and recreate the table in SQL Maintenance, does your column present?
Kind regards,
Leslie
Hello, I am getting this error while trying upgrade the DYNAMICS database from GP2010 SP4 to GP2015 R2. I check both tables and using a where clause of only "WHERE ASI_Favorite_Dict_ID = 346" I have no results in both tables. Are you able suggest any other possible values to search for in these tables that could be causing this issue?
thansk
Kemal
Ian
Thanks again, I think I have installed all patches for GP2010 if there is one for Field Service I am not seeing that. I do need Field Service. I have tried again from scratch and I get the same error.
Hi Chris,
Do you need the Field Service Module? If not, you could drop it from the .set file - and it won't upgrade, and it won't cause any issues (once you don't include it again in the .set file).
Also - if it is an unused module - it could be that it was not upgraded correctly during a previous upgrade?
If it is a required module, and you have data in there, then you need to get it upgraded. Sorry for asking again - but have you applied all required Field Service service packs and patches?
What data is in SVC30701? You could try dropping it (back it up first) and re-creating it....or copying it from the TWO (Fabrikham) company.....
Ian
Thanks I have followed all the updates before installing GP2010. It seems to be the field service that is causing the issue. I can start the upgrade over and see what happens. Yes this is a test server with a copy of our live database.
Hi Chris,
Did you follow the recommended path to upgrade from 9 to 2010? including service pack levels for each version etc? I haven't looked to closely at the errors you are getting - but the last one is very likely an incorrect table definition.
Your problems are most likely an issue with the path you are now following to upgrade - or a previous upgrade.
Is this in a test enviornment? If so - I would restart the upgrade from the very beginning and make sure you follow the upgrade path exactly (or indeed take a longer upgrade path if one is avaiable) - including service packs for all products listed in your dynamics.set and dynutils.set files. You could try and debug these issues - I have done so in the past when there was no other option - but you don't know how many you will get or how long it will take.
Ian.
Got this one working, but now a new issue with the DECPLCUR in the SVC30701 table. It does not like NULL bu that column does not even exsist in the SVC30701?
Error 135 and it says for the resolution to Remove the temp table at the database level - im not sure what that means.
When I run the following script
SELECT b.fileOSName, a.fileNumber, a.PRODID, a.Status, a.errornum, a.errordes, c.CMPANYID, c.INTERID
FROM DYNAMICS.dbo.DU000030 a
JOIN
DYNAMICS.dbo.DU000010 b
ON a.fileNumber = b.fileNumber
AND a.PRODID = b.PRODID
JOIN
DYNAMICS.dbo.SY01500 c
ON a.companyID = c.CMPANYID
WHERE (a.Status <> 0 or a.errornum <> 0) and a.Status <>15
I get the following error
[Microsoft][SQL Native Client][SQL Server]Cannot insert the value NULL into column 'DECPLCUR', table 'MAI.dbo.SVC30701'; column does not allow nulls. UPDATE fails.
But again I cant update DECPLCUR because it dosnt exsist in SVC30701
Vinutha
Thanks I have now gotten past that part, but I get the following error:
The following SQL statement produced an error:
CREATE UNIQUE NONCLUSTERED INDEX [AK2EXT40800] ON [EXT40800] ( [Extender_Menu_Name] ASC, [DEX_ROW_ID] ASC ) ON [PRIMARY]
ERROR [Microsoft][SQL Native Client][SQL Server]Column name 'Extender_Menu_Name' does not exist in the target table or view.
And then the upgrade to the database fails
Hi Chris,
Please go through the below solution to resolve your issue:
Symptom
You try to upgrade from GP 9.0 to GP 2010. You run utilities and get the error:
The stored procedure SynchronizeTableData() of form
duSQLAccountSynch : 27Pass Through SQL returned the
following results: DBMS: 2601, Microsoft Dynamics GP: 0.
DexSQL.log shows the error:
/* Date: 11/04/2011 Time: 17:20:50
SQLSTATE:(23000) Native Err:(2601) stmt(9097720):*/
[Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.ASIEXP86' with unique index 'AK2ASIEXP86'.*/
/*
Cause
An entry in the ASIEXP86 matches a record in ASIEXP86_T
Resolution
1. You need to search for the duplicate entry between the two tables. You run the following query in SQL:
SELECT * FROM DYNAMICS..ASIEXP86_T WHERE ASI_Favorite_Dict_ID = 346 and ASI_Favorite_Type = 1 and ASI_Favorite_Save_Level = 0
SELECT * FROM DYNAMICS..ASIEXP86 WHERE ASI_Favorite_Dict_ID = 346 and ASI_Favorite_Type = 1 and ASI_Favorite_Save_Level = 0
Note: You do this for each combination of ASI_Favorite_Type[1-9] and ASI_Favorite_Save_Level = [0-1].
2. You verify the following columns have the same value in both tables for the duplicate record:
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
3. You delete the duplicate record from the ASIEXP86. Delete from DYNAMICS..ASIEXP86 WHERE dex_row_ID = '3467'
4. Make sure the DU000030 table is clean, and run the upgrade again.
More Information
You can also use the duplicates query before using the query in the resolution section:
select ASI_Favorite_Dict_ID, ASI_Favorite_Type, ASI_Favorite_Save_Level, CMPANYID, USRCLASS, USERID, ASI_Favorite_Name, ASI_Field_Sequence from DYNAMICS..ASIEXP86
group by ASI_Favorite_Dict_ID, ASI_Favorite_Type, ASI_Favorite_Save_Level, CMPANYID, USRCLASS, USERID, ASI_Favorite_Name, ASI_Field_Sequence having COUNT(*)>1
Regards,
Vinutha
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,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (