Notice of Maintenance on October 9th. Click here for further details.

Sage 1099 TY 2020 Stand-Alone Updates advice.

Please be sure to read the release notes in the upcoming 1099 TY 2020 Stand-Alone Updates BEFORE installing this update, including the "Best Practices" section of the notes.  Doing so may help keep the continuity of the historical functionality and help protect your data.

Parents
  • I just downloaded the update and noticed that the Maintain vendors screen (APZMA001.ExE) has not been modified and there still isn't an NEC form option.   So is the update basically changed the 1099 Form Service screen to pull box 7 data of Misc form as box 1 on the new 1099-NEC form?  Thanks

  • in reply to John Lian @The ERP Group

    Before I look into this, please let me know:

    -> Sage 500 ERP Version

    -> PU Level

    -> That you have applied the database portion of the update

    -> That you have applied the client portion of the update.

    If you are just looking over the objects modified, then please realize that the vast majority of 1099-MISC/NEC changes are actually database driven changes and will only reflect once the database update is applied.  So if you are entering a Vendor, Vendor Class, Voucher, etc, the changes only appear after the database update as there was no client code to change.  The only client code that was changed was the import portion of 1099 Beginning Balances.  Note that the old imports were not updated as we generally do not support them going forward and instead encourage DM/DI to do imports.

  • in reply to Ramon M.

    OK, I just tried to run the update on Sage 500 2018 Jan 2019 update (7.9.2) and got error applying ap1099Update2020.sql.   The error is "Msg 50000, Level 16, State 1, Line 2801
    ERROR - Unknown error."  I got the same even when I run the script manually in SSMS.   SQL server version is 2016 (14.0.3192).

    I will figure out what's wrong with the script and re-run.  I assume that's the screen that will update the metadata and such...   

    Has anyone else reported this error yet?

  • Looks like the update is somehow not changing v1099Form check constraints on various tables, including tpoRequisition, tpoPurchOrder, etc.   As a result, that script is failing with errors like "The INSERT statement conflicted with the CHECK constraint "CK__tapV1099F__V1099__26BAB19C". The conflict occurred in database "MAS500_APP", table "dbo.tapV1099Form", column 'V1099Form'."

    Did I miss something or there is a bug with the update for v2018?

  • I found the bug.   ap1099Update2020.sql does attempt to modify all the table constraints on the V1099Form field to add the new value of 4 for NEC.  However, the statement that does that misses a bunch of tables.   So when the latter part of the script attempts to add 4 to that field in those missed tables, it errors out.

    The problem code is the red-highlighted line below - A bunch of contraints don't fit that restriction.  Example is the one on tapPendVoucher table.   I fixed it by commenting this line out.  After that, the script completed successfully.   Then when I relaunch Sage 500, I get the NEC dropdown value in Vendor Maint.   Long resolution to my original question.   But I hope this helps others.    Sage should really fix this.

    ---------------excerpt from ap1099Update2020.sql------------------------------------

    SELECT
    SO.name 'TableName'
    ,SCC.name 'ConstrainName'
    FROM
    sys.check_constraints SCC
    INNER JOIN sys.objects SO ON
    SO.object_id = SCC.parent_object_id
    INNER JOIN sys.columns SC ON
    SC.object_id = SO.object_id
    WHERE
    SO.type = 'U'
    AND SC.name = N'V1099Form'
    AND SCC.name LIKE 'CK[_][_]%V1099%[_][_]%' -- Constraint name matches Sage generated values.
    AND SCC.type = 'C'
    AND SCC.definition LIKE '%V1099Form%' -- V1099Form column name is in the definition.
    --AND SCC.definition LIKE '%(3)%(2)%(1)%' -- Constraint def contains 3, 2, and 1,
    AND SCC.definition NOT LIKE '%(4)%' -- Constraint def does not include a 4 already.
    ORDER BY
    SO.name

  • in reply to John Lian @The ERP Group

    We would be happy to fix this if it is an issue.  The reason for the line you commented out is to ensure that we only change unmodified Sage triggers.  Sometimes, customization change all sorts of things, including constraints and triggers by altering them or inserting them.  I have no examples of stock Sage constraints not meeting that commented out pattern.

    If possible, could you run the following with and without the commented out line on the pre-updated database.  On a stock Sage database pre-updated, you should get 17 rows, including tapPendVoucher:

    SELECT
    SO.name 'TableName'
    ,SCC.name 'ConstrainName'
    FROM
    sys.check_constraints SCC
    INNER JOIN sys.objects SO ON
    SO.object_id = SCC.parent_object_id
    INNER JOIN sys.columns SC ON
    SC.object_id = SO.object_id
    WHERE
    SO.type = 'U'
    AND SC.name = 'V1099Form' -- modified for use outside of script
    AND SCC.name LIKE 'CK[_][_]%V1099%[_][_]%' -- Constraint name matches Sage generated values.
    AND SCC.type = 'C'
    AND SCC.definition LIKE '%V1099Form%' -- V1099Form column name is in the definition.
    -- AND SCC.definition LIKE '%(3)%(2)%(1)%' -- Constraint def contains 3, 2, and 1,
    AND SCC.definition NOT LIKE '%(4)%' -- Constraint def does not include a 4 already.
    ORDER BY
    SO.name

    Commented out, or not commented out, I get:

    TableName ConstrainName
    -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
    tapPendVoucher CK__tapPendVo__V1099__711DBAFA
    tapRecurVoucher CK__tapRecurV__V1099__2962141D
    tapV1099Box CK__tapV1099B__V1099__3A8CA01F
    tapV1099Form CK__tapV1099F__V1099__422DC1E7
    tapVend1099Bal CK__tapVend10__V1099__4AC307E8
    tapVend1099BoxText CK__tapVend10__V1099__507BE13E
    tapVend1099Hist CK__tapVend10__V1099__5540965B
    tapVend1099SubmBox CK__tapVend10__V1099__666B225D
    tapVend1099Submit CK__tapVend10__V1099__76A18A26
    tapVendClass CK__tapVendCl__V1099__1249A49B
    tapVendor CK__tapVendor__V1099__42ECDBF6
    tapVendPmtAppl CK__tapVendPm__V1099__5F891AA4
    tapVoucher CK__tapVouche__V1099__2DBCB4E6
    tpoChngOrder CK__tpoChngOr__V1099__0FAEC5C0
    tpoPurchOrder CK__tpoPurchO__V1099__47540065
    tpoRequisition CK__tpoRequis__V1099__48131A74
    txxV1099Form CK__txxV1099F__V1099__7D5BD6B2

    (17 row(s) affected)

    If I create a script from the pre-update database for the tapPendVoucher table constraint CK__tapPendVo__V1099__711DBAFA I get:

    USE [v2018_TY2018_Applied]
    GO

    ALTER TABLE [dbo].[tapPendVoucher] WITH NOCHECK ADD CHECK (([V1099Form]=(3) OR [V1099Form]=(2) OR [V1099Form]=(1)))
    GO

    The use [] statement would be different on your machine.  Can you give me the constraint create script (whatever the offending constraint is on your system) on your database pre-update so I can compare?  

    And while you are at it, can you run the following against your pre updated database so I can see what the table is for that particular constraint?

    And finally, if you happen to know the earliest version of Sage 500 that database was created from, I could look into possible issues where it changed through time.

    If there is an issue with an unmodified constraint, I will get it fixed ASAP, though the delivery would be dependent upon work schedules of others. 

    Thanks for looking at this early.

    Ramon 

     

Reply
  • in reply to John Lian @The ERP Group

    We would be happy to fix this if it is an issue.  The reason for the line you commented out is to ensure that we only change unmodified Sage triggers.  Sometimes, customization change all sorts of things, including constraints and triggers by altering them or inserting them.  I have no examples of stock Sage constraints not meeting that commented out pattern.

    If possible, could you run the following with and without the commented out line on the pre-updated database.  On a stock Sage database pre-updated, you should get 17 rows, including tapPendVoucher:

    SELECT
    SO.name 'TableName'
    ,SCC.name 'ConstrainName'
    FROM
    sys.check_constraints SCC
    INNER JOIN sys.objects SO ON
    SO.object_id = SCC.parent_object_id
    INNER JOIN sys.columns SC ON
    SC.object_id = SO.object_id
    WHERE
    SO.type = 'U'
    AND SC.name = 'V1099Form' -- modified for use outside of script
    AND SCC.name LIKE 'CK[_][_]%V1099%[_][_]%' -- Constraint name matches Sage generated values.
    AND SCC.type = 'C'
    AND SCC.definition LIKE '%V1099Form%' -- V1099Form column name is in the definition.
    -- AND SCC.definition LIKE '%(3)%(2)%(1)%' -- Constraint def contains 3, 2, and 1,
    AND SCC.definition NOT LIKE '%(4)%' -- Constraint def does not include a 4 already.
    ORDER BY
    SO.name

    Commented out, or not commented out, I get:

    TableName ConstrainName
    -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
    tapPendVoucher CK__tapPendVo__V1099__711DBAFA
    tapRecurVoucher CK__tapRecurV__V1099__2962141D
    tapV1099Box CK__tapV1099B__V1099__3A8CA01F
    tapV1099Form CK__tapV1099F__V1099__422DC1E7
    tapVend1099Bal CK__tapVend10__V1099__4AC307E8
    tapVend1099BoxText CK__tapVend10__V1099__507BE13E
    tapVend1099Hist CK__tapVend10__V1099__5540965B
    tapVend1099SubmBox CK__tapVend10__V1099__666B225D
    tapVend1099Submit CK__tapVend10__V1099__76A18A26
    tapVendClass CK__tapVendCl__V1099__1249A49B
    tapVendor CK__tapVendor__V1099__42ECDBF6
    tapVendPmtAppl CK__tapVendPm__V1099__5F891AA4
    tapVoucher CK__tapVouche__V1099__2DBCB4E6
    tpoChngOrder CK__tpoChngOr__V1099__0FAEC5C0
    tpoPurchOrder CK__tpoPurchO__V1099__47540065
    tpoRequisition CK__tpoRequis__V1099__48131A74
    txxV1099Form CK__txxV1099F__V1099__7D5BD6B2

    (17 row(s) affected)

    If I create a script from the pre-update database for the tapPendVoucher table constraint CK__tapPendVo__V1099__711DBAFA I get:

    USE [v2018_TY2018_Applied]
    GO

    ALTER TABLE [dbo].[tapPendVoucher] WITH NOCHECK ADD CHECK (([V1099Form]=(3) OR [V1099Form]=(2) OR [V1099Form]=(1)))
    GO

    The use [] statement would be different on your machine.  Can you give me the constraint create script (whatever the offending constraint is on your system) on your database pre-update so I can compare?  

    And while you are at it, can you run the following against your pre updated database so I can see what the table is for that particular constraint?

    And finally, if you happen to know the earliest version of Sage 500 that database was created from, I could look into possible issues where it changed through time.

    If there is an issue with an unmodified constraint, I will get it fixed ASAP, though the delivery would be dependent upon work schedules of others. 

    Thanks for looking at this early.

    Ramon 

     

Children
  • in reply to Ramon M.

    Hi Ramon,

    I mistyped - I meant tapRecurVoucher, tapPendVoucher did come up before I commented out the line.   I tried it on 3 of my clients' DBs, which all originated from at least 6.3...  The issue is that the pre-update check constraints on the missed tables do not have parenthesis in them, such as below.  So with that line in the whereclause, only 8 tables showed up.

    ALTER TABLE [dbo].[tapRecurVoucher] WITH CHECK ADD CHECK (([V1099Form] = 3 or ([V1099Form] = 2 or [V1099Form] = 1)))
    GO

    I don't think it hurts to re-apply the constraints for any table that checks V1099Form field.   So it's really unnecessary to have that line in the whereclause.   If you must, at least change it to remove the parenthesis from the search.

    Thanks,

    John

  • in reply to John Lian @The ERP Group

    Wow, pre 6.3!  No wonder I never saw that pattern.  I have requested a refresh.  I don't know if the refresh will appear before Christmas, but it may.  

    For anyone else reading this, it is highly unlikely that you will run into this issue unless you have been upgrading your database for about 15 years or more.

    Thanks for help track this situation down.

    Ramon

  • in reply to Ramon M.

    Well, we certainly have many loyal Sage 500 customers.    I don't think we are alone in that, especially considering the trajectory of the product.  Thanks for confirming the issue, Ramon and requesting a refresh.   I have my fix, so I should be set to plow ahead.

  • in reply to John Lian @The ERP Group

    For example of loyal Sage 500 customers we started out with Acuity back in 1996.  Hoping when I go to do the 1099 update I don't run into any issues.

  • in reply to lizf

    There is a refresh that should come out Monday (12/28).  It was discovered that Edit Voucher 1099 Data was not editing correctly.  While the NEC form did show up, there was a coding style that prevented it from processing dynamically.  The reapplication of the client side portion should fix that up once the links are refreshed.  

  • in reply to Ramon M.

    Hi Ramon, is the refresh still coming out today?  We got anxious clients who want the update installed... Thanks!

  • in reply to John Lian @The ERP Group

    Yes, it went live around 7am (PST) this morning.  I posted a general message earlier this morning.

  • in reply to Ramon M.

    Since I've already downloaded all of the 1099 client updates for all versions of Sage 500 (2017, 2018, and 2019), how are the articles identified on the KB so that I can be certain that I'm downloading the most recent update?

  • in reply to Maria Reigle

    The lack of version or revision information was an oversight in retrospect as we did not plan on revising anything.  The latest versions would include verbiage similar to:

    Apzdl001 was modified to handle the new 1099 NEC form.  When using the Edit Voucher 1099 Data the NEC for would show up in the dropdown, but the dropdown selection would not be handled correctly.

    right before section 3.2.2.

    The client install could be identified by installing it and checking to make sure apzdl001.exe or apzdldl1.dll got updated.  The source code could be identified by the existence of apzdl001 source code.  The db install was not updated, but it is always best if you get one, get all.

    Sorry for the confusion.

    Ramon

  • in reply to Ramon M.

    This 2nd release also did address the issue with the server update when run against databases created in older version such as 6.3, the issue identified above.