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

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.

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

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 

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

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

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.

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.

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.  

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

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

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

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?

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

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.