We are in the process of upgrading FSCM 8.9 to SQL Server 2005 and
have hit very few problems. However, I thought it might be useful to
someone if I listed the few issues that we did have.
1. We had a Query expression which preserved the native SQL Server datetime format.
Replace....
CAST(A.DTTM_STAMP_SECas DATETIME(109))
with
CONVERT( DATETIME, A.DTTM_STAMP_SEC ,109)
2. In PeopleTools 8.1x PS Query did not support Outer Joins using the ANSI sql OUTER JOIN. Therefore Queries had to use the *= and =* notation embedded within expresssions. In SQL Server compatibility mode 90 this old style syntax is not supported. However, when you re-write your queries make sure you verify the results. One of the reasons *= was unpopular was that you could not differentiate between row selector criteria and join criteria as all predictes came after the WHERE clause. This sometimes gave undesired results. For example...
select COL1, COL2
from TABLE1 A, TABLE2 B
where A.COL1 *= B.COL1
and A.COL2 = 'something'
does not give the same results as
select COL1, COL2
from TABLE1 A
outer join TABLE2 B ON A.COL1 = B.COL1
where A.COL2 = 'something'
To obtain the same results as the original you would beed to add the row selector critiera and make it part of the join criteria. Like this
select COL1, COL2
from TABLE1 A
outer join TABLE2 B ON A.COL1 = B.COL1 and A.COL2 = 'something'
One of the side effects of rebuilding a query using the new OUTER JOIN syntax is that table alias names may change. If they do and the query is used in a Crystal report you must verify your Crystal report and remap any fields where the alias has changed.
3) Look out for NEW reserved words in SQL 2005. We had a few column aliases that are now reserved words in 2005.
Apart from these fairly trivial issues we are seeing some major benefits from moving SQL 2005. We're not live in production yet but will be by the end of March 2008.
Final note.... read and take note of the Oracle White Paper for SQL Server 2005 here. http://www.microsoft-oracle.com/assets/pdf/Oracle_whitepaper_FINAL.PDF
Comments