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.

I have been working with a client on upgrading their PeopleSoft database from SQL Server 2000 to 2012.  Everything has gone great, except when we tried to setup a Linked Server back to their other SQL Server 2000 databases.  SQL Server 2012 uses the SQLNCLI-11 Native Client which has backwards compatibility for two release levels, so SQL Server 2008 and 2005 are supported by this client.  You can use the SQL Server 2008 Native Client SQLNCLI-10 which does support backwards compatibility to SQL Server 2005 and 2000.

When setting up the linked server using the SQLNCI-10 Native Client, it was failing with an odd error:

The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.

After a looking around I found this knowledge base document 906954 which explains that because of the SQL Server 2012 environment running on x64 and the SQL Server 2000 environment is running on x86 that there are some compatibility issues that require the SQL Server 2000 environment needs to be at SP3 or SP4 patch level.  PeopleSoft certification documents show that their support requires the SQL Server 2000 database to be at SP3a, and they do support SP4.

Here is a super handy link to get SQL Server Build information:

http://sqlserverbuilds.blogspot.ca/

Sometimes it is helpful to be able to rename a table in PeopleSoft. I find when dealing with duplicates in tables during upgrades, you can rename a table to a temporary table and then re-create the table in question and then simply do an insert into the new table from the temporary table using a select distinct statement.

SQL SERVER:
sp_rename ‘TABLENAME’, ‘NEWTABLENAME’

ORACLE:
alter table tablename rename to newtablename

Outer joins is just one of the items that is required from time to time, but since I typically never recommend using them with PeopleSoft, I always forget the syntax.  Since my days go back to Oracle 7, I remember the messy (+) notation and so I always end up looking it up.  The good news is Oracle superseded the (+) notation by going to ISO 99 standard as of Oracle 9i, however it still supports (+) notations.

There are three types of joins you can do, left outer, right outer and full outer join. In a left outer join, all the records of the table listed on the left will be returned with the combined results of the join to the table listed on the right, however, if the right listed table does not have a matching row, NULLS will be returned for the columns of the right listed table.  The right outer join does the same thing just in reverse, whereas the full outer join basically does a UNION of the left & right outer joins.

Some database platforms do not support full outer joins so you can basically do it by doing a left outer join combined with with a right outer join using the union statement.

SELECT A.NAME, D.DEPARTMENT FROM EMPLOYEE A LEFT OUTER JOIN DEPARTMENT D ON A.DEPTID = D.DEPTID;

If you want to do the Oracle (+) syntax:

SELECT A.NAME, D.DEPARTMENT FROM EMPLOYEE A, DEPARTMENT D WHERE A.DEPTID = E.DEPTID (+);