SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Test not showing all results in TestResult table


SQL Test not showing all results in TestResult table

Author
Message
Steve Hall
Steve Hall
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16090 Visits: 12962
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
Craig Wilkinson
Craig Wilkinson
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36562 Visits: 8619
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/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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
Steve Hall
Steve Hall
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16090 Visits: 12962
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
Steve Hall
Steve Hall
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16090 Visits: 12962
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
Steve Hall
Steve Hall
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16090 Visits: 12962
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
Steve Jones
SSC Guru
SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)

Group: Administrators
Points: 585655 Visits: 20905
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

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Craig Wilkinson
Craig Wilkinson
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36562 Visits: 8619
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/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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
Steve Hall
Steve Hall
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16090 Visits: 12962
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search