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.

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.