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-valueSince 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.
Regards. Al
"Violence is the last refuge of the incompetent." -- Isaac Asimov
"Never let your sense of morals prevent you from doing what is right." -- Isaac Asimov
Neither a despot, nor a doormat, be
Every app wants to be a database app when it grows up
