MS SQL Dataserver returns an error related to the PROGRESS_RECID column when creating a new record.

This article discusses how to address the following error "The column Progress_recid cannot be modified because it is either a computed column or the result of a union operator"

Jan 13, 2016
Knowledge

Title

URL Name

MS-SQL-Dataserver-returns-an-error-related-to-the-PROGRESS-RECID-column-when-creating-a-new-record

Article Number

000182489

Environment

Product: OpenEdge DataServer for MS SQL Server Version: 11.x OS: Windows

Question/Problem Description

MS SQL Dataserver returns an error related to the PROGRESS_RECID column when creating a new record.

The column Progress_recid cannot be modified because it is either a computed column or the result of a union operator

Steps to Reproduce

Clarifying Information

PROGRESS_RECID is defined as a computed column on SQL Server.

Table definition on SQL Server reads:

CREATE TABLE [dbo].[customer](
...
   [PROGRESS_RECID] AS (case when [PROGRESS_RECID_ALT_] IS NULL then [PROGRESS_RECID_IDENT_] else [PROGRESS_RECID_ALT_] end) PERSISTED NOT NULL,
   [PROGRESS_RECID_IDENT_] [bigint] IDENTITY(1,1) NOT NULL,
   [PROGRESS_RECID_ALT_] [bigint] NULL DEFAULT (NULL),
CONSTRAINT [hisent#_#progress_recid] UNIQUE NONCLUSTERED
(
   [PROGRESS_RECID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Table definition on the MS SQL DataServer reads:

ADD TABLE "hisent" TYPE MSS
AREA "Schema Area"
DUMP-NAME "customer"
FOREIGN-NAME "customer"
FOREIGN-OWNER "dbo"
FOREIGN-TYPE "TABLE"
PROGRESS-RECID 14
INDEX-FREE-FLD 10
FILE-MISC15 2
QUALIFIER "msssports"
HIDDEN-FLDS "15,"
RECID-FLD-NAME "PROGRESS_RECID"
FILE-MISC25 "customer#_#progress_recid"
...

Error Message

37000 [Microsoft][ODBC SQL Server Driver][SQL Server] The column Progress_recid cannot be modified because it is either a computed column or the result of a union operator 37000 [Microsoft][ODBC SQL Server Driver][SQL Server] Statement could not be prepared

Defect Number

Enhancement Number

Cause

The Dataserver schema definition for the PROGRESS_RECID column does not match the PROGRESS_RECID definition in SQL Server.

In the above DataServer table definition, the HIDDEN-FLDS value is incorrect:

HIDDEN-FLDS "15,"

If the PROGRESS_RECID is a computed column, HIDDEN-FLDS should refer to PROGRESS_RECID, PROGRESS_RECID_IDENT_ and the PROGRESS_RECID_ALT_ fields. None of these fields are described in DataServer logical database schema:

HIDDEN-FLDS "14,15,16,"

Where:

14 is the internal column id for PROGRESS_RECID
15 is the internal column id for PROGRESS_RECID_IDENT
16 is the internal column id for PROGRESS_RECID_ALT_

There is also a reduntant field

FILE-MISC25 "<table name>#_#progress_recid"

This field should not be present if PROGRESS_RECID is a computed column, otherwise you would get the following error during a create operation: Invalid cursor state

Note that for the trigger-based PROGRESS_RECID definition, the HIDDEN-FLDS would refer to PROGRESS_RECID, PROGRESS_RECID_IDENT_. Only the the computed column PROGRESS_RECID definition includes PROGRESS_RECID_ALT.

HIDDEN-FLDS "14,15"

Where:

14 is the internal column id for PROGRESS_RECID
15 is the internal column id for PROGRESS_RECID_IDENT

Resolution

Please make sure you are using the right version of the df that match your SQL server schema definitions.

You may have to recreate the DataServer schema from the MS SQL Server definition.

Workaround

Notes

Keyword Phrase

Last Modified Date

1/13/2016 12:09 PM

MS SQL Dataserver returns an error related to the PROGRESS_RECID column when creating a new record.

Files(0)

Was this article helpful?

Was this article helpful?

MS SQL Dataserver returns an error related to the PROGRESS_RECID column when creating a new record.

Information

Related Articles