scope_identity

Question

scope_identity

keradinides

Mr or Mrs. 500

Points: 557
More actions
May 11, 2004 at 4:56 am

#108244

Hi
I have an app in Gupta (Centura) working throu ODBC to SQL2000.
Currently works with the ident_current ('xxx') function to retrieve the last ID inserted to table 'xxx'.
I want to change it to scope_identity(), but it returns null.
The @@identity also works fine.
Any idea why this happening?
Thanks a lot

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

brendt hess Hall of Fame Points: 3256 More actions · Answer 1

It sounds as if the actual generation of the Identity is not in the scope of the call to SQL Server. For example (since I know nothing of the structure of the interface) assume a call to a stored procedure that verifies data, then makes a call to a second SP to perform the actual insert.

@@Identity would work, as long as the insert did not trigger creation of another identity (e.g. in a logging file)

ident_current(x) would work, since you are getting the latest identity for table X, but if someone else manages an insert between the two statements, you can get incorrect information.

scope_identity would not work, since the identity is created in another scope (another SP, in this case).

I agree with wanting to get away from ident_current, but it sounds as if you may actually need to use @@IDENTITY, not scope_identity.

keradinides Mr or Mrs. 500 Points: 557 More actions · Answer 2

My problem is that I can't use @@identity for the reason you mentioned.

There is a trigger for insert on table x, that insert a new row to another table.

Also the ident _current(x) gives me some times wrong results since we have over 20 users.

Scope _Identity would work because it gives me the latest id of the scope, to use for other tables inserting. But only throu Query analyzer, not throu ODBC.

Thanks for the reply

gaetano.lombardo Mr or Mrs. 500 Points: 549 More actions · Answer 3

I think the problem is that you probably use two separated statement (and so, using odbc, it means two scope) for the select scope_identity ().

Use a unique statement for the insert and the select scope_identity() something like Call OdrPrepareProc (hSql, "INSERT INTO MyTable (fld1) VALUES (:sFld1) SELECT scope_identity", "")

then SqlExecute,

sequentially OdrPrepareNextResults (hSql, ":nIntoScopeIdentity", bDummy)

then SqlExecute and SqlFetchNext....

Notice me if run well, and take an eye to centura Odr and Sql Function! FORTUNATELY I abandoned Centura

"...e il mio maestro mi insegnò com'è difficile trovare l'alba dentro l'imbrunire" F.Battiato - Prospettiva Nevsky

brendt hess Hall of Fame Points: 3256 More actions · Answer 4

Marco has a valid point. However, you probably should just embed the SQL call into a Stored procedure, and return the Identity from that SP, e.g.:

CREATE PROCEDURE usp_MyStoredProc

@Param1 int, @Param2 varchar(25), @param3 decimal(10,2)

AS

Set Nocount On

Insert Into MyTable (Fld1, Fld2, Fld3)

Values (@Param1, @Param2, @Param3)

SELECT Scope_identity()

GO