January 2, 2013

SCOPE_IDENTITY() and remote views in VFP

By Mike Lewis

In a recent Hex Central article, Lynn Evans recommended using the SCOPE_IDENTITY() function to find the primary key of the most recently inserted row in a SQL Server table. That function is a better choice than @@IDENTITY or IDENT_CURRENT(), because it is specific to the session and the scope.

As a Visual FoxPro developer, I often use SCOPE_IDENTITY() when inserting records via SQL Pass-Through (SPT). But I recently found that it doesn't work with remote views. Specifically, if you use a view to insert a row, then use SQLEXEC() to send a call to SCOPE_IDENTITY(), you always get back a NULL - even if the SPT call uses the same connection handle as the view. That's because the two operations occur in different scopes.