Kbase P62556: Error 2090 corrected by Creating Progress_recid in foreign data source
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
16/10/2008 |
|
Status: Verified
FACT(s) (Environment):
Progress 9.1B
Progress 9.1C
MS SQL DataServer
ODBC DataServer
SYMPTOM(s):
Record ID is larger than <max-recid>. (2090)
Error appears when accessing or inserting data into foreign database
Progress_recid is not created in the foreign database or not used in the index supporting recid
Can not upgrade to Progress 9.1D
Can not find an alternative unique index with a smaller number of key fields to represent the Progress RECID
CAUSE:
If you do not select the Progress RECID field in your Progress-to SQL Server Database conversion or do not have a Progress_recid field, then a unique index over each table is selected to support Progress RECID/ROWID functions. RECID functionality enables backward and forward scrolling in the DataServer for Microsoft SQL Server product.
The RECID buffer is used to store key information about the unique index selected to support RECID. The default RECID buffer size is 245 bytes. The space is used dynamically to allocate the RECID buffer for a given record. The RECID buffer size needs to be large enough to contain all the key information regarding the unique index. If the unique index selected is a composite of many columns and/or contains large column names, the RECID key information might exceed the RECID buffer size and issue an error message 2090.
FIX:
Create PROGRESS_RECID and PROGRESS_RECID_IDENT columns in the foreign data source to support recid.
1. Add a column of the integer data type named PROGRESS_RECID. The new column must be able to contain null:
alter table <table name> add PROGRESS_RECID integer null
2. Add a column with identity characteristics named PROGRESS_RECID_IDENT_. The new column must have the numeric data type:
alter table table add PROGRESS_RECID_IDENT numeric(10,0) identity
3. Create a trigger to maintain the PROGRESS_RECID column
CREATE TRIGGER _TI_<table name> ON <table name> FOR INSERT AS
IF ( SELECT PROGRESS_RECID FROM INSERTED ) IS NULL
BEGIN
UPDATE t SET PROGRESS_RECID = i.IDENTITYCOL
FROM <table name> t JOIN INSERTED i ON
t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_
SELECT CONVERT (int,@@identity)
END
4. Change the nonunique indexes so that they include a PROGRESS_RECID column as the last component.
5. Create a non-unique index on the progress_recid The syntax is:
CREATE INDEX <database>.<owner>.<table>#_#progress_recid ON
<table name>(PROGRESS_RECID)
6. A second index must be created for the progress_recid_ident field. This index must be created as unique and named <table name>#_#progress_recid_ident. The syntax is:
CREATE UNIQUE INDEX <table name>#_#progress_recid_ident_ ON
<table name>(PROGRESS_RECID_IDENT_)
7. If you have already created your schema holder, delete and recreate it.