Kbase P130361: Duplicate key row error when loading .d file in MS SQL Server
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
4/10/2008 |
|
Status: Unverified
FACT(s) (Environment):
MS SQL DataServer
OpenEdge 10.0x
Progress 9.1x
SYMPTOM(s):
Error when loading .d file in MS SQL Server
The following errors appear in the .e file:
ERROR READING LINE #6 (Offset=183): 23000:[Microsoft][SQL Server] Cannot insert duplicate key row in object 'dbo.TableName' with unique index 'TableName##IndexName'
ERROR READING LINE #6 (Offset=183): 01000:[Microsoft][SQL Server] The statement has been terminated
The unique Progress/OpenEdge primary index is composed of one field
Several records have the '?' value in the field
The same .d file can be loaded without any error in an ORACLE database.
CAUSE:
This is a MS SQL Server limit.
MS SQL Server restricts NULL values with the UNIQUE constraint. UNIQUE constraints allow for the value NULL. However, as with any value particiapting in a UNIQUE constraint, only one null value is allowed per column.
ORACLE removes the UNIQUE constraint on NULLs.
FIX:
In order to workaround this MS SQL Server limit, make the Progress/OpenEdge primary index not unique in MS SQL Server.
Before loading the .d file, from MS SQL Server, run the following queries:
- Drop the unique index:
DROP INDEX TableName##IndexName ON TableName
- Recreate the index non unique:
CREATE INDEX TableName##IndexName ON TableName (FieldName)
Otherwise, make sure that the field composing the unique primary index does not contain null values.