>I'm working on a project inserting data into SQL Server 2005 (SugarCRM) via ODBC and SQLEXEC() etc.
>
>SugarCRM uses GUIDs as primary keys. They're not autogenerated, Sugar's business layer calls NewID() on inserts. If I'm inserting rows programmatically I need to get the primary key values back.
>
>One way to do this is by calling SCOPE_IDENTITY, @@IDENTITY etc. after the INSERT. But, a quick Google found a corner-case bug with these functions that is apparently not fixed even in SQL 2008:
>
>http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value
>
>Since this bug seems to crop up in parallel execution, larger installations may be more vulnerable than small ones based on SQL Server Express.
>
>While on the topic, is it possible to execute a stored procedure via ODBC and have it return an output that can be read in VFP? This seems to be the recommended approach.

Hi Al,

In SQL Server 2005 and up the recommended approach is to use OUTPUT clause of the Insert.

GUIDs and scope_identity() are two different things. You only get newly inserted ID for identity fields with scope_identity() function.

If you have GUID generated with NewID() as a default value, then the only way to get them back would be using new OUTPUT clause of the INSERT command in SQL Server 2005 and up.