>VFP and ADO do not execute scope_identity() in SQL SERVER 2000.
>
>
>
>My code :
>
>

>
>PUBLIC oFa  as CursorAdapter
>
>oFa = CREATEOBJECT('_ca_familia')   && my CA classe save conection object in _screen.__sqlconexao property
>oFa.CursorFill()
> = CURSORSETPROP("Buffering",5,"familia")
>
>INSERT INTO familia (cd_familia,ds_familia) values([0001],[teste])
>llRet = TABLEUPDATE(.T.,.T.,"familia")
>
>IF llRet
>   LOCAL oRs AS 'ADODB.RecordSet'
>   LOCAL lnRet
>
>   oRs = CREATEOBJECT('ADODB.RecordSet')
>   oRs.CursorLocation = 2  && adUseServer
>   oRs.CursorType = 0  && adOpenForwardOnly
>   oRs.LockType = 1  && adLockReadOnly
>   oRs.Open("SELECT scope_identity() AS 'id'",_screen.__sqlconexao )
>   lnRet = oRs.Fields(0).value    && Return NULL.
>   oRs.Close()
>   oRs = NULL
>
>ENDIF
>
>

>
>TIA

This is a true issue.

When you send string with parameters,
the ADO/ODBC/OLE blablablabla and other tons of interfaces,
convert you command into a EXEC command ( use SQL Profiler )

Result, the EXEC open another batch, and then the SCOPE_IDENTITY()
at EXEC program level still unchanged.

Analyze this, uses SQL Analyzer:

-- Start a new session
-- This open a inner batch
EXECUTE ('insert into T1 DEFAULT VALUES')
-- next return NULL,newIdentity
SELECT scope_identity(),@@identity
GO

-- This open a inner batch
EXEC sp_executesql N'insert into T1 DEFAULT VALUES'
-- next return NULL,newIdentity
SELECT scope_identity(),@@identity
GO

-- This open a inner batch
-- and return newIdentity,newIdentity
EXEC sp_executesql N'insert into T1 DEFAULT VALUES SELECT scope_identity(),@@identity'
GO

insert into T1 DEFAULT VALUES 
-- return newIdentity,newIdentity
SELECT scope_identity(),@@identity

Fabio