Problem:

How to obtain the Execution Properties of WSH, SSH, and ProcessExplorer type tests?

Resolution:

User can obtain information about the SCTM execution environment for a Test Definition type using web services.

The following properties can be retrieved:

Execution Definition Name
Execution Definition Identifier
Product Name
Product Version
Product Build
Execution Definition Keywords

Currently only the JUnit and SilkPerfomer plugin have built-in support for this new feature.

This feature was added to the Plugin API and can be used by all plugins. The properties can be accessed by calling getProperties() of the ExecutionContextInfo.

However, this feature is currently not available in Test Definition types WSH, SSH, and ProcessExecutor. Until built-in support for these test types are available, in order to retrieve execution properties for these test types, we would simply query the SCTM database to retrieve this information. The following SQL statement returns the relevant information.

// Borland Code VOzohili
// Date: 21 July 2009

// Get Execution Definition details of test run

// Open SCTM database using SQL Enterprise Manager or equivalent
// Run the SQL query below against SCTM database

// In the "WHERE" clause, please specify the execution definition id you wish to search

SilkCentral Test Manager 2009
------------------------------

SELECT SCC_Projects.ProjectName AS [Project Name], TM_TestPlanNodes.Name AS [Test Container Name],
TM_ExecTreeNodes.NodeName AS [Execution Definition Name], TM_ExecutionDefinitions.ExecDefID_pk_fk AS
[Execution Definition ID],
TM_ExecDefinitionRuns.ProductName, TM_ExecDefinitionRuns.VersionName, TM_ExecDefinitionRuns.BuildName,
TM_ExecDefinitionRuns.Keywords,
CAST(TM_TestDefExecutions.ExecutionTimestamp AS CHAR) AS [Execution Timestamp]
FROM TM_ExecDefinitionRuns INNER JOIN
TM_ExecutionDefinitions ON TM_ExecDefinitionRuns.ExecDefID_fk = TM_ExecutionDefinitions.ExecDefID_pk_fk
INNER JOIN
TM_TestContainers ON TM_ExecutionDefinitions.TestContainerID_fk =
TM_TestContainers.TestContainerID_pk_fk INNER JOIN
TM_TestPlanNodes ON TM_TestContainers.TestContainerID_pk_fk = TM_TestPlanNodes.NodeID_pk INNER JOIN
TM_ExecTreeNodes ON TM_ExecutionDefinitions.ExecDefID_pk_fk = TM_ExecTreeNodes.NodeID_pk INNER JOIN
SCC_Projects ON TM_TestContainers.ProjectID_fk = SCC_Projects.ProjectID_pk AND
TM_ExecTreeNodes.ProjectID_fk = SCC_Projects.ProjectID_pk INNER JOIN
TM_TestDefExecutions ON TM_ExecDefinitionRuns.ExecDefRunID_pk_fk = TM_TestDefExecutions.ExecDefRunID_fk
AND
SCC_Projects.ProjectID_pk = TM_TestDefExecutions.ProjectID_fk
WHERE (TM_ExecutionDefinitions.ExecDefID_pk_fk = "")

If you require the SCTM 2008 R2 SP1 sql query, please download the relevant file from the solution attachments. The difference between the SCTM 2008R2SP1 and SCTM 2009 queries is that the table TM_Executions used in SCTM 2008R2SP1 no longer exists in SCTM 2009, therefore in SCTM 2009, the query uses the TM_ExecDefinitionRuns table instead.

To run the above query, we would require a connection to the database, therefore, the query can either be run from SCTM Reports or using SQL Server Manager Studio or equivalent. Furthermore, the VBScript code below executes the above SQL query and returns the result to a recordset which in turn will be saved as an output XML file. User can then read the required values from this XML file.

Note: Please specify the SQL server instance name, database name, user id and password for the database connection. The connection string used below is of an SQL Server, user can specify their own connection string if database type is different.

// Please specify the database connection values Server, Database, user id and password
// In the "WHERE" clause, please specify the execution definition id you wish to search

SilkCentral Test Manager 2009
------------------------------

"Borland Code Vozohili
"21 July 2009
"SilkCentral Test Manager 2009

"Please specify the database connection values Server, Database, user id and password
"Also specify the execution definition id in the sql WHERE clause

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adPersistXML = 1

"Database Connection String
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};" & _
"Server=;" & _
"Database=;" & _
"user id=;" & _
"password=;"


sql = "SELECT SCC_Projects.ProjectName, TM_TestPlanNodes.Name, " & _
"TM_ExecTreeNodes.NodeName, TM_ExecutionDefinitions.ExecDefID_pk_fk, " & _
"TM_ExecDefinitionRuns.ProductName, TM_ExecDefinitionRuns.VersionName, TM_ExecDefinitionRuns.BuildName, TM_ExecDefinitionRuns.Keywords, " & _
"CAST(TM_TestDefExecutions.ExecutionTimestamp AS CHAR) " & _
"FROM TM_ExecDefinitionRuns INNER JOIN " & _
"TM_ExecutionDefinitions ON TM_ExecDefinitionRuns.ExecDefID_fk = TM_ExecutionDefinitions.ExecDefID_pk_fk INNER JOIN " & _
"TM_TestContainers ON TM_ExecutionDefinitions.TestContainerID_fk = TM_TestContainers.TestContainerID_pk_fk INNER JOIN " & _
"TM_TestPlanNodes ON TM_TestContainers.TestContainerID_pk_fk = TM_TestPlanNodes.NodeID_pk INNER JOIN " & _
"TM_ExecTreeNodes ON TM_ExecutionDefinitions.ExecDefID_pk_fk = TM_ExecTreeNodes.NodeID_pk INNER JOIN " & _
"SCC_Projects ON TM_TestContainers.ProjectID_fk = SCC_Projects.ProjectID_pk AND " & _
"TM_ExecTreeNodes.ProjectID_fk = SCC_Projects.ProjectID_pk INNER JOIN " & _
"TM_TestDefExecutions ON TM_ExecDefinitionRuns.ExecDefRunID_pk_fk = TM_TestDefExecutions.ExecDefRunID_fk AND " & _
"SCC_Projects.ProjectID_pk = TM_TestDefExecutions.ProjectID_fk " & _
"WHERE (TM_ExecutionDefinitions.ExecDefID_pk_fk = "")"


Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open sql, objConn, adOpenStatic, adLockOptimistic
objRecordSet.MoveNext
objRecordSet.Save "output.xml", adPersistXML

objRecordSet.Close
objConn.Close