Hi! First post here, so please excuse me if I get any posting protocol wrong.

Running 2K(SP3) with Sql 2K(SP3a). Porting a single user application that was running against Access using ADO/OLEDB using the Microsoft.JET.OLEDB.4.0 provider. As far as we know, database-wise the application and ADO were working without a hitch.

Started porting the application's install program to use SQL server using 'sqloledb' as a provider. With a small amount of work, have all of the tables and stored procedures ported over, and using Query Analyzer and Enterprise Manager, everything seems great.

However (this is a question after all), we seem to have problems with the primary table, defined as such...

IsDeleted bit not null
DateCreated date not null default('getdate()')

Now, when I go to enter the "default state" objects into the database, I do the following sequence:

get object ready in memory
go to Objects table and fetch next id
set id into the Objects descended object
save the object

In order to grab the next id, I am trying to do the following (inside of a big try block, omitted for clarity) assuming m_pConnection is the connection object, already opened and verified:

// Allocate pointers
ADODB::_RecordsetPtr resultRecordSet = NULL;
ADODB::_CommandPtr localCommand = NULL;

// Create instances of the object
HRESULT hr = resultRecordSet.CreateInstance(__uuidof(ADODB::Rec ordset));
hr = localCommand.CreateInstance(__uuidof( ADODB::Command ));

// Set up the insert and execute it.
localCommand->ActiveConnection = m_pConnection;
localCommand->CommandText = "INSERT INTO Objects ( IsDeleted ) VALUES ( 0 );";
resultRecordSet = localCommand->Execute( NULL, NULL, ADODB::adCmdText );

// Set up the request to get the result
localCommand->CommandText = "SELECT SCOPE_IDENTITY() as col1;";
resultRecordSet = localCommand->Execute( NULL, NULL, ADODB::adCmdText );

// Get the results.
ADODB::FieldsPtr recordFields = resultRecordSet->GetFields();
ADODB::FieldPtr thisField = recordFields->GetItem( "col1" );
ADODB::DataTypeEnum dataType = thisField->GetType();
if( !resultRecordSet->EOF ) {
_variant_t fldVal = thisField->GetValue();
retCode = thisField->GetValue();

Now, when I do this, I get one of two behaviours. Most of the time, the result set thinks its at the EOF, and no records are in the result set. If it returns a valid result set, it gets through, but fldVal is set to VT_NULL, which doesn't help.

I have tried the above 2 queries in sequence in Query Analyzer, and it works great. We also tried an approach in out application with the ResultSet AddNew method, and we seem to have a similar problem getting the identity back out.

Any ideas? Things to check? Service packs that I need to install?