Kbase P106954: FOR EACH query does not retrieve records from the MS SQL Server Database when EXCLUSIVE-LOCK is used
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
17/07/2008 |
|
Status: Unverified
FACT(s) (Environment):
MS SQL DataServer
SYMPTOM(s):
FOR EACH query does not retrieve records from the MS SQL Server Database when EXCLUSIVE-LOCK is used
The schema holder was created by pulling the Database schema from the SQL Server Database
The SQL Server database is newly created for the development environment
The SQL Server Database table contains only 3 fields/columns
The table has only one index composing an ID field (non-unique/Foreign Key) and SQL Server timestamp field
CAUSE:
This is a SQL Server Database design issue that could work well with Progress MS SQL Server DataServer product. The Progress SQL Server DataServer splits timestamp filed into two different fields in the data dictionary - Date portion of the timestamp field maps to Progress Date field, and the time portion of it maps to a character data type. The character fields only contains minute and second portion but not the milliseconds (the SQL Server database, on the other hand, has the millisecond). Therefore, when a query is submitted with EXCLUSIVE-LOCK, it can not place an EXCLUSIVE-LOCK on the non-unique index since the millisecond portion of the timestamp field makes the data unique.
FIX:
Create another field with int(integer) or bigint data type and make it identity type with increment by 1. This field then can be used as an index.