How to view test results in tSQLt?

Question

We are using tSQLt for unit testing our database and executed below:

EXEC tSQLt.Run '[testComplianceDimensions].[test CountOfPropertiesWithLatestRepairJob]'
EXEC tSQLt.Run '[testComplianceDimensions].[test FactPropertyLatestRepairJobAgg]'

This is the output Message:

(1 row(s) affected)
[testComplianceDimensions].[test CountOfPropertiesWithLatestRepairJob] failed: (Failure) Fact_PropertyLatestRepairJobAgg failure - property counts do not match Expected: <324211> but was: <0>
 
+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name                                                        |Dur(ms)|Result |
+--+----------------------------------------------------------------------+-------+-------+
|1 |[testComplianceDimensions].[test CountOfPropertiesWithLatestRepairJob]|  16037|Failure|
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 1 test case(s) executed, 0 succeeded, 1 failed, 0 errored.
-----------------------------------------------------------------------------

(1 row(s) affected)
 
+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name                                                  |Dur(ms)|Result |
+--+----------------------------------------------------------------+-------+-------+
|1 |[testComplianceDimensions].[test FactPropertyLatestRepairJobAgg]|     14|Success|
-----------------------------------------------------------------------------
Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

Is there a table where all test results are saved?

Thanks for your help.

Answer 1

The table tSQLt.TestResult contains the result of the last execution of tSQLt.Run or tSQLt.RunAll

The contents of the table are not preserved between executions of these procedures - in your example, the table would contain the results of the first test until the second test was executed. If you want to gather the results of more than one test, you need to execute the tests as part of the same command - in this case, possibly with EXEC tSQLt.Run '[testComplianceDimensions]' (which will run all the tests in the suite)

Answer 2

I was able to fairly simply modify my tSQLt installation to log the results to another database table using @gotqn's suggested method to change the output format. Note that I had to modify another proc as well to allow RedGate's SQL Test UI to post the results.

I started my journey here: https://groups.google.com/g/tsqlt/c/qP1iazrp-pE

Which led me to create a script which I run on each new tSQLt installation in order to gather all results into a single table, along with additional metrics to help us build a dashboard for test passes/failures.

[tSQLt].[NullTestResultFormatter] was modified, since running tests via RedGate's SQL Test's UI uses this Formatter. If you aren't using RedGate's SQL Test UI and just doing EXEC tSQLt.Run* then you can likely ignore/delete this step.

[tSQLt].[SaveTestResultFormatter] is the new Formatter I created. Simply dumps the last test result into my table.

EXECUTE @RC = [tSQLt].[SetTestResultFormatter] @Formatter changes the Default formatter to my new one.

Here's the full block of code I run on each new tSQLt installation. It assumes the database and table exist: [tSQLtRunHistory].[dbo].[TestResults]

/*
tSQLt Customization - Logging of TestResult
This Script, when run on a database with tSQLt installed, will modify
the ResultFormatters to insert the TestResult data to an external
database (tSQLtRunHistory.dbo.TestResults)
*/

USE --CHOOSE DATABASE TO ENABLE TEST LOGGING
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER   PROCEDURE [tSQLt].[NullTestResultFormatter]
AS
BEGIN

    --Manually added for custom reporting of each test result
    --Changing this allows RedGate SQL Test to post, since it runs tests with the NullFormatter, not the DefaultFormatter
    --Adds the test result to another table - doesn't seem to post when running tests via the GUI, but does when tSQLt.RunAll is executed
    INSERT INTO [tSQLtRunHistory].[dbo].[TestResults]
        (
            [Server]
            , [LoginUserName]
            , [HostName]
            , [Database]
            , [Class]
            , [TestCase]
            , [TranName]
            , [Result]
            , [Msg]
            , [TestStartTime]
            , [TestEndTime]
        )
    SELECT
        @@SERVERNAME
        , SUSER_NAME()
        , HOST_NAME()
        , DB_NAME()
        , [Class]
        , [TestCase]
        , [TranName]
        , [Result]
        , [Msg]
        , [TestStartTime]
        , [TestEndTime]
    FROM    [tSQLt].[TestResult];
    --END of Manually added

    RETURN 0;
END;
GO

CREATE OR ALTER PROCEDURE [tSQLt].[SaveTestResultFormatter]
AS
BEGIN
    --Custom ResultFormatter which adds each TestResult to a central database for cataloguing
    --Adds the test result to another table
    INSERT INTO [tSQLtRunHistory].[dbo].[TestResults]
        (
            [Server]
            , [LoginUserName]
            , [HostName]
            , [Database]
            , [Class]
            , [TestCase]
            , [TranName]
            , [Result]
            , [Msg]
            , [TestStartTime]
            , [TestEndTime]
        )
    SELECT
        @@SERVERNAME
        , SUSER_NAME()
        , HOST_NAME()
        , DB_NAME()
        , [Class]
        , [TestCase]
        , [TranName]
        , [Result]
        , [Msg]
        , [TestStartTime]
        , [TestEndTime]
    FROM    [tSQLt].[TestResult];
END;
GO

DECLARE @RC INT
DECLARE @Formatter NVARCHAR(4000)

SET @Formatter = 'tSQLt.SaveTestResultFormatter' --'tSQLt.DefaultResultFormatter'

EXECUTE @RC = [tSQLt].[SetTestResultFormatter] 
   @Formatter
GO

For reference, my [dbo].[TestResults] table creation script is:

USE [tSQLtRunHistory]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestResults](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [Server] [NVARCHAR](MAX) NOT NULL,
    [LoginUserName] [NVARCHAR](MAX) NOT NULL,
    [HostName] [NVARCHAR](MAX) NOT NULL,
    [Database] [NVARCHAR](MAX) NOT NULL,
    [Class] [NVARCHAR](MAX) NOT NULL,
    [TestCase] [NVARCHAR](MAX) NOT NULL,
    [TranName] [NVARCHAR](MAX) NOT NULL,
    [Result] [NVARCHAR](MAX) NULL,
    [Msg] [NVARCHAR](MAX) NULL,
    [TestStartTime] [DATETIME] NOT NULL,
    [TestEndTime] [DATETIME] NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Answer 3

You can try to change the output format using tSQLt.SetTestResultFormatter and consume the results by another application as:

There is currently no way to change the layout of the default output. however, there is an xml format generator that returns the test results in a JUnit compatible format. That can be used to run tSQLt tests inside a CI environment. See https://www.simple-talk.com/sql/sql-tools/using-sql-test-database-unit-testing-with-teamcity-continuous-integration/ for an example implementation.

and as explained the tSQLt.SetTestResultFormatter

is an extension point that allows the output to be formatted in different ways. There is for example an XML formatter that you can use to generate JUnit compatible output instead of human readable. We have not stabilized that piece yet. that is, why it is not documented yet. The goal is to allow external formatters to be installed through this as extension to the existing ones. However, you can get the XML output through other means. See for example http://tsqlt.org/177/integrating-tsqlt-with-cruise-control/

If this is not you are looking for, you will get more help in the GitHub repository.

Also, you might stop and think twice before investing in tSQLt as implementing your own solution will be not so difficult and you will have more control (as we did).