Kbase P63288: How to Modify tables to create PROGRESS_RECID in support of the ROWID function in 4GL with MS SQL se
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
21/06/2010 |
|
Status: Verified
GOAL:
How to Modify tables to create PROGRESS_RECID in support of the ROWID function in 4GL with MS SQL server data server?
GOAL:
How to manually create insert triggers to support progress_recid column?
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
MS SQL DataServer
FIX:
During migration, DataServer creates progress_recid and progress_recid_ident_ columns and builds insert triggers to support these columns. However, if tables are directly pulled in from SQL server, and these columns do not exist in tables, DataServer supports ROWID if tables have unique index. If tables do not have unique index, you can follow below steps to create columns and triggers to support ROWID.
Following are steps on how to create the PROGRESS_RECID column to support ROWID.
1. Add a column of the integer data type named PROGRESS_RECID. The new column must be able to contain null:
alter table table
add PROGRESS_RECID integer null
2. Add a column with identity characteristics named PROGRESS_RECID_IDENT_. The new column must have the integer data type:
alter table table
add PROGRESS_RECID_IDENT_ integer identity
3. Create a trigger to maintain the PROGRESS_RECID column:
create trigger _TI_table ON table for insert as
if ( select PROGRESS_RECID from inserted) is NULL
begin
update t set PROGRESS_RECID = i.IDENTITYCOL
from table 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:
create index table##index on table (column, PROGRESS_RECID)
5. If you have already created your schema holder, delete and recreate it.