Kbase P79897: Errors 23000 and 37000 are displayed when updating SQL Server table with timestamp, identifier, imag
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
16/03/2010 |
|
Status: Verified
SYMPTOM(s):
Errors 23000 and 37000 are displayed when updating SQL Server table with timestamp, identifier, image, or text fields
23000:[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update a timestamp column
37000:[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update identity column 'rowid'
37000:[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared
Unable to update SQL Server table with timestamp, identifier, image, or text fields
Trying to update a MS SQL Server table
MS SQL Server table contains timestamp, identifier (identity), image, or text data type
FACT(s) (Environment):
MS SQL DataServer
Progress 9.1D
OpenEdge 10.0A
Windows
CAUSE:
Bug# OE00104076
CAUSE:
All columns in the table except the column defined as either timestamp, identity, image, or text participate in indices. When the schema holder is created, the first field which does not participate in an index is assigned to the _file record as a free field.
The DataServer sometimes uses this field in a set statement:
SET <field> = <field>
This is the statement which causes the problem. Those 4 field types should not be used by the utilities as a free field.
Fields which should not be used as a free fields are: timestamp, identity, image, and text. Currently, text fields are skipped so if a table has both timestamp and text fields that do not participate in indices, the timestamp is still selected.
FIX:
Upgrade to 9.1E or later.
OR
Upgrade to OpenEdge 10.0B or later.
Or
If upgrading isn't a viable option, a workaround is to add an integer field to the table. The integer field must be marked as ALLOW NULL since no data needs to be placed in the field. When updating a record it must be found with EXCLUSIVE-LOCK and the update will be allowed to go through. If the EXCLUSIVE-LOCK is not used then problems will occur with the buffers since the new integer field will be set to unknown and can not be compared.