MS SSMA fails on two rhythmyx tables

Rhythmyx Technical Discussion
1

We have been using the MS SSMA to migrate our rhythmyx repository to MS SQL Server 2005 and only 2 of 180 tables have failed !

RXMENUVISIBILITY.PK_RXMENUVISIBILITY
PSX_PERSISTEDPROPERTYVALUES

Has anyone already managed to migrate these two tables manually ?

2

NOTE after running MS SSMA you will need to reconfigure the rhythmyx data connection using the GUI tool

$ ./RhythmyxServerPropertiesEditor.bin

NOTE that the RhythmyxServerPropertiesEditor.bin includes a hardcoded link to the path where you originally installed rhythmyx so you’ll need to create a symlink to the SAME path if you have migratrated the rhythmyx tree as well !

3

FYI we’re now going to try a different approach (instead of using MS SSMA)

  1. install/create a clean rhythmyx repository on ms sql server 2005
  2. use a php script to copy data from old db on top of clean rhythmyx repository
4

we have now managed to migrate

PSX_PERSISTEDPROPERTYVALUES
RXMENUVISIBILITY.PK_RXMENUVISIBILITY

by using SQL Developer Version 1.5.0.52 EA2 to export the data including CLOBs into a SQL script and executing it using MS SQL Server 2005 Management Studio

5

We have just noticed another issue with using MS SSMA to migrate the rhythmyx repository the default datatype mapping that MS SSMA uses :

VARCHAR2 -> VARCHAR instead of NVARCHAR
CLOB -> VARCHAR(max) instead of NTEXT

APP VENDER ORIG ORACLE table

CREATE TABLE “PSX_PERSISTEDPROPERTYVALUES”

( “CONTEXT” VARCHAR2(25),
“USERNAME” VARCHAR2(100),
“CATEGORY” VARCHAR2(25),
“PROPERTYNAME” VARCHAR2(50),
“PROPERTYVALUE” CLOB

APP VENDER ORIG MSSQL table

CREATE TABLE [PSX_PERSISTEDPROPERTYVALUES](
[CONTEXT] nvarchar NOT NULL,
[USERNAME] nvarchar NOT NULL,
[CATEGORY] nvarchar NOT NULL,
[PROPERTYNAME] nvarchar NOT NULL,
[PROPERTYVALUE] [ntext] NOT NULL,

MS SSMA MIGRATED table

note VARCHARs instead of NVARCHARs and NTEXTs

CREATE TABLE [dbo].[PSX_PERSISTEDPROPERTYVALUES](
[CONTEXT] varchar NOT NULL,
[USERNAME] varchar NOT NULL,
[CATEGORY] varchar NOT NULL,
[PROPERTYNAME] varchar NOT NULL,
[PROPERTYVALUE] varchar NOT NULL,