Borislav,

>>Rajesh,
>>
>>You should use scope_identity() instead of @@identity.
>>
>>>How to return the autoinc field from sql server.
>>>I think the @@identity should do the work but am confused with the insertrefreshcmd.
>
>I am not sure SCOPE_IDENTITY() will works, becuase when you refresh CA you already are out of scope :o)
>I saw in SQL Profiler that INSERT command and Refresh command are in two different Batches (tested with ODBC connection not with ADO). He must use IDENT_CURRENT('Table_name') if He use SQL Server 2005. But I think @@IDENTITY should work :o)

From SQL Server help: bold emphasis added by me

IDENT_CURRENT is similar to the Microsoft® SQL Server™ 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

using anything but scope_identity() gives you a high probability that you'll be getting someone else's key value in a system that isn't single user.

IDENT_CURRENT is bad because it can cross sessions (connections).

@@identity is bad because it can cross scopes. Say the insert to TableA invokes a trigger that inserts into TableB, you'll get the TableB identity value not the TableA identity. Additionally you may not know when in the future an insert trigger will be added to the table. So your code works fine for a while and someone doing something else a year from now causes your code to start throwing foreign key constraint errors if you are lucky or worse relating child records to the wrong parent record.

scope_identity must be called within the sp before any other inserts occur to another table with an identity.

SPs should be written to always immediately grab scope_identity() after the insert if that value is to be returned or used in any child table inserts.

insert into Parent ....
set @ParentId = scope_identity()

insert into Child ... values ( ... @ParentId ... )
set @ChildId = scope_identity()

insert into GrandChild ... values ( ... @ChildId ... )
set @GrandChildId = scope_identity()

-- return the values to the caller
select @ParentId as ParentKey, @ChildId as ChildKey, @GrandChildId as GrandChildId