Kbase P139794: How does MS SQL server DataServer support ROWID Function?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
05/02/2009 |
|
Status: Verified
GOAL:
How does MS SQL server DataServer support ROWID function?
GOAL:
Under which conditions does MS SQL server DataServer support ROWID function?
GOAL:
How to modify tables and schema holder to have ROWID support?
GOAL:
How does MS SQL Server DataServer support RECID and ROWID functions?
FACT(s) (Environment):
Windows
MS SQL DataServer
FIX:
The ROWID value is not stored directly in the MS SQL Server data source, but it is represented by a unique index key in your database. If you migrated your database to a MS SQL Server and elected to use the Create RECID Field option during the migration, a unique 8-byte integer column named PROGRESS_RECID and a corresponding seed column named PROGRESS_RECID_IDENT will have been automatically generated in your database tables along with a corresponding Index containing the PROGRESS_RECID value as its key.
Databases that are only pulled from the native environment or are migrated without the Create RECID Field option must choose a unique index key from each table to represent the PROGRESS_RECID to support the RECID and ROWID functions, and forward and backward scrolling within their ABL sessions. ABL will internally map the selected unique key to the Progress RECID and ROWID functions.
The DataServer supports the ROWID function for MS SQL Server data source tables that have a unique index. The DataServer utilities use an index that meets this criterion to provide values for the ROWID function. If you build your schema holder using the OpenEdge DB to MS SQL Server utility and you select the Create RECID field option, it is automatically designated as the ROWID index. However, if you do not create the RECID field, you can select a unique index to support ROWID.
When you create or update a schema holder, the DataServer uses the following guidelines to select a column in a data source table to support the ROWID function:
? If the data source table has a PROGRESS_RECID column, the DataServer selects that column. A column of this type provides optimal support for the ROWID function; you cannot select an alternative to it.
? If the data source table does not have a PROGRESS_RECID column, the DataServer evaluates the available indexes and selects one according to the following criteria:
1. The index must be defined as unique.
2. The index must be defined as mandatory, or at least treated as such by your application.
The index selection process proceeds, according to the following order:
1. If you select an index, then you must select a qualified index.
2. If you do not select an index, the MS SQL Server DataServer selects a qualified index based on a single-component integer.
However, if the DataServer determines that more than one qualified index meets the additional criterion as a single-component integer, the DataServer selects the first index that fulfills the complete criteria.
3. If the MS SQL Server DataServer cannot find a qualified index based on a single-component integer, it seeks a qualified index that is multi-component and/or non-integer.
However, if the DataServer determines that more than one qualified index meets the additional criterion as multi-component and/or non-integer, the DataServer selects the first index that fulfills the complete criteria.
4. If the MS SQL Server DataServer cannot find a qualified index that is a multi-component and/or non-integer, the task cannot be completed and ROWID support is not provided. The table will not be able to uniquely identify any records. As a result, implementing FIND statements and performing queries could lead to inaccuracies in locating records.
Note: An index that you select as a ROWID must be defined as a unique index. It must also be mandatory, if not by definition, then by means of the application code.
To select an index to support the ROWID function, use the following procedure in the Data Dictionary with the schema holder connected (you do not have to connect to the MS SQL Server database).
To select an index to support the ROWID function:
1. Choose Tables mode.
2. Select the table whose ROWID you want to modify.
3.. Choose Table Properties.
4. Choose DataServer, a dialog which allows you to choose an index to support ROWID
5. Select one index. If the index is not unique and mandatory, you will get following warning
"This index is NOT Unique. The index contains field(s) that are not Mandatory. Your application has to take care of that.".