Progress
DataServer for
Microsoft SQL Server
Guide


Modifying Tables to Support the ROWID Function

If you want to use the ROWID function with a data source, you must make certain changes to your data source table. The following example procedure illustrates how to do this.

To use the ROWID function with a SQL Server™ database, make the following changes to your data source table:

  1. Add a column of the integer data type named PROGRESS_RECID. The new column must be able to contain null:
  2. alter table table
        add PROGRESS_RECID integer null 
    

  3. Add a column with identity characteristics named PROGRESS_RECID_IDENT_. The new column must have the numeric data type:
  4. alter table table
        add PROGRESS_RECID_IDENT numeric(10,0) identity 
    

  5. Create a trigger to maintain the PROGRESS_RECID column:
  6. create trigger _TI_table on table for insert as
    begin
        if (select max(inserted.PROGRESS_RECID) from inserted) is NULL
        begin
            update table set PROGRESS_RECID = @@identify
                where PROGRESS_RECID is null
            select convert (int, @@identity)
        end
    end 
    

  7. Change the nonunique indexes so that they include a PROGRESS_RECID column as the last component:
  8. create index table##index on table (column, PROGRESS_RECID) 
    

  9. If you have already created your schema holder, delete and recreate it.

Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095