1

I have a stored procedure which uses IDENT_CURRENT to update last added value in a table.

The issue is:

IDENT_CURRENT returns a value that does not exist in the table. The number it returns is much higher than Max(ID) of the table. How is it possible and what can be done to fix it. I never wanted to use MAX(ID) to update the table.

Thanks.

Learner
  • 3,904
  • 6
  • 29
  • 44
  • 4
    `IDENT_CURRENT` returns the last `IDENTITY` **generated** even if that was for a row that was never inserted as the transaction was rolled back. Can you explain more about why you need to update the most recently inserted row? Do you want to update the one most recently inserted in your current session or globally? – Martin Smith May 27 '13 at 16:40
  • http://stackoverflow.com/questions/14445185/dbcc-checkident-reseed-is-new-value-required – David Brabant May 27 '13 at 16:42
  • The thing is I manually added just 1 row and the insert was successful. I am thinking where and how did the rollback happen. – Learner May 27 '13 at 16:43
  • We can't tell you when the rollback happened. Or whether there were some rows that were since deleted. – Martin Smith May 27 '13 at 16:46
  • I have a windows service which sends files to FTP and runs every few minutes. I need to store the value of last file sent which is based on the last ID. I need the one in my current session. – Learner May 27 '13 at 16:47
  • 2
    @Learner - So return `SCOPE_IDENTITY` after your `INSERT` statements or use the `OUTPUT` clause. – Martin Smith May 27 '13 at 16:48

1 Answers1

0

If you're sure that the table is updated only at the time, you can use IDENT_CURRENT ("Table") and get the last inserted id in that table.

:)

Leonardo Huarita
  • 1
  • 1