I'm using VFP9 and I use remote views that back into SQLServer 2000.

What I'd like to be able to do is use SQLServer's scope_identity() function
to return the identity value generated upon inserting (and performing a
tableupdate()) a new record into an updatable remote view that backs into a
SQLServer table with an identity field as its primary key.

The problem I'm having is that scope_identity() returns null in this
insert into my_remote_view (field1) values ("blah")
tableupdate(0, .T., "my_remote_view")
Sqlexec(Cursorgetprop("ConnectHandle", "my_remote_view"), "select scope_identity() as LastIdentity", "ScopeIdentity")
ScopeIdentity.LastIdentity && this prints null

If I use sql pass-through (sqlexec()) to insert the record then there is no
problem, scope_identity() returns the identity value as expected.
If I use @@Identity instead of scope_identity() then an identity value is
returned, but since @@Identity is not 100% reliable this is of marginal

It seems like a remote view's update action has its own scope that ends once
the update is committed, while its session persists - hence @@Identity works
and scope_identity() does not.

I suppose I could try using a cursoradapter and alter the UpdateCmd property
to include code to use scope_identity(), but I don't really want to go down
this route since I'm working on a large application that makes extensive use
of remote views and only peripheral use of cursoradapters.

Has anyone found a way to use scope_identity() with a remote view

Thanks in advance.
Paul S. Elliott

Re: SQLServer's scope_identity() and VFP's remote views

Naomi Nosonovsky

Aleksey Tsingauz from VFP MS team showed a way to use SCOPE_IDENTITY() with CursorAdapter.

For Remote Views you may check http://fox.wikis.com/wc.dll Wiki~_MsdeVfp102_2

Re: SQLServer's scope_identity() and VFP's remote views


Thanks, that is interesting, but unfortunately the approaches mentioned either use @@Identity() or cursoradapters, neither of which are appropriate in my scenario.