You’re offline. This is a read only version of the page.
40
We just upgraded from GP 2010 to GP 2013 and few days ago and also installed the latest service pack, which happened to be the 2015 Year End Update for GP 2013.
Now we get the error "A get/change operation on table 'pm1099PeriodDtl' failed accessing SQL data. Number of results columns doesnt match table definition." whenever attempting to work with any Vendor in Purchasing and access the 1099 Detail table. For example, just going to Cards => Purchasing => 1099 Detail and bringing up a vendor will generate this error. We first noticed it when trying to post a batch to payables that had a check.
So it would seem our 1099 Period Detail table has more or less fields than it should. It's hard to imagine how that happened since there were no errors during the upgrade process. We also don't have any third party products installed and just a couple of very simple VBA customizations that don't relate to this area.
Here is our 1099 Period Detail table (PM00204) definition from SQL:
[VENDORID] [char](15) NOT NULL,
[TEN99TYPE] [smallint] NOT NULL,
[YEAR1] [smallint] NOT NULL,
[PERIODID] [smallint] NOT NULL,
[TEN99BOXNUMBER] [smallint] NOT NULL,
[TEN99AMNT] [numeric](19, 5) NOT NULL,
[TEN99FRNORUSDTL] [char](41) NOT NULL,
[TEN99STATECD] [char](3) NOT NULL,
[TEN99STATIDNUM] [char](21) NOT NULL,
[TEN99TAXEXMTCUSIPNUM] [char](13) NOT NULL,
[TEN99DIRSALECB] [tinyint] NOT NULL,
[TEN99STATNMBR] [char](21) NOT NULL,
[TEN99FATCAFILEREQ] [tinyint] NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
Does anyone know what could be causing this problem? Any help would be appreciated.
Thanks,
- Dave
*This post is locked for comments
Hello Manuel,
We are also having this issue for MEM. We updated to GP 2015 R2 and installed Build 14.00.0502s06 for MEM. What build did you install for MEM that fixed this issues? Was it Build 503s05?
Thank you for your help!
Claudia Orozco
Hello,
There's been a few comments made on this forum, so I wanted to add some information based on what I've seen here:
>>First, you won't lose your data if you recreate the table in PSTL, as long as you use these steps:
1. In the PSTL window, the 'Toolkit' utility is what you'll want to use.
2. In the Toolkit Options window, choose the 'Recreate SQL Objects' option and click Next.
3. In the System Diagnostics window, select the 'Financial' series and then select a table such as GL10110.
4. Still on this window, select the 'Recreate Selected Table' and also the 'Recreate Data for Selected Table(s)' in order to recreate the table with data intact.
5. Click on the 'Perform Selected Maintenance' to proceed. It will show some warning and confirmation boxes you'll need to click through. Once the table is re-created, you'll get a message letting you know that. Click OK.
>>In the few upgrade cases where we've seen the PM00204 / PM1099PeriodDtl error show, the main cause is that for some reason, the PM00204 table doesn't get updated with the new TEN99FATCAFILEREQ column.
If you resolve the issue by manually re-creating the PM00204 table with this missing column, make sure you also re-create the views, triggers, stored procedure and all related objects for this table, if you don't run it through the actual upgrade, as these objects need to know that there is a new column in the table, otherwise you may see errors such as "Number of results columns doesn't match table definition".
>>One potential cause of tables not getting updated with new columns during an upgrade, whether it be PM00204 or others, is if there are records in the DU000030 table held in the DYNAMICS/system database from a prior upgrade. If there are records in the DU000030 table from a prior upgrade, it can cause Utilities to think tables have already been upgraded and it won't try to upgrade the tables and you won't get any type of error until later in the upgrade when it attempts to create the new views, stored procedures, etc., and throws an 'invalid column' error because it's expecting new columns in the tables, but they're not there.
The recommendation is to make sure the DU000030 table is empty before starting ANY upgrade of the GP databases, whether it be a major version upgrade or just a service pack or hotfix upgrade.
Hopefully this helps!! Thanks all!!
Thank you John! We also came to the same conclusion after many inquires to Microsoft and Binary Stream. MEM was updated to fix the issue. Everything went smoothly after the update.
Hi Manuel,
You can try the SQL Maintenance (Microsoft Dynamics GP >> Maintenance >> SQL. You can perform the same steps as the PSTL but this allows you to drop and recreate the tables in one window.
Backup your table first by running the following SQL Script:
select * into PM00204_YYYYMMDD from PM00204
Just change the YYYYMMDD as the date.
Select the Company Database from the list >> Product would be Microsoft Dynamics GP >> highlight the Purchasing 1099 Period Detail.
Mark the Drop table and the Drop Auto-Procedure first to process then highlight the same table but mark the Create table and Create-Auto-Procedure as the 2nd process.
Once you have performed the above steps, re-insert the data back into the table (just make sure you change the YYYYMMDD).
insert into [dbo].[PM00204]
([VENDORID],
[TEN99TYPE],
[YEAR1],
[PERIODID],
[TEN99BOXNUMBER],
[TEN99AMNT],
[TEN99FRNORUSDTL],
[TEN99STATECD],
[TEN99STATIDNUM],
[TEN99TAXEXMTCUSIPNUM],
[TEN99DIRSALECB],
[TEN99STATNMBR],
[TEN99FATCAFILEREQ])
select ([VENDORID],
[TEN99TYPE],
[YEAR1],
[PERIODID],
[TEN99BOXNUMBER],
[TEN99AMNT],
[TEN99FRNORUSDTL],
[TEN99STATECD],
[TEN99STATIDNUM],
[TEN99TAXEXMTCUSIPNUM],
[TEN99DIRSALECB],
[TEN99STATNMBR],
[TEN99FATCAFILEREQ]
from PM00204_YYYYMMDD
I hope this helps.
Cheers,
Leh
Warning: If you recreate the table in PSTL you will lose your data.
I ran 'Recreate Dex procs for selected table' and everything is now working. I no longer get the error when printing the 1099 edit list or viewing 1099 Detail inquiry.
We contacted Binary Stream, the vendor for MEM, and they had a new version to accommodate the window change. If anyone is having this problem, I'm sure the issue is that you have a third-party that uses their own version of that window. Either you have not updated the third-party software, or the vendor has not accommodated the update. In our case, the update was available, but I just had not installed it.
John
As far as I know, only issues in Payables.
Fixed by recreating PM00204 using PSTL.
- FB
We just did the same upgrade , GP2010 => GP2013 with the service packs available on CustomerSource and got the same error. I will be contacting my dealer/vendor with the same questions on how to resolve this issue.
As far as anyone knows, is this error confined to the AP module ? I'd hate to try and run a Gl ,AR or PR posting and find out the same type of error is present.
Thanks
Andrew
Hi Manuel,
I experienced this same issue, and for me it was due to MEM's window not accommodating the update. I changed the window back to the Microsoft Dynamics GP window, and now we are all set. Our partner has opened a case with Binary Stream to alert them to the issue.
John
We are currently suffering an Unhandled script exception when opening the vendor details window. The error is: Illegal address for field '1099 FATCA Requirement' in script 'Set1099DetailFields'. We used the PSTL to recreate PM00204 with data recreating for selected table, without success. Anyone else have this issue after installing the Year End Update from MS?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (