SM 9.3 upgrade SQL compare utility result contains unknown field type

[Catherine Doce]

Dear Expertise,

I'm upgrading from SM 7.11 to SM 9.3. I ran SQL Compare Utility and added the new fields to dbdict tables. There is only field I can't added regarding its type. file name: rcenv, field name: tableAccessKETask, type: record.

I can't see the type record in the field types in dbdict when add new field.

How can I add this field?

Thanks for your help

[Greg_S]

I just ran the upgrade and it did it all for me. In fact, I found the SQL compare utility to not be of help.

Ensure that your database user has read/write access to the SYS.V_$ tables, otherwise the upgrade will fail.

In my upgrade process, the system created this table, and others for me.

Here is the code to get your table into the database:

ALTER TABLE RCENVM1 ADD

("DESCRIPTOR" BLOB  NULL,

"NAME" VARCHAR2(50)  NULL);

commit;

Good Luck,

Greg

[Catherine Doce]

Thanks Greg for your reply. I'll run the upgrade and see the result.

[Catherine Doce]

I ran the upgrade and get the following errors:

'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.dbdict)
file:(dbdict) key:(name=bizservice) (apm.upgrade.dbdict,save.dbdict)
ALTER TABLE BIZSERVICEM1 ADD "OS_NAME" VARCHAR(60)  NULL, ADD "OS_MANUFACTURER" VARCHAR(60)  NULL, ADD "OS_VERSION" VARCHAR(60)  NULL, ADD "BIOS_ID" VARCHAR(60)
  NULL, ADD "BIOS_MANUFACTURER" VARCHAR(60)  NULL, ADD "BIOS_MODEL" VARCHAR(60)  NULL, ADD "TOTAL_DISC" VARCHAR(60)  NULL, ADD "FREE_DISC" VARCHAR(60)  NULL, AD
D "PHYSICAL_MEM_TOTAL" VARCHAR(60)  NULL, ADD "VIRTUAL_MEM_TOTAL" VARCHAR(60)  NULL, ADD "MEM_PAGES_TOTAL" VARCHAR(60)  NULL, ADD "MEM_PAGES_SIZE" VARCHAR(60) 
NULL, ADD "DRIVERS" VARCHAR(60)  NULL, ADD "MACHINE_NAME" VARCHAR(60)  NULL, ADD "ADDLMACADDRESS" VARCHAR(60)  NULL, ADD "ADDLIPADDR" VARCHAR(60)  NULL, ADD "PO
RT_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 (apm.upgrade.dbdict,save.dbdict)
Cancelling dbdict update, failed to execute the following SQL: (apm.upgrade.dbdict,save.dbdict)
API=SQLExecute [in sqmssqlExecOne], Statement=ALTER TABLE BIZSERVICEM1 ADD "OS_NAME" VARCHAR(60)  NULL, ADD "OS_MANUFACTURER" VARCHAR(60)  NULL, ADD "OS_VERSION
" VARCHAR(60)  NULL, ADD "BIOS_ID" VARCHAR(60)  NULL, ADD "BIOS_MANUFACTURER" VARCHAR(60)  NULL, ADD "BIOS_MODEL" VARCHAR(60)  NULL, ADD "TOTAL_DISC" VARCHAR(60
)  NULL, ADD "FREE_DISC" VARCHAR(60)  NULL, ADD "PHYSICAL_MEM_TOTAL" VARCHAR(60)  NULL, ADD "VIRTUAL_MEM_TOTAL" VARCHAR(60)  NULL, ADD "MEM_PAGES_TOTAL" VARCHAR
(60)  NULL, ADD "MEM_PAGES_SIZE" VARCHAR(60)  NULL, ADD "DRIVERS" VARCHAR(60)  NULL, ADD "MACHINE_NAME" VARCHAR(60)  NULL, ADD "ADDLMACADDRESS" VARCHAR(60)  NUL
L, ADD "ADDLIPADDR" VARCHAR(60)  NULL, ADD "PORT_NUMBER" VARCHAR(60)  NULL, ADD "NOTIFICATION_GROUP" VARCHAR(60)  NULL, ADD "REMOTE_PHONE" VARCHAR(60)  NULL, AD
D "REMOTE_IP" VARCHAR(60)  NULL, ADD "DISASTER_RECOVERY" VARCHAR(60)  NULL, ADD "LOGIN_SERVER_NAME" VARCHAR(60)  NULL (apm.upgrade.dbdict,save.dbdict)
SQL State: 42000-8180  Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (apm.upgrade.dbdict,save.dbdict)
SQL State: 42000-156   Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'ADD'. (apm.upgrade.dbdict,save.dbdict)
Unrecoverable error in application:  sm.upgrade on panel call.service.pack
Unrecoverable error in application:  apm.upgrade.wizard on panel process.dbdict
Unrecoverable error in application:  apm.upgrade.dbdict on panel save.dbdict"

by the way i scaned the database for for errors and fixed manually all null values.

[Greg_S]

I had a similar problem, but for me it was the ComputerM1 table.
Do you have records in BizServiceM1? I'm guessing you do not.
I recommend you manually fix the table with an Alter Table command, then add a dummy record - that's what I had to do for ComputerM1.

[Greg_S]

I had a lot of problems with my upgrade from 7.11 to 9.30, but for me, it was ComputerM1 table.

Your error looks like it may also be related to ComputerM1, So I'm including files I used to fix. See attachments.

First I dropped ComputerM1. Then I ran the Create Table program.

Then I ran the Alter Table program that adds in new fields.

Finally, I stopped/restarted the app service, then went into the application and in database (db <CR>) found computer, and manually added a dummy record. Then restarted the upgrade - Success!

Also, here is what your BizServiceM1 table should look like:

create table BIZSERVICEM1 (
LOGICAL_NAME  VARCHAR2(200)
,PROBLEM_MANAGER  VARCHAR2(140)
,SYSMODTIME  DATE
,SYSMODCOUNT  FLOAT(126)
,SYSMODUSER  VARCHAR2(60)
,SERVICE_STATUS  VARCHAR2(30)
,SERVICE_DESCRIPTION  CLOB
,ALLOWSUBSCRIPTION  CHAR(1)
,CM3SUBSCRIPTIONADD  VARCHAR2(30)
,CM3SUBSCRIPTIONREMOVE  VARCHAR2(30)
);
commit;

[Greg_S]

Attachments in Zip file.

[Greg_S]

After I had successfully upgraded once, I did a database Refresh, and re-ran the upgrade process again, making notes so that the upgrade of Production goes smoothly.

[Piku]

Hi Catherine,

I suppose this is issue in base function RAD shipped OOB in SM.
Look in to some threads on forum regarding the same.


hth,

[Catherine Doce]

Thanks Greg for your reply, but I've already 72 records in bizservice table

[Greg_S]

I wonder what that table looks like. When you do a Describe on that table, what does the structure look like?

Because it may need Logical_name to be 200. If there was a problem with the alter table statement, it might not have updated the structure of the table properly.

[Catherine Doce]

Hi Greg,

I discovered that the error in the OOB system, not in the table, so I opened another discussion with that error.

the new title is "SM 9.3 upgrade OOB system error for SQL statement".

I hope that you can help me for that error.

thanks