"Unknown file format for Excel file in dsLayout" when opening Data Entry form

Resolving The Problem

Fix:

Restore a copy of the original (before upgrade) database. Re-run the Database Conversion utility.

• Check to make sure that there were no errors (during the upgrade steps).

Workaround:

Re-run database conversion step 952 manually.

• The steps vary slightly, depending on whether you use Oracle or Microsoft SQL.

Oracle:

1. Ensure no users are using the system (downtime)

2. As a precaution, create a complete backup of your schema

3. Ask your Oracle administrator (DBA) to run the following script (on the 'bad' database schema):

DECLARE
sSql VARCHAR2(4000);
BEGIN
IF NOT UFNC_OBJECT_EXISTS('SELECT 1 FROM user_tab_COLUMNS WHERE table_name = ''XFDLAYOUT'' AND column_name = ''EXCELFILEFORMAT''') THEN
sSql := 'ALTER TABLE XFDLAYOUT ADD EXCELFILEFORMAT number DEFAULT (0) NOT NULL';
EXECUTE IMMEDIATE (sSql);

sSql := 'UPDATE XFDLAYOUT SET EXCELFILEFORMAT = 56';
EXECUTE IMMEDIATE (sSql);

sSql := 'UPDATE XFDLAYOUT SET EXCELFILEFORMAT = 51 WHERE UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(layout, 2,1)) = ''PK''';
EXECUTE IMMEDIATE (sSql);
END IF;
END;

DECLARE
sSql VARCHAR2(4000);
BEGIN
IF NOT UFNC_OBJECT_EXISTS('SELECT 1 FROM user_tab_COLUMNS WHERE table_name = ''XRCLAYOUT'' AND column_name = ''EXCELFILEFORMAT''') THEN
sSql := 'ALTER TABLE XRCLAYOUT ADD EXCELFILEFORMAT number DEFAULT (0) NOT NULL';
EXECUTE IMMEDIATE (sSql);

sSql := 'UPDATE XRCLAYOUT SET EXCELFILEFORMAT = 56';
EXECUTE IMMEDIATE (sSql);

sSql := 'UPDATE XRCLAYOUT SET EXCELFILEFORMAT = 51 WHERE UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(layout, 2,1)) = ''PK''';
EXECUTE IMMEDIATE (sSql);
END IF;
END;

1. Ensure no users are using the system (downtime)

2. As a precaution, create a complete backup of your database

• Ideally, perform this task on a 'test' database first, before trying it on the 'production' database

update xfdlayout set excelfileformat = 56
update xfdlayout set excelfileformat = 51 where cast(substring(layout,1,2) as char(2)) = 'PK'

update xrclayout set excelfileformat = 56
update xrclayout set excelfileformat = 51 where cast(substring(layout,1,2) as char(2)) = 'PK'

if not exists(select * from syscolumns Where id = (select id from sysobjects where name = 'xrclayout') and name = 'excelfileformat')
BEGIN
alter table xrclayout add excelfileformat int not null default 0;
EXEC ('update xrclayout set excelfileformat = 56');
EXEC ('update xrclayout set excelfileformat = 51 where cast(substring(layout,1,2) as char(2)) = ''PK''');
END

if not exists(select * from syscolumns Where id = (select id from sysobjects where name = 'xfdlayout') and name = 'excelfileformat')
BEGIN
alter table xfdlayout add excelfileformat int not null default 0;
EXEC ('update xfdlayout set excelfileformat = 56');
EXEC ('update xfdlayout set excelfileformat = 51 where cast(substring(layout,1,2) as char(2)) = ''PK''');
END