Kbase P18789: SQL Dataserver - Unable to update identity field.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
13/12/2007 |
|
Status: Unverified
FACT(s) (Environment):
Progress 9.1D
MS SQL DataServer
SYMPTOM(s):
Unable to update identity field.
2300: [Microsoft][ODBC SQL Server Driver][SQLServer] Cannot insert explicit value for identity column in table 'customer' when IDENTITY_INSERT is set to off
CAUSE:
IDENTITY_INSERT is set to off
FIX:
Change SQL Server table structure to have PROGRESS_RECID and PROGRESS_RECID_IDENT_ fields which are suported by SQL Dataserver.
The following sequence of commands will drop the identity field and create
the PROGRESS_RECID* fields.
1. DROP INDEX Customer.old_Ident_index;
2. ALTER TABLE Customer DROP COLUMN old_ident_field;
3. ALTER TABLE Customer add PROGRESS_RECID integer null;
4. ALTER TABLE Customer add PROGRESS_RECID_IDENT_ numeric(10,0) identity;
5. CREATE INDEX [Customer#_#progress_recid] ON [Customer]([PROGRESS_RECID]) ON [PRIMARY];
6.
create trigger _TI_customer on custmer 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;
7. Update SQL dataserver schema for Customer table only.