SQL Test not showing all results in TestResult table

  • BrainDonor

    SSCoach

    Points: 19148

    Playing with SQL Test against the AdventureWorks2012 database, and when executing all of the SQLCop tests, I don't appear to be getting all of the results written to the tSQLt.TestResult table - only the last test of the queue.

    The screen-shot above shows that there are 12 failures, but the query against TestResult only shows 1 result.

    Interestingly, the 'Id' column increments by 49 each time, which is the number of tests within that list.

    Additionally, if I run the query against TestResult whilst the tests are executing (with NOLOCK) I can see other entries being added while running, but it only ever seems to show one row.

    If I run any of these tests individually then the results from that one test are always available within the TestResult table.

    So it appears that every row written to the TestResult table is removed as the next row from that test suite is written to it.

    Has anybody else encountered this, or know what it may be that I'm doing that would cause this?

    Steve Hall
    Linkedin
    Blog Site

  • Cadavre

    SSC-Forever

    Points: 41582

    Don't know what the issue is (I don't use the framework, installed it to test if I could reproduce), but if you change the sproc Private_CleanTestResult to: -

    ALTER PROCEDURE [tSQLt].[Private_CleanTestResult]

    AS

    BEGIN

    SELECT '1';

    --DELETE FROM tSQLt.TestResult;

    END;

    GO

    It stops the table being truncated at all. So my assumption is that somewhere, after each test is run, it runs "Private_CleanTestResult" which deletes everything from "TestResult".


    Forever trying to learn
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]

    If you litter your database queries with nolock query hints, are you aware of the side effects?  Try reading a few of these links...
    (*) Missing rows with nolock
    (*) Allocation order scans with nolock(*) Consistency issues with nolock
    (*) Transient Corruption Errors in SQL Server error log caused by nolock
    (*) Dirty reads, read errors, reading rows twice and missing rows with nolock

    Craig Wilkinson - Software Engineer
    LinkedIn

  • BrainDonor

    SSCoach

    Points: 19148

    Thanks Craig,

    That's an easy enough solution, so I'll try that and if I do want to clear the table I'll create a Setup stored proc to call first.

    Interesting that it uses DELETE and not TRUNCATE TABLE.

    Thanks for your time.

    Steve Hall
    Linkedin
    Blog Site

  • BrainDonor

    SSCoach

    Points: 19148

    Well it was worth a shot. It doesn't clear the TestResult table, which I wanted, but it marks all of the tests as having passed, even though checking the results in the table show that the same 12 still failed.

    It would seem to upset something somewhere by trying that workaround.

    Steve Hall
    Linkedin
    Blog Site

  • BrainDonor

    SSCoach

    Points: 19148

    Apparently it's by design. Each individual test writes to the TestResult table, having cleared that table first. So if you run 49 tests (as you do if you run the entire SQLCop class) then you just get back the result of the last test executed from that suite.

    I'm not sure I see the point in that.

    There is a suggestions site within Redgate (UserVoice) where I can post a suggestion, but I must say - anybody from Redgate reading this - that is one of the ugliest web sites I have ever seen. I've tried it in IE and Chrome and it isn't pretty.

    Steve Hall
    Linkedin
    Blog Site

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714067

    I think you've hit it. Each test runs as a separate tsqlt.run call rather than a larger call with all results.

    At the end of the day, I really only worry about failing tests. That's the concern, but if you have multiples, really you have to run them one by one to get the results for analysis. Kind of a pain.

    Open UserVoice (or vote) suggestions, as I'd like to see more work on SQL Test done to improve it

  • Cadavre

    SSC-Forever

    Points: 41582

    I guess a trigger on insert that saves the data in an audit table is the temporary solution until it gets properly fixed.


    Forever trying to learn
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]

    If you litter your database queries with nolock query hints, are you aware of the side effects?  Try reading a few of these links...
    (*) Missing rows with nolock
    (*) Allocation order scans with nolock(*) Consistency issues with nolock
    (*) Transient Corruption Errors in SQL Server error log caused by nolock
    (*) Dirty reads, read errors, reading rows twice and missing rows with nolock

    Craig Wilkinson - Software Engineer
    LinkedIn

  • BrainDonor

    SSCoach

    Points: 19148

    Steve Jones - SSC Editor (1/19/2016)


    I think you've hit it. Each test runs as a separate tsqlt.run call rather than a larger call with all results.

    At the end of the day, I really only worry about failing tests. That's the concern, but if you have multiples, really you have to run them one by one to get the results for analysis. Kind of a pain.

    Open UserVoice (or vote) suggestions, as I'd like to see more work on SQL Test done to improve it

    Somebody made a similair suggestion within UserVoice, so I added to that.

    Regarding the layout of the UserVoice site - there's probably an issue accessing it from my work browsers. I had to access the site via my 'phone in order to get something that was usable. It looks like a lot of the graphics are being blocked for some reason.

    Steve Hall
    Linkedin
    Blog Site

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply