Odd behavoir with @@identity...not getting correct value

[Brendan Sullivan]

We are running MS SQL Server 7.0

Target table has single trigger to maintain update time stamp.

We have many copies of the same database schema running on one server, all with unique database names. Such as Test_DB, Development_DB, etc.

This problem only occurs in one database, that was created using a 'restore' of another database.

Problem only happens in a single stored procedure.

Problem:
delete all rows from table (empty table)
Runn dbcc checkident ( 'T_BATCHES', reseed, 0)
Run procedure which does the following:
- Begin transaction
- Insert row into table
- Look at identity value (@@identity) - its value is 2 (*** THIS IS WRONG **)
- Select identity column from table - its value is 1
- Commit transation

The @@identity value being reported is one greater than the actual value in the database.

This does not occur if I do the same steps outside of the stored procedure.
Also, this same code and DDL run in other databases.

Help!

Thanks.

- Brendan

[JC Moore]

I had a similar problem once.
When I accessed @@IDENTITY after inserting a row, I received an inaccurate value. This was caused because there was a trigger on my table that accessed a different record than the one I inserted. @@IDENTITY was updated to a new value when the trigger accessed the other record. In my situation, I re-wrote the trigger. This may not be a feasible solution for you.




------------
Brendan Sullivan at 3/22/01 10:32:50 AM

We are running MS SQL Server 7.0

Target table has single trigger to maintain update time stamp.

We have many copies of the same database schema running on one server, all with unique database names. Such as Test_DB, Development_DB, etc.

This problem only occurs in one database, that was created using a 'restore' of another database.

Problem only happens in a single stored procedure.

Problem:
delete all rows from table (empty table)
Runn dbcc checkident ( 'T_BATCHES', reseed, 0)
Run procedure which does the following:
- Begin transaction
- Insert row into table
- Look at identity value (@@identity) - its value is 2 (*** THIS IS WRONG **)
- Select identity column from table - its value is 1
- Commit transation

The @@identity value being reported is one greater than the actual value in the database.

This does not occur if I do the same steps outside of the stored procedure.
Also, this same code and DDL run in other databases.

Help!

Thanks.

- Brendan

Update:
I removed all triggers from the offending table and the problem still occurs.

Thanks for the suggestion JC.
- Brendan




------------
JC Moore at 3/22/01 11:13:44 AM

I had a similar problem once.
When I accessed @@IDENTITY after inserting a row, I received an inaccurate value. This was caused because there was a trigger on my table that accessed a different record than the one I inserted. @@IDENTITY was updated to a new value when the trigger accessed the other record. In my situation, I re-wrote the trigger. This may not be a feasible solution for you.




------------
Brendan Sullivan at 3/22/01 10:32:50 AM

We are running MS SQL Server 7.0

Target table has single trigger to maintain update time stamp.

We have many copies of the same database schema running on one server, all with unique database names. Such as Test_DB, Development_DB, etc.

This problem only occurs in one database, that was created using a 'restore' of another database.

Problem only happens in a single stored procedure.

Problem:
delete all rows from table (empty table)
Runn dbcc checkident ( 'T_BATCHES', reseed, 0)
Run procedure which does the following:
- Begin transaction
- Insert row into table
- Look at identity value (@@identity) - its value is 2 (*** THIS IS WRONG **)
- Select identity column from table - its value is 1
- Commit transation

The @@identity value being reported is one greater than the actual value in the database.

This does not occur if I do the same steps outside of the stored procedure.
Also, this same code and DDL run in other databases.

Help!

Thanks.

- Brendan

[Dale Shaw]

Hi

It was suggested in a previous thread that you check / rebuild the indexes on the table.

Dale

[Brendan Sullivan]

------------
Brendan Sullivan at 3/22/01 10:32:50 AM

We are running MS SQL Server 7.0

Target table has single trigger to maintain update time stamp.

We have many copies of the same database schema running on one server, all with unique database names. Such as Test_DB, Development_DB, etc.

This problem only occurs in one database, that was created using a 'restore' of another database.

Problem only happens in a single stored procedure.

Problem:
delete all rows from table (empty table)
Runn dbcc checkident ( 'T_BATCHES', reseed, 0)
Run procedure which does the following:
- Begin transaction
- Insert row into table
- Look at identity value (@@identity) - its value is 2 (*** THIS IS WRONG **)
- Select identity column from table - its value is 1
- Commit transation

The @@identity value being reported is one greater than the actual value in the database.

This does not occur if I do the same steps outside of the stored procedure.
Also, this same code and DDL run in other databases.

Help!

Thanks.

- Brendan



Thanks for all the help.

Dale Shaw - your idea led me down the right path.

Solution: We had bad foreign keys setup on the offending table. Basically we had a self referencing foreign key on the identity column - that was dumb! I'm not sure how this FK got into our schema, but it did. Once it was removed all worked great.

Thaks again.
- Brendan