Kbase P127228: Error 1461 running application against SQL server after modifying SQL Server table
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
11/28/2007 |
|
Status: Unverified
FACT(s) (Environment):
MS SQL 2000
MS SQL DataServer
SYMPTOM(s):
Error 1461 occurs after applying update to application and SQL Server database
Schema holder does not match database schema - file OWNER.TABLENAME field <FIELDNAME> (1461)
Application deployed in the following manner:
Development has a SQL script to update SQL database, then built a new schema holder and recompiled the application.
The SQL script for SQL database, new .DF for the schema holder and the recompiled r code are shipped to customers sites.
Schema holder and r code from a working site are identical to that from a non-working site
Code works after schema holder is created using the Create DataServer Schema utility in Data Administration
Supplied .DF file contains information that is not included when the schema is pulled via the Create DataServer Schema utility
Dumping the schema of affected table from new schema holder and comparing it to the same table in the supplied .DF shows that the the FOREIGN-POS values are different
The order of the columns in SQL server for the table as in TABLENAME is different than the order of fields for the same table in the schema holder
CAUSE:
The developers inserted new fields into the SQL Server tables so that they were in the correct sequence and then updated their .DF file.
When the .SQL script was used to update the production SQL Server database, it updated the SQL Server table and appended the new fields to the end of the field-list.
The .DF contained the correct fields in the wrong order; it was correct for the development SQL Server database but not the production SQL Server database.
FIX:
1) Create a temporary schema holder using the Create DataServer Schema utility in Data Administration.
2) Dump the .DF of the affected table from temporary schema holder.
3) Compare the FOREIGN-POS values for the affected table in the .DF supplied by development to the FOREIGN-POS values for the affected table in .DF created from the temporary schema holder.
4) Update the FOREIGN-POS values in the .DF supplied by development with the FOREIGN-POS values from the .DF created from the SQL Server database; note that the FOREIGN-POS of the PROGRESS_RECID field is located in the ADD TABLE section of the .DF. For example, PROGRESS-RECID 8 where 8 is the FOREIGN-POS.
5) Delete the affected table from the schema holder via Data Dictionary.
6) Load the modified .DF for the affected table via Data Administration.