TestComplete does not wait for SQL query results before performing next step

[elesniewski]

I have TestComplete step that queries SQL server to run a procedure.  I have scripted the query to monitor the status of the procedure and return when complete.  I have run this successfully in TestComplete using an Indicator message that will show the status of the SQL procedure, but it now skips monitoring the sql procedure on the first run through of the TestComplete test. I have to re-run the TestComplete test to successfully pause while monitoring the sql procedure before moving to the next step. I cannot understand this behavior, each time I have to run TestComplete twice in order to have the Indicator show the status of the SQL job before moving to the next step.  Here is the code from TestComplete and the SQL procedure/job:

USE [CMS]
GO

/****** Object:  StoredProcedure [dbo].[Monitor_PopulateQueSys]    Script Date: 9/25/2018 2:08:40 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[Monitor_PopulateQueSys] 
AS
DECLARE @JobName NVARCHAR(128) = N'Verus_PopulateQueueSystem'
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE
@jobID UNIQUEIDENTIFIER,
@maxID INT,
@status INT,
@rc INT
SELECT @jobID = job_id
FROM msdb..sysjobs
WHERE name = @JobName
SELECT @maxID = MAX(instance_id)
FROM msdb..sysjobhistory
WHERE job_id = @jobID
AND step_id = 0
SET
@maxID = COALESCE(@maxID, -1)
RAISERROR ( 'Starting Job: %s', 0, 1, @JobName) WITH NOWAIT
EXEC @rc = msdb..sp_start_job @job_name = @JobName
WHILE (SELECT MAX(instance_id) FROM msdb..sysjobhistory WHERE job_id = @jobID AND step_id = 0) = @maxID
BEGIN
RAISERROR ( 'Wait for job to finish', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:01'
END
SELECT @maxID = MAX(instance_id)
FROM msdb..sysjobhistory
WHERE job_id = @jobID
AND step_id = 0
SELECT @status = run_status
FROM msdb..sysjobhistory
WHERE instance_id = @maxID
IF @Status <> 1
RAISERROR ( N'Job failed', 15, 1) WITH NOWAIT
ELSE
RAISERROR ( 'Job succeeded', 0, 1) WITH NOWAIT
GO

And here is how it is called from TestComplete:

function populateQueue() 
{
  // Step 13
  var AConnection, RecSet, Cmd, cmdTxt;
  var userID = KeywordTests.Main.Variables.getID.Value("Login");
  var pWord = KeywordTests.Main.Variables.getID.Value("Password");
  // Create a new Connection object
  AConnection = ADO.CreateConnection();

  // Specify the connection string
  AConnection.ConnectionString = "Provider=SQLOLEDB;Server=<redacted>;Database=CMS;Uid=" + userID + ";Pwd=" + pWord;
  // Activate the connection
  AConnection.Open();
  // Create a new Coomnad object
  Cmd = ADO.CreateCommand();
  // To create an ADO command you can also use the following code:
  // Cmd = getActiveXObject("ADODB.Command");
  // Specify the connection
  Cmd.ActiveConnection = AConnection;
  // Specify command type and text
  Cmd.CommandType = adCmdText;
  // EXEC stored procedure 
  Indicator.PushText("Populate Queue System Procedure can take a few minutes!");
  cmdTxt = 'EXEC Monitor_PopulateQueSys';
  Log.Message(cmdTxt);
  Cmd.CommandText = cmdTxt;
  // Execute the command
  Cmd.CommandTimeout = 0;
  RecSet = Cmd.Execute();
  // Process the results
    Log.Checkpoint("Populate Queue System procedure completed.");
  
  AConnection.Close();
  Indicator.PopText();
}

Does anyone have any idea why this will fail to wait for the procedure to complete before moving on to the next step the first time it is run?  Thanks for your replies.

[AlexKaras · Accepted solution]

Hi,

Just a wild guess: what if you try approach illustrated here - https://support.smartbear.com/viewarticle/9014/ ?

[elesniewski]

This does appear to work well.  I will update as solution after a few days of testing.  Thanks.

[elesniewski]

The test is broken again, at this step.

I setup the code as suggested in the article.

It worked fine the first few times, but today, it is showing the same bug.

The test does not wait for SQL query results before performing the next step.

Is there and dummy output result that I can setup, even though the stored procedure does not take any parameters?  I would like to try and see if this will force TestComplete to wait for the stored procedure to finish before moving on to the next step?  Something like:

  // Adding a return parameter
  SProc.Parameters.AddParameter();
  SProc.Parameters.Items(0).name = "RETURN_VALUE";
  SProc.Parameters.Items(0).DataType = adInteger;
  SProc.Parameters.Items(0).Direction = adParamReturnValue;  
  SProc.Parameters.Items(0).Value = null;

...

Log.Message("Result: " + SProc.Parameters.Items(0).Value);

[cneedham]

Your problem is bigger than that... you need to wait for lots of things in your scripts probably.. 

Create a generic funciton like this and use it: 

static runBatchFile(batchFile, waitForExit, args = "")
{
WshShell.Run("cmd /c .\\Stores\\Files\\" + batchFile + " " + args, 1, waitForExit);
}