Inserted Identity Values in SQL 2005

We have legacy code which uses C/C++ ADO to insert a record within a table
and return the value of its identity/key field using the @@Identity function.
The code has worked reliably for years using SQL 2000 as a data source. We
have upgraded to SQL 2005 (with Service Pack 2 installed) and the code still
works correctly on a development version of our database running under 2005.
The production version is identical except for being a subscriber within a
merge publication where the publication manages identity ranges for the
various published articles/tables.

When running the code with this production database, we experiece sporadic
failures in being able to retrieve the identity values of newly inserted
records. Code is shown below, note all tables contain a key/identity field
named 'ID' within our database and we have switched the compatibility level
of the DB to 80, since prior to this it would not work at all. Any help is
appreciated. Thanks. -David

bool InsertRecordReturnID(
_ConnectionPtr spCON, LPCTSTR strInsQuery,
LPCTSTR strTableName, LONG& nID)
{
_RecordsetPtr spRSInsert = NULL;
_RecordsetPtr spRSReturn = NULL;

try {
CString strQueryTxt;

CREATEiNSTANCE(spRSInsert,Recordset);
spRSInsert->CursorLocation = adUseServer;
spRSInsert->Open((LPCTSTR)strInsQuery,
_variant_t((IDispatch *)spCON,true),
adOpenStatic,adLockOptimistic,adCmdText);

// Retrieve recordset with RS pointer set to inserted record
CREATEiNSTANCE(spRSReturn,Recordset);
spRSReturn->CursorLocation = adUseServer;
strQueryTxt.Format("SELECT [ID] FROM [%s] WHERE [ID] = @@IDENTITY",
strTableName);
spRSReturn->Open(
(LPCTSTR)strQueryTxt,
_variant_t((IDispatch *)spCON,true),
adOpenStatic,adLockOptimistic,adCmdText);

//exception when using SQL 2005 database with merge sync
nID = spRSReturn->Fields->Item["ID"]->Value;

// Close record sets
if ((spRSInsert!=NULL)&&((spRSInsert->State)==adStateOpen))
spRSInsert->Close();
if ((spRSReturn!=NULL)&&((spRSReturn->State)==adStateOpen))
spRSReturn->Close();

return true;
}
catch (_com_error &e ) {
// Close record sets (if opened)
if ((spRSInsert!=NULL)&&((spRSInsert->State)==adStateOpen))
spRSInsert->Close();
if ((spRSReturn!=NULL)&&((spRSReturn->State)==adStateOpen))
spRSReturn->Close();
_bstr_t bstrSource(e.Source());
_bstr_t bs =
bstr_t(" Error: ") + _bstr_t(e.Error()) + _bstr_t(" Msg: ")
+ _bstr_t(e.ErrorMessage()) + _bstr_t(" Description: ")
+ _bstr_t(e.Description());
nID = 0;
return false;
}
} //end of InsertRecordReturnID



.

Loading