eConnect Requester and eConnect_Out problem prevents entry of Purchase Orders

Today I tried to enter a purchase order in my client's GP 9 Test database.  As soon as I tabbed to the PO number field, GP would hang, and after a while would come back saying that "a unique po number couldn't be assigned".

I checked the POP setup window and verified that there was a next PO number setup, and even tried changing the number.  Still no luck.

I found this old newsgroup thread where the brilliant Mohammad Daoud and Mariano Gomez offered some suggestions to this problem.  After trying Mohammad's suggestion (add and remove the alpha prefix), and even Mariano's scripts to reset the next number, the error still persisted.

The next step was to pull out my good buddy SQL Profiler.  After starting a trace, I tabbed to the PO number field, and 6,000 SQL statements whizzed by in Profiler.  Fun.

Fortunately, as Mariano explains in his excellent blog post, the 6,000 statements were just GP trying to get the next document number 1,000 times. 

I looked at the different statements, and tested them, and everything looked fine--GP would get the next number from the POP setup table, make sure it wasn't in use, and then proceed to use it to create a new record in POP10100.  But then it would do the same thing again.  And again.

So I finally took POP10100 insert statement from SQL Profiler and tried to run it manually.  Viola, there was my error.  It was an error on the eConnect_Out table, indicating that there was no default value for the DATE1 field.  When the POP10100 insert occurred, the eConnect Out trigger was causing the POP insert to fail.  This, along with a few other weird errors gave me a clue that something was wrong with the eConnect setup on this test database.

I can't begin to explain the exact cause of the problem or how the Test database broke, but to resolve this, I first renamed the eConnect_Out, eConnect_Out_Setup, and eConnectOutTemp tables, to eConnect_Out1, eConnect_Out_Setup1, etc.

I then ran the eConnect 9 setup to install eConnect in a new company database.  I specified the Test database in the connection string and clicked install.  The eConnect_Out tables were recreated as part of the setup process.

I then went back into eConnect Requester Setup, configured it to track Purchase Order inserts and updates, and then tried entering a new PO.

Fortunately, that did the trick and GP was able to grab the next PO number and insert a record into POP10100.

After thinking about it, one thing that concerns me is if this would have happened on a GP 10 or GP 2010 install.   With GP 9, the eConnect install is separate, but with GP 10 and 2010, it's included in the GP setup, so I'm not sure how I would recreate the tables.  There may be a way, but I've never had to consider it.

Whew!  Is the day over yet?