Identity Seed Problem

[tapks]

Hi!

I am facing a very piculiar problem while using MS SQL Server 7 which is as follows.

I have a table having an Identity Column. Through stored procedure I am inserting the data into it & while the record is getting created I want to use the Identity seed generated. The procedure was working fine for some time. But after some days I found that even though it creates the record with the identity sheet, it is not returning the lat identity value i.e. through function @@identity.

Is have checked the database, tables etc through DBCC & also done Update statistics. But the same problem persists.

When I am using the same procedure on the same table of a different database it is working fine.

Can anybody helps me out at the earliest.

Thankx in advance

TapKs

 

[NathanGriffiths]

Hi tapks,

I'm not sure why you're getting this error but have you tried using:

DBCC CHECKIDENT ('table_name', NORESEED)

This returns a report of the current identity value of the table and what the identity value should be. If they are not the same and the actual identity value is lower than what it should be you can fix this using:

DBCC CHECKIDENT ('table_name', RESEED)

If this isn't the problem, you could try not using @@IDENTITY and replacing it with IDENT_CURRENT('table_name') [SQL Server 2000 only!]. @@IDENTITY returns the last inserted identity in any table in the current session, but IDENT_CURRENT returns the last inserted identity of a specific table regardless of session or scope.

Hope that helps,

Nathan

 

[tapks]

Hi Nathan!

As I am using SQL 7, the 1st option os OK. But the table is generating the identity sheed while inserting any record into it. My problem comes when I want to use the same identity sheed to do some calculation in the same stored procedure. Through @@identity function I want to captue the same & use it for calculation. Actually it is not capturing the identity sheed value.

Can you pl help me out.

TapKs

 

[NathanGriffiths]

The value of @@IDENTITY might be returned incorrectly if an INSERT or SELECT INTO statement fails, or if a transaction is rolled back. The @@IDENTITY value is not re-set if either of these situations occur.

You might want to check that all the inserts and transactions performed by your procedure are working successfully, including those performed by any triggers that may exist on the tables.

If you could post the code for your procedure it would be useful!

Nathan

 

[tapks]

Hi Nathan!

I have checked that it is inserting the record by generating the identity key. But after that when I use the @@identity function to know the identity value it is returning nothing.

The same code was working fine for sometime. But I was facing the problem 3 /4 days back.

I am also posting my code below :

CREATE PROCEDURE [InsMstBlock]
@pBlockName varchar(50),
@pErr int = null output
AS
declare
@intBlockId int

begin /* Start */

insert into Mast_Block (Block_Name, Modified_On)
values (@pBlockName, gaedate())

set @intBlockId = @@identity
set @pErr = @@error

select @pErr, @intBlockId
return

end /* Start */

Pl help me out.

I know there was no problem in the procedure. May be some problem with the table or with the database.

 

[NathanGriffiths]

Your procedure seems fine (apart from "gaedate()" which i'm assuming is a typo)

If this procedure is returning nothing (zero?) for the identity value then the problem probably lies with the table or database.

Other things to check;

The identity field on the insert table is still present (sounds stupid I know but...)

There are no triggers set on the table which perform an insert on another table with no identity field. This would cause @@IDENTITY to be 0.

There is no other process in the same session performing inserts at the same time that might cause @@IDENTITY to be 0

 

[redgate]

Hi

I had this problem using SQL 2000

Try put

SET NOCOUNT ON

/*
Your Procedure Code
*/

SET NOCOUNT OFF

Hope this works