C++ programmaticall y reconnection to SQL Server 2000 after stop/restart SQL service

[Emanuele]

I have write a program using MS Visual studio C++ 7.0 (platform Windows XP professional). I'm not using .NET.
This program save data in a SQL server 2000 database using ADO.
Everything works correctly, yet
if i stop the sql service and then restart it, while the programs write records, the reconnection to the server dont't work.

When the service stops i catch all the exceptions. I want try to reconnect to the database continually till the service restart and the connection reestablish.

I use connection, recordset and command object to execute an SQL statement (UPDATE MyTable SET filed1=value1 etc..

After the record write failure i call the method to close the connection. Even if the service is stopped this function don't generate an exception. Later when i call the ReconnectDataba se function i detect that the connection state is down and i try to reconnect to the database.

The reconnection will be reestablish correctly.

If i call BeginTransactio n/RollbackTransac tion/CommitTransacti on when i write the record i don't succed to close the connection after the record writing failure. The Close() function generate an exception. If i read the connection state it is ON even if it is not true.
I have tried to rewrite my ReconnectDataba se function but i don't succed to reconnect to the database even if the service has been restared.

This is the code to write the record:

 
    try
    {
        pConnenction->BeginTrans();
    }
    catch(_com_error &e) 
    {
        .......        
    }
 
 
    // CADOCommand is my wrapper class around ADO object
    CADOCommand cmd(pDatabase, cmdString, adCmdText);
    if (myRecordset.Execute(&cmd) == FALSE) {
 
        try
        {    
            pConnection->RollbackTrans();
        }
        catch(_com_error &e) 
        {
             .........
        }
 
        // When the service is stop this function generate an exception only if i 
        // have previously call  BeginTrans/Rollback.
        // I can catch this exception but my ReconnectionDatabase() function 
        // (see later) don't succed to reconnect to the database
        rsMachines.GetActiveConnection()->Close();
 
        return FALSE;
 
    }
 
    try
    {
        pConnection->CommitTransaction();
    }
    catch(_com_error &e) 
    {
        ...........
    }
 
 

This is the function that i use to try to reconnect to the database:

BOOL CDbase::ReconnectDatabase(_ConnectionPtr pConnectionPtr)
{
    long state;
    _bstr_t strConnection;
 
    state = pConnectionPtr->GetState(); 
 
    if (state == 0) {    
 
        strConnection = (_bstr_t) pDatabase->GetConnectionString();
 
 
        pConnectionPtr->CursorLocation = ADODB::adUseClient;
 
        try {
 
                 pConnectionPtr->Open(strConnection,_bstr_t(L""),_bstr_t 
                                                            (L""),ADODB::adModeUnknown);
        }
        catch(_com_error& e) {
 
 
                return FALSE;
        }
    }
 
    return TRUE;
}
 

I have tryed to close/open the database
I have tryed to delete and new all the object and then reopen the database.
If the sql service has been stopped and then restarted I only have exception.
Only if i don't use recovery the reconnection restart but i need recovery.


I have the same problem even when i disconnect/reconnect the network cable using a remote database.

Thanks for every kind of suggestion.

Emanuele.