IMO, there is not a reliable way to retrieve the IDENTITY value assigned in SQL Server 7.0. You could try to capture @@IDENTITY but be warned. If the table that you're inserting into (call it A) has an trigger that inserts a row into another that that also has an IDENTITY column (call it B), @@IDENTITY will contain the identity value for table B not table A.

There is a new function in SQL Server 2000 to work-around this issue.

-Mike