Open Forum

  • 1.  Cursor Error with PSTL Customer Combiner

    TOP CONTRIBUTOR
    Posted Oct 27, 2021 05:58 PM
    Hi!

    I am curious if anyone else has had a problem with the PSTL Customer Combiner and Modifier in GP 18.3.   We upgrade on Oct 1st (just a few weeks ago) and this is the first time I've use the Combiner since upgrading.  I've done this over 56 times in GP 2015 R2 (Yes, I have a copy of all of my csv files - I know how they need to be setup :) )

    I have a file with 47 records.  The file validates just fine, but when I process the PSTL Combiner, a "ta_CCCursor SQL cursor already exists" error after about 10-15 records are processed.  I close the errors and it proceeds.  After another at about 10 or so more records the error reappears.


    I thought there might be a problem with the customers at those records, so I moved them to the top of the file, restored my test company, and reprocessed the PSTL file.  It process the problem records (that are now at the beginning of the file)  but gives the same cursor error around the same record number as the first pass.

    So, I created a macro that Enters the customer numbers and process the record one at a time.  I used Replicator to repeat this macro calling the PSTL modifier once for every customer.  Again, it fails with the Cursor Error after about same number of records.

    I recreated the macro, opening the PSTL screen, entering the customer numbers, process the merge and then closing the PSTL window.  Ran Replicator again with my 47 records.  When playing back the macro, the error still appears.  The I rearranged the customers if failed on to the top of the list, restored my Test system (for the 20th or so time) and played the macro again.  It fails again with the same error about the same record number, not on any specific customer.

    Has anyone else had any similar issues with GP 18.3 and PSTL's Customer Merge operation?

    Thanks

    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Cursor Error with PSTL Customer Combiner

    GOLD CONTRIBUTOR
    Posted Oct 28, 2021 08:44 AM
    Hi John,

    It was nice to meet you at Summit!  I hope you had a smooth trip back.

    I just ran a quick test as I use this tool a number of times too.  My test environment is 18.3.1245.  I ran it for 47 records in Fabrikam and it ran with no issues.

    ------------------------------
    John Kirsch
    GP Product Lead
    Dynamic Consulting
    Green Bay WI
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Cursor Error with PSTL Customer Combiner

    TOP CONTRIBUTOR
    Posted Oct 28, 2021 09:23 AM
    John,

    I would look for ISV conflicts.  Some ISV's document what you need to do to be compatible with PSTL.  Some don't.

    Chris

    ------------------------------
    Chris Donnelly
    Sr Mgr of Info Systems and Financial Reporting
    Healthmark Industries
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Cursor Error with PSTL Customer Combiner

    TOP CONTRIBUTOR
    Posted Oct 28, 2021 10:40 AM
    Hi John,

    My thought is third party, also. But I have run into this before and this script helped me:

    /*
    STEP 1 - Logout all the users from GP.
    STEP 2 - Find the SPID for the open cursor by running below SQL script.
    */

    SELECT CUR.session_id
    FROM sys.dm_exec_cursors(0) CUR
    WHERE CUR.name = 'ta_CCCursor'

    /*
    STEP 3 - kill the session of the user.
    */

    kill <session_id> --Replace with the integer of the results from above

    As we had a run-away session issue.

    Hope that helps,


    ------------------------------
    Mark Wiley
    Dynamics Credentialed Professional
    Senior Solutions Architect
    GraVoc
    Ohio Office
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Cursor Error with PSTL Customer Combiner

    GPUG ALL STAR
    Posted Oct 28, 2021 11:31 AM
    Hi John,
    I concur with the other replies... I ran once into this issue, and it was due to an ISV / add-on table that was causing the problem. I finally put the finger on the problem by running PSTL customer merge process thru the SQL debugger step by step (which was painfully long) until it hit the table with the problem and I figured out that it was the data type of one field that was incompatible.
    In your case the Cursor error message might be a slightly different origin, but the SQL debugger approach could help tackle the problem.

    PS: I had to decompile the ta_ SP that was involved as I believe that it was encrypted..

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: Cursor Error with PSTL Customer Combiner

    TOP CONTRIBUTOR
    Posted Oct 28, 2021 01:02 PM
    Edited by John Arnold Oct 28, 2021 01:03 PM

    I'd like to thank everyone for their input.

    Here's what I've done and learned...

    I used GP Power Tools (Thanks @David Musgrave) to Script out the SQL that was running.  The stored proc rmCustomerCombiner is called for each customer being modified.  That ends up calling the stored proc rmCustomerCombinerMaster.  rmCustomerCombinerMaster has what I would consider a bug.  It has the following logic

    CREATE CURSOR ta_CCCursor with some SQL code to excute (updates or deletes)
    Open the cursor
    While the cursor is happy, loop
       Execute some SQL returned by the cursor
       if There as an error
           Set an error state and RETURN from the stored proc
    End While
    DEALLOCATE CURSOR

    This pattern is in the rmCustomerCombinerMaster 11 times.  One of my pet peeves in programs is routines that have multiple returns.  Why?  Because it is easy to forget what needs to be cleaned up when returning early.   In this case, the RETURN happened before the DEALLOCATE CURSOR is called.  So, when there is a problem on the 10th customer, the 11th also fails because the cursor was not DEALLOCATE properly on the 10th record.

    So, I restored my test company (again...), updated the rmCustomerCombinerMaster stored proc deallocating the cursor before each return.  Then, I used replicator to create the SQL code to call rmCustomerCombinerMaster for each of my 47 records:

    DECLARE rmCustomerCombiner CHAR(27)
    DECLARE @retstat INT
    DECLARE @param3 INT
    DECLARE @Customer CHAR(31)

    SET NOCOUNT ON

    SET @Customer = 'C05650'
    EXEC @retstat = rmCustomerCombiner @Customer, 'C21290', @param3 OUTPUT
    SELECT @retstat, @param3, @Customer AS '********'

    SET @Customer = 'C14476'
    EXEC @retstat = rmCustomerCombiner @Customer, 'C65323', @param3 OUTPUT
    SELECT @retstat, @param3, @Customer AS '********'
    (Rinse and repeat)

    After running the script in SSMS with text output, I saw what I and everyone else expected...  an ISV problem.

    Violation of PRIMARY KEY constraint 'PKW5026CustItemRevs'. Cannot insert duplicate key in object 'dbo.W5026CustItemRevs'. The duplicate key value is (C50733 , E3-2000-750-NE-H-D-B , 1.00000).

    Sorry for my rambling, but I thought you might want to see the results.  It turns out the customer I thought had the problem didn't, the previous one did.



    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: Cursor Error with PSTL Customer Combiner

    GPUG ALL STAR
    Posted Oct 28, 2021 01:27 PM
    Glad you found it John!
    That's pretty much the way I nailed the problem with the same function and the ISV table with the wrong field type. Debugging and tracing, then running in step-by-step mode the code..
    The issues with most of the original PSTL codes is that they were built to fix some recurrent problems in GP, but unlikely thoroughly tested against all kinds of possible problems that could come up during execution. The tools are tested with a limited set of data (Fabrikam) and doesn't envision someone's else way of entering data.
    In french GP environment, the apostrophe is often inserted in data fields as part of the data (like L'Eglise) and this can throw SQL out when it's not properly taken care of in the code (not to speak about special characters).
    Great job!

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: Cursor Error with PSTL Customer Combiner

    TOP CONTRIBUTOR
    Posted 27 days ago
      |   view attached
    Well, I was having SO much fun with the whole PSTL thing, I thought I'd share some more results...  I created a query to look through GP tables looking for anything that might cause a problem before running the Customer Combiner (PSTL).

    Here are my comments from the attached file:

    This query will search all Tables where the keycolumn (CUSTNMBR in this example)
    is in a unique index/key. If it finds any rows where the Source and Destination
    values of the keycolumn along with all of the other columns that make up in the
    index/key are the same. Any rows that it returns will cause the PSTL Combiner to
    fail. It helps prevent the "**** Failed During update" error when running the
    PSTL Combiner.

    SQL Cursors, traversing tables and indices, SQL code that generates SQL code... is there anything more fun? :)

    Enjoy & I hope you find this useful!

    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------

    Attachment(s)

    txt
    PSTL Pre Check.txt   5 KB 1 version
    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role