TestComplete: SQL: ADO.CreateADOCommand: After a period of run time- Object is no longer valid.

[jkrolczy]

I have a test script in which I am doing a series of SQL queries using ADO.

I would like to use these in a for - loop to go through a couple of servers in the same script.

I have them broken out into individual scripts currently ... at least 4 of the 18 so far.

Proving concept and hoping to work out this issue before going further.

One time through the code works, but on the next loop thru I eventually get:  Object is no longer valid.

To me, there seems to be maybe a timeout going on ???

Here is a sample piece of code of one of my queries.

var idx = 1;

var inum= 0;

// Data base operations
var RecSet, Cmd;

// Create a new object
Cmd = ADO.CreateADOCommand();

// Specify the connection string
Cmd.ConnectionString = "Provider="+Project.Variables.DBSqlServConnStrName+";Server="+transSvr[inum]+";Database=Domain1;Trusted_Connection=yes";

// Specify the command text (the SQL expression)
Cmd.CommandText = "SELECT * FROM Sites where Status = 0";
// Execute the command
RecSet = Cmd.Execute();
// Process the table records
RecSet.MoveFirst();
while (! RecSet.EOF)
{
siteID[idx] = RecSet.Fields("SiteID").Value;
// Log.Message(idx+" "+RecSet.Fields("SiteID").Value);
idx++;
RecSet.MoveNext();
};

// Log.Message(" ");
Log.Message("# of Active Sites: "+siteID.length);
// Log.Message(" ");

My script initially logged out a lot of the info from one SQL query part to another.

The log dumps were around ~500 rows.

As I took out most of the logging, the 5 SQL queries I performed worked and the script completed.

Again --- just for 1 loop, into the second loop, Object is no longer valid. will appear somewhere.

Which  leads me back to a timeout issue for the SQL queries ?

If this is the case, anyone know how to extend the timeout period or disable it for SQL: ADO.CreateADOCommand ?

[tristaanogre]

On what line do you get the "Object is no longer valid" error?

[AlexKaras]

Hi,

Another idea is that connections created by ADO objects are not closed in your code. And ADO has a limit to simultaneously opened connections (32 IIRIC). Try to explicitly close connection before switching to another server.

[tristaanogre]

---

@AlexKaras wrote:

Hi,

Another idea is that connections created by ADO objects are not closed in your code. And ADO has a limit to simultaneously opened connections (32 IIRIC). Try to explicitly close connection before switching to another server.

---

Ah!  I had forgotten about that.  I knew about he connections for the Microsoft JET used for CSV and Excel DDT, but yeah, that might be the limation there as well.  Connection could be closed and the objects should be set to "null" to allow for implicit garbage collection.

[jkrolczy]

I will try this approach and get back with reply asap.

[sonya_m]

Thanks for the collective effort in solving this!

@jkrolczy was this a success?

[jkrolczy]

I have played with the Implicit and Explicit Close ways for the Recordset and Cmd of ADO.

I have also tried to condense down my code (remove unnecessary code and logging) to not waste time.

My script continues to FAIL around 25 secs.

Some of this time is TestComplete run time ~5-10 secs.

Maybe let's go with this a different direction ... how can I get more time for the ADO connection.

It seems the intense SQL query going on along between 2 servers I am connecting to seems to need more time.

I understand what is being done for a Close if that worked, but the 'Object is no longer valid.' after

the script runs for the ~25 secs is the killer right now.

My work around right now is I have broken my for-loop going thru 18 different Production envs

out into their own script.

Now again from this - running each script 99% of the time each will run fine but the 1% of  'Object is no longer valid.' occurs to this timeout ? .  So rerunning the script again, it will usually complete.

I am also at the mercy of the Production DBs with a lot of data and how quickly it can handle my DB calls

Summarizing here:

  How to increase ADO timeout time ?

[BenoitB]

    var Command = ADO.CreateCommand();
    var Error   = "";
    Command.CommandText = Sql;
    Command.CommandTimeout = 120;  // Timeout of the connection in seconds
    Command.ActiveConnection = Connection;
    Command.Execute();
    ErrCount = Connection.errors.count;
    if (ErrCount !== 0) {
      for (Err = 0; Err == ErrCount; Err++){
        Error += Connection.errors.item + "\n";
      }
      Connection.Errors.Clear;
      throw Error;
    }

When an error occurs do you trace the stack of ADO error to look at the root cause ?

[jkrolczy]

Sorry,

I understand the principle here that this should work, but it did not for me.

I see a solution was accepted for this thread, but it is not a solution that solved this issue for me.

Thanks all for the info you supplied me.

I will continue to try and debug more on this issue or maybe even try to change up

my ADO coding structure to where maybe the CommandTimeout can be adjusted.

[sonya_m]

@jkrolczy the solution was unmarked!

What happened when you tried using the approach? We can continue the investigation here and see if the Community can come up with more useful suggestions!