SM 9.3 upgrade OOB system error for SQL statement

[Catherine Doce]

Dear Expirites,

I'm upgrading from sm 7.11 to sm 9.3 using update patch 3. i followed the upgrade guid and did all system configration and run system Scan and sql compare utility, but i got an error when run the upgrade utility:

"The record being added contains a NULL key (message,add.schedule)
file:(schedule) key:(schedule.id=) (message,add.schedule)
Key #7 is empty. (message,add.schedule)
The record being added contains a NULL key (message,add.schedule)
file:(schedule) key:(schedule.id=) (message,add.schedule)
Key #7 is empty. (message,add.schedule)
The record being added contains a NULL key (message,add.schedule)
file:(schedule) key:(schedule.id=) (message,add.schedule)
Key #7 is empty. (message,add.schedule)
An error occurred while attempting to update a record (apm.upgrade.dbdict,save.old.dbdict)
file:(dbdict) key:(name=computer) (apm.upgrade.dbdict,save.old.dbdict)
ALTER TABLE COMPUTERM1 ADD "SERVICE_STATUS" VARCHAR(60)  NULL, ADD "ALLOWSUBSCRIPTION" VARCHAR(60)  NULL, ADD "PROBLEM_MANAGER" VARCHAR(60)  NULL, ADD "CM3SUBSC
RIPTIONADD" VARCHAR(60)  NULL, ADD "CM3SUBSCRIPTIONREMOVE" VARCHAR(60)  NULL, ADD "PORT_NUMBER" VARCHAR(60)  NULL, ADD "NOTIFICATION_GROUP" VARCHAR(60)  NULL, A
DD "REMOTE_PHONE" VARCHAR(60)  NULL, ADD "REMOTE_IP" VARCHAR(60)  NULL, ADD "DISASTER_RECOVERY" VARCHAR(60)  NULL, ADD "LOGIN_SERVER_NAME" VARCHAR(60)  NULL, AD
D "FLASH_MEMORY" VARCHAR(60)  NULL, ADD "CTR6" VARCHAR(60)  NULL, ADD "TRY_TYPES" VARCHAR(60)  NULL, ADD "GIGA" VARCHAR(60)  NULL (apm.upgrade.dbdict,save.old.d
bdict)
Cancelling dbdict update, failed to execute the following SQL: (apm.upgrade.dbdict,save.old.dbdict)
API=SQLExecute [in sqmssqlExecOne], Statement=ALTER TABLE COMPUTERM1 ADD "SERVICE_STATUS" VARCHAR(60)  NULL, ADD "ALLOWSUBSCRIPTION" VARCHAR(60)  NULL, ADD "PRO
BLEM_MANAGER" VARCHAR(60)  NULL, ADD "CM3SUBSCRIPTIONADD" VARCHAR(60)  NULL, ADD "CM3SUBSCRIPTIONREMOVE" VARCHAR(60)  NULL, ADD "PORT_NUMBER" VARCHAR(60)  NULL,
 ADD "NOTIFICATION_GROUP" VARCHAR(60)  NULL, ADD "REMOTE_PHONE" VARCHAR(60)  NULL, ADD "REMOTE_IP" VARCHAR(60)  NULL, ADD "DISASTER_RECOVERY" VARCHAR(60)  NULL,
 ADD "LOGIN_SERVER_NAME" VARCHAR(60)  NULL, ADD "FLASH_MEMORY" VARCHAR(60)  NULL, ADD "CTR6" VARCHAR(60)  NULL, ADD "TRY_TYPES" VARCHAR(60)  NULL, ADD "GIGA" VA
RCHAR(60)  NULL (apm.upgrade.dbdict,save.old.dbdict)
SQL State: 42000-8180  Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (apm.upgrade.dbdict,save.old.dbdict)
SQL State: 42000-156   Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'ADD'. (apm.upgrade.dbdict,save.old.dbdict)"

i run the command from SQL Query to database directlt and give me syntax error, i tried to write correctly as

"ALTER TABLE COMPUTERM1
ADD "SERVICE_STATUS" VARCHAR(60)  NULL,
"ALLOWSUBSCRIPTION" VARCHAR(60)  NULL,
"PROBLEM_MANAGER" VARCHAR(60)  NULL,
"CM3SUBSCRIPTIONADD" VARCHAR(60)  NULL,
"CM3SUBSCRIPTIONREMOVE" VARCHAR(60)  NULL,
"PORT_NUMBER" VARCHAR(60)  NULL,
"NOTIFICATION_GROUP" VARCHAR(60)  NULL,
"REMOTE_PHONE" VARCHAR(60)  NULL,
"REMOTE_IP" VARCHAR(60)  NULL,
"DISASTER_RECOVERY" VARCHAR(60)  NULL,
"LOGIN_SERVER_NAME" VARCHAR(60)  NULL,
"FLASH_MEMORY" VARCHAR(60)  NULL,
"CTR6" VARCHAR(60)  NULL,
"TRY_TYPES" VARCHAR(60)  NULL,
"GIGA" VARCHAR(60)  NULL;"

but the sql gives me error

"Column names in each table must be unique. Column name 'PROBLEM_MANAGER' in table 'COMPUTERM1' is specified more than once"

is there a solution regarding this case?????

thanks for ur help

[Catherine Doce]

the SQL query gives first error

"Column names in each table must be unique. Column name 'SERVICE_STATUS' in table 'COMPUTERM1' is specified more than once."

[Greg_S]

Do a "desc computerm1" and please tell us what it shows.
Also, please do a "select * from computerm1" to see how many rows it returns. I wonder if it is 0.

I had this very problem on my computerm1 table, and I found that

1) my table was not defined properly, i.e., I needed to have logical_name as varchar2 (200) and

2) there were no records in it.
I found that after doing my alter table and after it was configured properly, I needed to add a dummy record. After that, I was able to restart the upgrade, and it proceeded to successful completion.

[Catherine Doce]

Thanks Greg for your concern.

Find the attached resell of desc and select statement.

Note: I'm using MS SQL 2005 not oracle, and this problem happened for bizservice table too before compturem1, and I'm sure it is for the customer fields that added to that tables. Also I'm sure this error will appear for every table I added a custom field to. I run the upgrade for a default SM database and the upgrade goes smoothly, but the errors appears here for the custom fields that added to the table.

[Greg_S]

Interesting: We had custom fields in some of our tables and the upgrade did not die on them. But it did have a problem with computerm1.

I see your logical_name in computerm1 table is still set to 80. I think that is the problem, because the upgrade expects it to be 200 characters wide.

We also have a WinServer OOB install. I've included a screenshot if the structure of the computerm1 table on that system.

[Greg_S]

Oh, and I see you already have records in the computerm1 table, so that won't be an issue.

Hope this helps.

[Greg_S]

I took a closer look at your computerm1 table and compared it against my Windows OOB SM 9.30 and found these column width changes:

LOGICAL_NAME        change from 80 to 200

OS_MANUFACTURER  change from 40 to 60

BIOS_MANUFACTURER  change from40 to 60

BIOS_MODEL                change from 40 to 50

I had exactly this same problem during my upgrade. Here is my code fix (Oracle specific):

ALTER TABLE COMPUTERM1 modify
("LOGICAL_NAME"            VARCHAR2(200),
"OS_MANUFACTURER"        VARCHAR2(60)  ,
"BIOS_MANUFACTURER"      VARCHAR2(60)  ,
"BIOS_MODEL"             VARCHAR2(50)  );
commit;

good luck.

-Greg

[Greg_S]

Update:

We ran into this problem again when we were upgrading our DEV system. We stopped and restarted the service, then applied the modify table SQL as above, reran upgrade, and it still failed.

We found that there was already one record in the computerm1 table, a dummy record that I had added previously. We deleted that record, then added in another dummy record, and restarted the upgrade process - and it ran successfully.

Because you have many records in your computerm1 table, you may wish to consider an unload / reload process either during or after the upgrade.