eConnect Invalid object name ‘PA01901’ GP2010 sp1

After test upgrade of one of our GP installations we were getting the above error when trying to create a purchase order via eConnect. ‘PA01901’ is one of the project accounting tables, a module we don’t have installed.

Late into the night I Googled the issue and found almost nothing about it, also tried Customer Source where again there were some whispers but no substance.

clip_image001

I downloaded the service packs for eConnect. After applying eConnect Service packs for eConnect version11 up to pack 2 we still had the error.

Service packs tried;
MicrosoftDynamicsGP11-eConnect-x86-KB2386133-E.msp
MicrosoftDynamicsGP11-eConnect-x86-KB2435574-E.msp
MicrosoftDynamicsGP11-eConnect-x86-KB2561289-E.msp

After some pointers from a GP contact I have, it turns out that the eConnect stored procedure. taPoHdr is to blame. You can see the check below for the existence of the table in that procedure, the point at which the failure occurs.

if exists(select 1 from dbo.sysobjects (nolock) where name  = 'PA01901')
and exists (select 1 from POP10110 (nolock) where PONUMBER = @I_vPONUMBER)
and not exists
(select 1 from PA01901 (nolock) where PATranType = 6 and PADocnumber20 = @I_vPONUMBER)
begin
...

 

The statement tries to see if it has any rows in the table if it exists or not. Obviously if it does not exist you can’t read the rows and falls over, it should have been nested logic here. An easy scripting mistake to make, shame it made it into production code though. It does prove the importance of coverage in testing and checking all code paths…

Solutions

This can be corrected as shown below by nesting correctly. This is how the problem is solved if GP2010 SP2 is applied. This change to the stored procedure is prevented as a user as this is a protected stored procedure, encrypted to prevent tinkering. I imagine if you managed to decrypt the stored proc and apply the fix to a live environment, it would not be supported.

if exists(select 1 from dbo.sysobjects (nolock) where name  = 'PA01901')
begin
if exists (select 1 from POP10110 (nolock) where PONUMBER = @I_vPONUMBER)
and not exists
(select 1 from PA01901 (nolock) where PATranType = 6 and PADocnumber20 = @I_vPONUMBER)
begin
...
end

Alternative

You could try scripting out the table from your Fabrikam or TWO sample databases and using that to create an empty PA01901 table. However if you have not installed project accounting, it is unlikely to be there either. My other concern with that approach is that if you put those table in, other scripts might start trying to behave as if Project Accounting is installed, trying to add data to other tables that may not exists.
Safer to just get GP2010 SP2 installed.

 

 

http://community.dynamics.com/product/gp/f/32/p/42635/77416.aspx

The above post was the only related post I could find.

Comments (4) -

  • HI, I am facing same error, if you don't mind can you send me the script for PA01901 table. I really appreicate my email or to this forum.
  • Tim
    As I said in the post we don't have Project Accounting installed so I can't quickly script that table out.
    I would be cautious about that solution anyway as it may cause subsequent issues. If you create that table, you will have some objects in the database for a module you don't have properly installed. Thus updates to other tables in that module may be attempted by the system on it seeing that PA01901 is present that may result in cascading object not found errors.
    Apply SP2 or find "another way" to fix the scripting error in the eConnect Stored proc.

    Wishing you luck...
  • Wes
    This solved my problem. Thank you!
    • Tim
      Glad to hear it!

Add comment

Loading