Just working on another upgrade today and ran into one of those gotcha’s that can make you scratch your head for hours.  The upgrade job failed trying to do a datamover script:

PeopleTools 8.53.05 – Data Mover
Copyright (c) 2013 PeopleSoft, Inc.
All Rights Reserved
Message Set Number: 0
Message Number: 0
Message Reason: Invalid User ID and password for signon. (0,0)
PSDMTX Error: signon

Funny thing is I can log into Application Designer, and I have the application server and process schedulers all running with the same user.  I check to make sure that my connect id still has the correct access and make sure that no accounts are locked, and everything looks great.  I even have PeopleSoft Administrator as one of my roles and still no dice.

If you have ever ventured to look at the PeopleTools Tab in the Permission List Component, you will see there is an option for Data Mover Access.  Make sure you have a permission list with this option turned on.  I have a custom permission list just for the upgrade and I added it to that, and bingo I was back in business.

Today, I was working on a PeopleTools upgrade for a client from 8.51 to 8.53.08 and ran into a failure when doing the Coverting PeopleTools Objects step of the upgrade.  When running the PeopleTools Data Conversion process I got a strange series of errors and then an error messaging that looked like UPGPTHASH failed. The messages appeared to be orphaned Application Engine PeopleCode, but the failure appeared to be with the Hashing.  I ran UPGPTHASH from the command line and it completed successfully but interestingly enough reported the invalid objects just like when PTUPGCONVERT ran it.

I decided to run a sysaudit on the database and found that I had PeopleCode-1, PeopleCode-2, PeopleCode-3, PeopleCode-6 and PeopleCode-7 errors in the database.  I will work on figuring out why they were there tomorrow but for today, I executed the steps from the data integrity guide from PeopleBooks to delete the invalid entries:

PeopleCode-1:

DELETE FROM PSPCMNAME WHERE NOT EXISTS (SELECT ‘X’FROM PSPCMPROG B WHERE B.OBJECTID1 = PSPCMNAME.OBJECTID1 AND B.OBJECTVALUE1 = PSPCMNAME.OBJECTVALUE1 AND B.OBJECTID2 = PSPCMNAME.OBJECTID2 AND B.OBJECTVALUE2 = PSPCMNAME.OBJECTVALUE2 AND B.OBJECTID3 = PSPCMNAME.OBJECTID3 AND B.OBJECTVALUE3 = PSPCMNAME.OBJECTVALUE3 AND B.OBJECTID4 = PSPCMNAME.OBJECTID4 AND B.OBJECTVALUE4 = PSPCMNAME.OBJECTVALUE4 AND B.OBJECTID5 = PSPCMNAME.OBJECTID5 AND B.OBJECTVALUE5 = PSPCMNAME.OBJECTVALUE5 AND B.OBJECTID6 = PSPCMNAME.OBJECTID6 AND B.OBJECTVALUE6 = PSPCMNAME.OBJECTVALUE6)

PeopleCode-2:

DELETE FROM PSPCMPROG WHERE NAMECOUNT <> 0 AND NOT EXISTS (SELECT ‘X’FROM PSPCMNAME B WHERE PSPCMPROG.OBJECTID1 = B.OBJECTID1 AND PSPCMPROG.OBJECTVALUE1 = B.OBJECTVALUE1 AND PSPCMPROG.OBJECTID2 = B.OBJECTID2 AND PSPCMPROG.OBJECTVALUE2 = B.OBJECTVALUE2 AND PSPCMPROG.OBJECTID3 = B.OBJECTID3 AND PSPCMPROG.OBJECTVALUE3 = B.OBJECTVALUE3 AND PSPCMPROG.OBJECTID4 = B.OBJECTID4 AND PSPCMPROG.OBJECTVALUE4 = B.OBJECTVALUE4 AND PSPCMPROG.OBJECTID5 = B.OBJECTID5 AND PSPCMPROG.OBJECTVALUE5 = B.OBJECTVALUE5 AND PSPCMPROG.OBJECTID6 = B.OBJECTID6 AND PSPCMPROG.OBJECTVALUE6 = B.OBJECTVALUE6)

PeopleCode-3:

UPDATE PSPCMPROG SET NAMECOUNT = (SELECT COUNT(*) FROM PSPCMNAME C WHERE C.OBJECTID1 = PSPCMPROG.OBJECTID1 AND C.OBJECTVALUE1 = PSPCMPROG.OBJECTVALUE1 AND C.OBJECTID2 = PSPCMPROG.OBJECTID2 AND C.OBJECTVALUE2 = PSPCMPROG.OBJECTVALUE2 AND C.OBJECTID3 = PSPCMPROG.OBJECTID3 AND C.OBJECTVALUE3 = PSPCMPROG.OBJECTVALUE3 AND C.OBJECTID4 = PSPCMPROG.OBJECTID4 AND C.OBJECTVALUE4 = PSPCMPROG.OBJECTVALUE4 AND C.OBJECTID5 = PSPCMPROG.OBJECTID5 AND C.OBJECTVALUE5 = PSPCMPROG.OBJECTVALUE5 AND C.OBJECTID6 = PSPCMPROG.OBJECTID6 AND C.OBJECTVALUE6 = PSPCMPROG.OBJECTVALUE6)

PeopleCode-4, PeopleCode-5, PeopleCode-6:

Open the PeopleCode program in Application Designer and correct the invalid reference

PeopleCode-7:

DELETE FROM PSPCMPROG WHERE OBJECTID1 = 104 AND OBJECTID2 = 107 AND OBJECTVALUE2 NOT IN (SELECT APPCLASSID FROM PSAPPCLASSDEFN P WHERE P.PACKAGEROOT = OBJECTVALUE1 AND P.APPCLASSID = OBJECTVALUE2)

Restarted the PTUPGCONVERT Step (F9) and the Conversion process ran without issue.

I also found several cases where it reported that UPGPTHASH will fail where the conversion process will fail because of a license code issue.  I can’t remember when PeopleSoft made the switch over but now when you install the software you are to use the license codes from PeopleSoft License Code Page. Often if your database has been around awhile the license code stored on PSOPTIONS.LICENSE_CODE will be different then whats on the License Code Page.  You can update the license code on PSOPTIONS, using SQL:  just note that the license code should be in lower case, no spaces, and no dashes.

I have been working on a couple of upgrades for some clients and have found that doing PeopleTools Upgrades to 8.53 from pre 8.48 that I have encountered an error while using SQL Server 2012 and the SQLNCLI11 native client during the upgrade.  The error occurs in the PTUPGCONVERT Application Engine while it is generating new Services for Integration Broker.

I reported the case to PeopleSoft but got nowhere with the problem.  After looking around with google, I found a common theme that made me try something that shouldn’t work but it did.  I changed the ODBC connection to the database to use the SQL Server 2008 Native Client SQLNCLI10.  I do not encounter this issue when I am running the upgrade on SQL Server 2008.  Once the ODBC is changed I reran the upgrade process and everything worked!

It reports a conversion error which appears to be a NULL gets converted to a long int 2097184 which it is trying to insert into a small integer field and therefore generated an error when it tries to insert the data into a table.

When doing an upgrade or any kind of patching you will often times want to compare the object you are importing with what is currently housed within your database.  PeopleSoft standard compare will generate a list of everything that is different between the data being imported and the data within the database.  You can get creative with these settings, but one thing that is SUPER helpful is to generate the output so that the compare reports can be viewed via the internet browser.

When in the compare options, if you select the Report Options tab you can select Generate Browser Reports, and the compare reports will be generated to the Report Output Directory which in the example is d:\temp\compares\{PROJECTNAME}.  If you want to change this directory you need to cancel out of the compare and going into the Tools Menu -> Options, and change the output directory.

After the compare is complete you will have a full set of output files in your output directory which you can put onto your PIA webservers public html document home directory:

%PS_CFG_HOME%/webserver/{domain}/applications/peoplesoft/PORTAL.war/

I typically make a compare folder here and insert the various compares there and then in the compare folder make a basic index page to link to the CompareViewer.html that was originally in the compare reports output folder root.

Lastly, you might find these reports don’t show up in the browser,  I find they are best viewed using Firefox, but IE works however, your security settings may cause you some issues.

I got a note from a client the other day saying that they were randomly sending out a lot of messages when Integration Broker failed to send a message.

It turned out that they had the Error Notification Integration Broker process turned on, which notifies everybody with the role APP_MSG_ADMINSITRATOR that an error has occurred. However, since this is workflow based if you look at the role under the security area, there is a workflow tab, and by default this role uses a query based workflow routing.  The base routing is Query: _ROLE_APP_MSG_ADMINISTRATOR, and if you dig into this you will find that everybody that has access to a web services (entry on PSAUTHWS table) will get notified.

In order to route the messages strictly to the administrators assigned to the role APP_MSG_ADMINISTRATOR, turn off the use query to route workflow in the role configuration.  PeopleTools -> Security ->Permissions & Roles -> Roles – tab WORKFLOW.