Consultor Eletrônico

Status: Unverified

FACT(s) (Environment):

MS SQL DataServer
ODBC DataServer

SYMPTOM(s):

Getting set of errors when loading .d into SQL Server

23000:[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

Duplicate unique key in database table (1443)

Load of database contents completed.
Error/Warnings listed in .e files placed into same directory as .d files

CAUSE:

This occurs when the table within SQL Server has an identity column and records are trying to be inserted into that column. Before you can insert information into that column, you must create PROGRESS_RECID and PROGRESS_RECID_IDENT_ columns within SQL Server.

FIX:

To correct the issue you must do the following;

1. Add an integer column named PROGRESS_RECID
alter table customer add PROGRESS_RECID integer null

2. Add an numeric column named PROGRESS_RECID with identity characteristics
alter table customer add PROGRESS_RECID_IDENT_ numeric (10,0) identity

3. Create a trigger to maintain the PROGRESS_RECID column
create trigger _TI_customer on customer for insert as
begin
if (select max(inserted.PROGRESS_RECID) from inserted) is NULL
begin
update customer set PROGRESS_RECID = @@identity
where PROGRESS_RECID is null
select convert (int, @@identity)
end
end;

4. Change the nonunique index so that it includes PROGRESS_RECID column as the last component
create index customer##newindex on customer (name, PROGRESS_RECID)

5. Delete all data within the table
delete from customer

6. Repull the schema into the schema holder, perform Update/Add Table Definitions

7. Load the .d again.