Skip to Content
0
Former Member
Jan 06, 2006 at 03:21 PM

Identity Column in SQL Server 2000

23 Views

I am working on SBO Version 2004a.

I created <b>Identity Column</b> in User Defined Table and made the entries for this Table and its columns in Sap Business One OUTB and CUFD tables respectivily.

I use this identity column to populate the Code and name fields automatically using Insert triggers.

I add 10 records to the table and now delete the 5th record. The Identity column and the code field will contain values as (1,2,3,4,6,7,8,9,10)

Now on Upgrading the patch level( SAP Business One 2004A (6.70.190) SP: 00 PL: 36), the Identity column is converted to simple INT column and no Identity column property is attached to it.

There is no T-SQL in SQL 2000 to convert an existing column to Identity Column hence I can not convert it back to Identity Column.

The Only Solution is to drop the column and re-create it as an Identity Column.

If I do so, the Identity column is polulated for all the rows starting from 1 to 9 (seed is 1).

Now on adding the next record, the primary key voilation occurs on the Code field since the next available Identity column value is 10 but this value is already used in 9th record.

<b>Is there any way so that the patch do not change the definition of the User defined tables and their columns.</b>

Thanks

Vishal Nigam