Hi,
SQL 2K offers 3 identity retrieving functions: @@IDENTITY, IDENT_CURRENT, and SCOPE_IDENTITY
My data-handling objects use VFP cursors and TableUpdate() to save records - one at a time. If the record is new - I want to retrieve the identity value for the new record. I have been using @@identity but it is useless if you have cascading-insert triggers on the table you insert into.
I'd use IDENT_CURRENT, but it is useless because it is not session-specific (I'd like to know what they were thinking on this one)
so ... I am left with SCOPE_IDENTITY, which at first, seems to fit the bill - works great in SPT - however - the TableUpdate() function in VFP uses the SQL SP_ExecuteSQL stored procedure which is it's own "scope". Therefore - after the tableupdate insert, my scope is "done" and a SCOPE_IDENTITY call returns null.
Any ideas how I can get back the identity from SQL for my original "inserted into" table when using tableupdate?
TIA,
Ken B. Matson
GCom2 Solutions