TestComplete does not wait for SQL query results before performing next step
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.
Hi,
Just a wild guess: what if you try approach illustrated here - https://support.smartbear.com/viewarticle/9014/ ?