You’re offline. This is a read only version of the page.
75,730
I have a routine that uses eConnect to import contracts and projects. Every now and then I run into a violation of primary key message on the budget and fees tables. This is most likely being caused by orphaned records. Looking at the documentation(taPAProjectBudgetMaster) there is a field called UpdateExisting with notes saying not currently implemented but may be in future releases. Has this ever happened? I would prefer to use that flag as opposed to having to build in a check with additional code.
Isaac, that is entirely possible. I am importing contracts from an old legacy system. All I get is a job number, date, contract amount and customer number. In GP there are 27 cost categories that could potentially be used on a project. So my import routine creates 27 budgets for each so that would be 27 x $99,999,999.99. The client uses PA basically as a holding pen. All they do is cut PO's and bill off projects. GP is responsible for financial statements while the heavy lifting of project analysis happens in the legacy system.
Hi Richard,
Maybe running a SQL Profiler while getting this error would shed some more light on this issue. Especially if you have access to the unencrypted procs.
If you used one of our Trace Templates it would make sure you are capturing some extra detail that you might need in troubleshooting this too.
http://support.microsoft.com/help/912281
The is a chance that the overflow is actually due to another table or field that ends up being updated as a result of this value being populated on the budget. Maybe the projects with the issue have more budgets than others or have more quantity in general than others. For example, maybe the ones with the issue have 99999999999.99000 unit cost times 99999999999.99000 quantity times a large number of budgets, so the amount that rolls up to the project or contract in the PA01201 or PA01101 are actually the ones blowing up. That could be why it is customer specific.
I haven't seen this personally, but that is just a thought based on what you are seeing without knowing the exact SQL being passed in that causes the error.
I hope this helps!
Isaac Olson
Microsoft Support
Thanks Isaac. I need to evaluate this as this condition only appears to affect older contracts/projects that are no longer active. If you do not mind I have a followup question on this procedure. As part of my routine it creates a series of budget records. I was setting
SET @I_PABUnitCost = 99999999999.99000 and
SET @I_PAFUnitCost = 99999999999.99000.
For some peculiar reason I had a very small set of records where the import would crash with this message:
"Arithmetic overflow error converting numeric to data type numeric"
Changing both to
SET @I_PABUnitCost = 99999999.99000 and
SET @I_PAFUnitCost = 99999999.99000.
eliminates this message. Both fields in the PA01301 table defined as numeric(19,5). Why would most records be fine but a few would not? It appeared to have something to do with the customer number.
Hi Richard,
Just to confirm, I opened up the taPAProjectBudgetMaster proc all the way back to GP10.0 and the logic built for UpdateExisting has not changed so this never ended up getting the traction needed to justify adding this in a future release.
However, if you know that these partial records are indeed corrupt or not supposed to be in the system, you could just add a quick check in the pre-proc for taPAProjectBudgetMaster to delete the record if it already exists. It wouldn't be a supported solution and I would recommend testing it, but it would be a solution that doesn't take much effort for what you are looking for.
I hope this helps,
Isaac Olson
Microsoft Support
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,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (