GP2013 R2 service pack fails

(0) Share

Report

Posted on by Richard Wheeler

75,848 Moderator

We are trying to do an application of GP 2013 R2 to GP 2013(12.00.1482). On two of the companies it fails at the very end with the message "Failed to create stored procedure uprCreateTrxDataFromTimesheets" and then "Invalid column name PAYRCORD" Now it only fails on the two older companies that had been created using GP 10 and then were upgraded. They do not use Project Accounting or PDK Timesheets. Does anyone have an idea of how to get past this? I have checked the DU000010, 20 and 30 tables and cleared away a bogus PRODID of 2. Any other tables I can clear?

*This post is locked for comments

All responses (11)

Answers (0)

Suggested answer

Rob Klaproth 1,730 on at

Like (0)

Report

RE: GP2013 R2 service pack fails

I logged a case with Microsoft regarding this. In most cases, an easier fix then installing HR or messing with your system is to just drop and re-create the offending tables. Here's a potential fix:

Check these tables to see if the offending column exists:

Select PAYRCORD from TATM0130
Select PAYRCORD from UPR00400

Select PAYRCORD from TATX1030

In my case, the column was missing from TATM0130 and TATX1030 tables.

Next, check the tables to see if there is any data:

Select count(*) from TATM0130

Select count(*) from UPR00400

Select count(*) from TATX1030

In my case, there was NO data in any of the tables.

Lastly, re-create the TATM0130 and TATX1030 scripts to the column is there by using this script:

/*Begin_TATM0130*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TATM0130]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TATM0130]
GO

CREATE TABLE [dbo].[TATM0130] (
[TIMECODE_I] [char] (7) NOT NULL ,
[DSCRIPTN] [char] (31) NOT NULL ,
[INACTIVE] [tinyint] NOT NULL ,
[TIMETYPE_I] [smallint] NOT NULL ,
[PAYRCORD] [char] (7) NOT NULL ,
[PAYTYPE] [smallint] NOT NULL ,
[SENIORITY_I] [char] (7) NOT NULL ,
[OVERTIMECODE_I] [char] (7) NOT NULL ,
[HOURSOVERTIME_I] [int] NOT NULL ,
[BSPAYRCD] [char] (7) NOT NULL ,
[PAYPEROD] [smallint] NOT NULL ,
[WRNTIMEFALLSBLWZERO] [tinyint] NOT NULL ,
[ACCRUETIMEBENEFITS_I] [tinyint] NOT NULL ,
[GBTABEN] [smallint] NOT NULL ,
[CHANGEBY_I] [char] (15) NOT NULL ,
[CHANGEDATE_I] [datetime] NOT NULL ,
[NOTESINDEX_I] [numeric](19, 5) NOT NULL ,
[DEX_ROW_ID] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PKTATM0130] PRIMARY KEY CLUSTERED
(
[TIMECODE_I]
) ON [PRIMARY] ,
CHECK (datepart(hour,[CHANGEDATE_I])=(0) AND datepart(minute,[CHANGEDATE_I])=(0) AND datepart(second,[CHANGEDATE_I])=(0) AND datepart(millisecond,[CHANGEDATE_I])=(0))
) ON [PRIMARY]
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATM0130].[ACCRUETIMEBENEFITS_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[TATM0130].[BSPAYRCD]'
GO

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[TATM0130].[CHANGEBY_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_DATE]', N'[TATM0130].[CHANGEDATE_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[TATM0130].[DSCRIPTN]'
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATM0130].[GBTABEN]'
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATM0130].[HOURSOVERTIME_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATM0130].[INACTIVE]'
GO

EXEC sp_bindefault N'[dbo].[GPS_MONEY]', N'[TATM0130].[NOTESINDEX_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[TATM0130].[OVERTIMECODE_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATM0130].[PAYPEROD]'
GO

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[TATM0130].[PAYRCORD]'
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATM0130].[PAYTYPE]'
GO

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[TATM0130].[SENIORITY_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[TATM0130].[TIMECODE_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATM0130].[TIMETYPE_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATM0130].[WRNTIMEFALLSBLWZERO]'
GO

setuser
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[TATM0130] TO [DYNGRP]
GO

/*End_TATM0130*/
/*Begin_TATX1030*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TATX1030]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TATX1030]
GO

CREATE TABLE [dbo].[TATX1030] (
[TRX_I] [int] NOT NULL ,
[EMPID_I] [char] (15) NOT NULL ,
[JOBTITLE] [char] (7) NOT NULL ,
[DEPRTMNT] [char] (7) NOT NULL ,
[DIVISIONCODE_I] [char] (7) NOT NULL ,
[TIMECODE_I] [char] (7) NOT NULL ,
[TRXDATE] [datetime] NOT NULL ,
[TRXENDDT] [datetime] NOT NULL ,
[HOURS_I] [int] NOT NULL ,
[HOURSAVAILABLE_I] [int] NOT NULL ,
[DAYSWRDK] [int] NOT NULL ,
[WKSWRKD] [int] NOT NULL ,
[ACCFLG] [tinyint] NOT NULL ,
[ACCRUETIMEBENEFITS_I] [tinyint] NOT NULL ,
[POSTED] [tinyint] NOT NULL ,
[ATTTYP] [char] (31) NOT NULL ,
[ATTRSN] [char] (31) NOT NULL ,
[ATTTRXSTATUS] [smallint] NOT NULL ,
[BACHNUMB] [char] (15) NOT NULL ,
[COMPTRNM] [int] NOT NULL ,
[CMRECNUM] [numeric](19, 5) NOT NULL ,
[PAYRCORD] [char] (7) NOT NULL ,
[CHANGEBY_I] [char] (15) NOT NULL ,
[CHANGEDATE_I] [datetime] NOT NULL ,
[CHANGETIME_I] [datetime] NOT NULL ,
[NOTESINDEX_I] [numeric](19, 5) NOT NULL ,
[DEX_ROW_ID] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PKTATX1030] PRIMARY KEY CLUSTERED
(
[TRX_I]
) ON [PRIMARY] ,
CHECK (datepart(hour,[CHANGEDATE_I])=(0) AND datepart(minute,[CHANGEDATE_I])=(0) AND datepart(second,[CHANGEDATE_I])=(0) AND datepart(millisecond,[CHANGEDATE_I])=(0)),
CHECK (datepart(day,[CHANGETIME_I])=(1) AND datepart(month,[CHANGETIME_I])=(1) AND datepart(year,[CHANGETIME_I])=(1900)),
CHECK (datepart(hour,[TRXDATE])=(0) AND datepart(minute,[TRXDATE])=(0) AND datepart(second,[TRXDATE])=(0) AND datepart(millisecond,[TRXDATE])=(0)),
CHECK (datepart(hour,[TRXENDDT])=(0) AND datepart(minute,[TRXENDDT])=(0) AND datepart(second,[TRXENDDT])=(0) AND datepart(millisecond,[TRXENDDT])=(0))
) ON [PRIMARY]
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATX1030].[ACCFLG]'
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATX1030].[ACCRUETIMEBENEFITS_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[TATX1030].[ATTRSN]'
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATX1030].[ATTTRXSTATUS]'
GO

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[TATX1030].[ATTTYP]'
GO

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[TATX1030].[BACHNUMB]'
GO

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[TATX1030].[CHANGEBY_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_DATE]', N'[TATX1030].[CHANGEDATE_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_DATE]', N'[TATX1030].[CHANGETIME_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_MONEY]', N'[TATX1030].[CMRECNUM]'
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATX1030].[COMPTRNM]'
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATX1030].[DAYSWRDK]'
GO

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[TATX1030].[DEPRTMNT]'
GO

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[TATX1030].[DIVISIONCODE_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[TATX1030].[EMPID_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATX1030].[HOURSAVAILABLE_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATX1030].[HOURS_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[TATX1030].[JOBTITLE]'
GO

EXEC sp_bindefault N'[dbo].[GPS_MONEY]', N'[TATX1030].[NOTESINDEX_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[TATX1030].[PAYRCORD]'
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATX1030].[POSTED]'
GO

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[TATX1030].[TIMECODE_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_DATE]', N'[TATX1030].[TRXDATE]'
GO

EXEC sp_bindefault N'[dbo].[GPS_DATE]', N'[TATX1030].[TRXENDDT]'
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATX1030].[TRX_I]'
GO

EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[TATX1030].[WKSWRKD]'
GO

setuser
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[TATX1030] TO [DYNGRP]
GO

/*End_TATX1030*/
Derek Albaugh on at

Like (0)

Report

RE: GP2013 R2 service pack fails

For the two company databases failing on Project Accounting because that module is still showing at GP 9.0 even though you're currently upgrading to GP 2015 R2, you could use these scripts to reset the system tables so that, when you launch GP 2015 R2 Utilities to upgrade these two databases, it won't attempt to upgrade Project Accounting but instead install it anew at the GP 2015 R2, while upgrading all other modules as normal, so that PA will be available in the new version if needed.

The scripts I'm referring to are here:

>For Project Accounting, use these scripts:

Against the DYNAMICS system database:

1. Delete DYNAMICS..DB_Upgrade where PRODID = 258 and db_name = 'CompanyA'

2. Delete DYNAMICS..DU000020 where PRODID = 258 and companyID = #

3. Delete DYNAMICS..DU000030 where PRODID = 258 and companyID = #

Against these two company databases, run this script:

4. Drop Table PA10702

You'll need to replace CompanyA in script #1 with the actual db_name value for the company database(s) as well as replace # in scripts #2 and #3 with the actual CMPANYID value for the database from the DYNAMICS..SY01500 table.

If you're getting the error about "Invalid column name PAYRCORD", this error is usually seen with the uprCreateTrxDataFromTimecards procedure being created and this column should be in the following tables: UPR00400, TATM0130 and TATX1030, but usually is found to not be in either TATM0130 and/or TATX1030, both of which are tables from the HR (414) module, because HR was once installed in the past, but is no longer and these tables exist but don't have this newer column.

Normally, I can get past this error just by dropping and re-creating these two HR tables with the PAYRCORD column, without needing to actually install HR if it isn't being used by the customer.

Let me know if you have any questions or further issues...….

Thanks
Rob Klaproth 1,730 on at

Like (0)

Report

RE: GP2013 R2 service pack fails

Getting same error when going from 2010SP4 to 2015R2. It's only happening in 2 of the companies that appear to have an orphaned project accounting installed. Their 2 companies with PA show PA being on version 9.0.259 for PRODID 258 (Project Accounting), but all the other products are on 11.0.x. I am wondering if I strip out the PA tables if that will help, but maybe installing HR is easier. The question no one seemed to answer here is do I have to roll back the upgrade and install HR in 2010, or can I install HR in 2015 and re-run utilities on the failed companies?
Suggested answer

Elizabeth Hodge 162 on at

Like (1)

Report

RE: GP2013 R2 service pack fails

Thank you. Running a 2010 to 2018 upgrade for a client and ran into this problem at 1 am during the first phase of upgrading from 2010 to 2015. Allowed me to get through that step. Thanks to Jonathan for the original drop table info too.
Community Member on at

Like (0)

Report

RE: GP2013 R2 service pack fails

I have the exact same issue with a customer who had HR from GP 8 in their databases. I followed the removal method as given above and I was able to then install HR in GP 2010 SP4 and now on the 2015 jump that has HR enabled gives errors on all the HR tables and won't convert. What steps after the above scripts? Remove HR again somehow after the reinstall.

Vince Stefanetti

Exact MAX Consultant
Suggested answer

Brian P Connell 859 on at

Like (1)

Report

RE: GP2013 R2 service pack fails

i don't see a response that this actually resolved the issue...so I'll put my 2 cents in....

-- Upgrading 4 company DB's from 11.0.2351 to 14.0.0804. 3 of them used payroll.

-- the first 3 failed and the non-payroll company succeeded. It should also be noted the 3 companies have been around since 7.5, the 4th company was added in GP2010.

-- the failures were the same as this long error in dex_log

--Read through a bunch of tech pages including this one and decided to add HR to the install.

--Upon launching the utils after the HR mod dyn.set addition.... I received the Product 414 does not support upgrading from version 7.5. Problem for me was there were no 414 entries in db_upgrade, du table, or anywhere that I could find.

-- I ran the drop scripts that Jon Fear suggested after a successful install here ( before attempting upgrade again).

drop table DYNAMICS..SUSPREF

drop table DYNAMICS..HR2APP06

drop table DYNAMICS..AAX10130

(for each company)

Drop Table INTERIDx..HR2NJ01

Drop Table INTERIDx..EX010130

(if you don't know what interid is, you shouldn't be doing the upgrade. call someone who knows. )

After that the utilities successfully got past the "product 414 does not support upgrading..... message and the companies successfully updated.

Now....do I leave the HR module installed?...hmmm.......

/Brian
Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

RE: GP2013 R2 service pack fails

Hi Richard,

Where you able to resolve the issue ? the status of the questions is still 'unanswered' ... so it would be good to mark it as resolved :-) if it's the case.

Have a great time.
Jonathan Fear on at

Like (2)

Report

RE: GP2013 R2 service pack fails

Delete DYNAMICS..DB_Upgrade where PRODID = 414 and db_name = 'xxxx'

Delete DYNAMICS..DU000020 where PRODID = 414 and companyID = xx

Delete DYNAMICS..DU000030 where PRODID = 414 and companyID = xx

If you are reinstalling for Dynamics you have to drop the following tables:

SUSPREF

HR2APP06

AAX10130

You can find the Companyid by running the following script (you need to use this same script for Dynamics if you are reinstall this module for Dynamics as well):

select CMPANYID,CMPNYNAM,INTERID from Dynamics..SY01500

Last you need to run the following drop scripts against each company you need to reinstall the module for:

Drop Table HR2NJ01

Drop Table EX010130
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

RE: GP2013 R2 service pack fails

Thanks Jonathan. After reading this I found that the issue was fixed by installing HR and running GP Utilities on just these two companies. Once it recreated all the HR tables with the correct structures the upgrade completed successfully. Now I would like to remove all HR tables from these two companies so future upgrades will proceed normally. Do you happen to have script that will drop all HR tables and Views?
Jonathan Fear on at

Like (0)

Report

RE: GP2013 R2 service pack fails

There are a large number of tables that use this column Richard:

AHPSLH01

AHRTA330

APR_APR50100

APR_APR70501

APR_APR70510

APR_APR70511

APR00400

APR00401

APR00402

APR00403

APR40600

APR41100

APR41101

gpUPRCPR

HRPppc01

HRPRED01

HRPSLH01

ORM_UPR_SETP_OT_DTL

ORM_UPR00400

ORM_UPR10203

ORM_UPR40600

SF010115

SF010200

SF010501

SF010502

SVC00930

SVC06001

TATM0130

TATX1030

UPR00400

UPR00400_OT

UPR00402

UPR10203

UPR19900

UPR40600

UPR40600_OT

UPR41901

Now with that being said one of them is the TATX1030 table which you are referencing as well so let's start there. I would first check and see if there is data in this table and if there is not drop the table, recreate it using another company that has successfully upgraded and then update the failed record in the DU000030 table to a status of 0.

If there is data in the table them you can fix the table scripting the data into a backup table, recreate the table at that prior version and then delete the failed record in the DU000030 table and have GP Utilities try the conversion again or you can recreate before you start the upgrade.

Just a side note it doesn't look like this table has ever been changed since GP 9 so my guess is this issue has been actually around on this environment for quite some time.