Red Gate forums: SQL Test

RE: flatlining my desktop

james.billings — Mon, 15 Apr 2013 17:36:49 GMT

Pretty unusual then. Do you have any other (non Red Gate) SSMS add-ins installed?

One option may be to try downloading a trial of our ANTS Performance Profiler, and profiling SSMS. This might show whereabouts in the execution excessive CPU is occurring.
If that works, you can save the results, zip them, and mail them to support@red-gate.com quoting F0071759 in the subject line.

RE: flatlining my desktop

dschlobohm — Mon, 15 Apr 2013 17:30:03 GMT

Just by pressing the SQL test the window opens the utilization goes up
You exit the window or press the button to turn SQL test off the utilization hits 30 percent and stays there until I exit SSMS. No test is running just sits there spinng cpu's

RE: flatlining my desktop

james.billings — Mon, 15 Apr 2013 16:54:21 GMT

Thanks for your post
This isn't something we're aware of that I know of- does it happen irrespective of which SQL instance you're connected to?

Does it occur simply by pressing the SQL Test button so the window appears (i.e. you've not added any tests to any databases yet) or is it when you run the tests themselves?

flatlining my desktop

dschlobohm — Fri, 12 Apr 2013 12:53:10 GMT

using ssms 10.5 i have noticed that if i click on SQLtest that it use 30 percent of my desktop cpus regardless if anything is running

I like to use different ssms for different projects so as you can see it flatlines my desktop quickly. I has also notice that if i turn off SQL test this doesnt fix the problem you have to get out of SSMS and back in

RE: Trouble testing an expected sproc failure

AdamY — Tue, 02 Apr 2013 18:48:38 GMT

Thanks. This has been posted at:

http://groups.google.com/forum/#!topic/tsqlt/m4-SV4lfI9U

RE: Trouble testing an expected sproc failure

David Atkinson — Tue, 02 Apr 2013 17:32:46 GMT

Questions to do with the underlying tSQLt framework are best placed on the GoogleGroup forum:

https://groups.google.com/forum/?fromgroups#!forum/tsqlt

To access the tSQLt experts I'd recommend that you post you question there, maybe referencing this post to avoid duplication.

kind regards

David Atkinson
Red Gate

Trouble testing an expected sproc failure

AdamY — Tue, 02 Apr 2013 16:48:40 GMT

I am new to SQL Test and I'm trying to test that a sproc will fail when expected and am having some problems. I am curious if anyone has a work-around or can tell me what I've done wrong.
From what I can tell, SQL Test stops executing the sproc when an error is raised, but SQL Server does not - which is why it works when our application calls the sproc, but not when SQL Test does. This causes 2 problems:
1. SQL Test thinks the sproc's return code is NULL.
2. SQL Test thinks there is an uncommitted transaction since it never runs the ROLLBACK in the sproc.

Here is the setup...

[1] Sproc to be tested:

Code:

CREATE PROCEDURE [dbo].[uspMakeError]
AS
SET NOCOUNT ON

DECLARE
@RowCount INT,
@RetCode INT,
@TranLevel INT,
@ErrMsg VARCHAR(2047),
@ErrState INT,
@ErrSev INT

BEGIN TRY
SET @TranLevel = @@TRANCOUNT
SET @RetCode = 0

BEGIN TRANSACTION

IF 1 = 1 -- Some condition is met that should raise an error.
BEGIN
SET @ErrMsg = 'My error message.'
RAISERROR(@ErrMsg, 11, 1) -- Send control to CATCH block.
END
END TRY

BEGIN CATCH
SET @RetCode = -1
SET @ErrState = ERROR_STATE()
SET @ErrSev = ERROR_SEVERITY()

-- Update error message.
SET @ErrMsg = ISNULL(ERROR_MESSAGE(), 'No error message available.')
/* Only show sproc name if error occurred in a child sproc. */
+ CASE WHEN ISNULL(OBJECT_NAME(@@PROCID), '') <> ISNULL(ERROR_PROCEDURE(), '') THEN ' Procedure: ' + ERROR_PROCEDURE() + '.' ELSE '' END
+ ' Error: ' + CAST(ISNULL(ERROR_NUMBER(), 0) AS VARCHAR) + '.'
+ ' Line: ' + CAST(ISNULL(ERROR_LINE(), 0) AS VARCHAR) + '.'

RAISERROR(@ErrMsg , @ErrSev, @ErrState) -- !! THIS IS WHERE SQL TEST STOPS !!

IF @@TRANCOUNT > @TranLevel
ROLLBACK TRANSACTION
END CATCH

IF @@TRANCOUNT > @TranLevel
COMMIT TRANSACTION

RETURN @RetCode

[2] Test in SQL Server. Run this code in SSMS - it works fine, the @rc value is correct and there are no transaction state errors:

Code:

DECLARE @rc INT
EXEC @rc = dbo.uspMakeError
PRINT @rc

Quote:

Msg 50000, Level 11, State 1, Procedure uspMakeError, Line 36
My error message. Error: 50000. Line: 22.
-1

[3] Now create the SQL Test test sproc (assumes a "test" schema):

Code:

CREATE PROCEDURE [test].[test Make sure a sproc fails]
AS
BEGIN
DECLARE @RC INT

BEGIN TRY
EXEC @RC = dbo.uspMakeError
END TRY
BEGIN CATCH
END CATCH

EXEC tSQLt.AssertEquals @Expected = -1, @Actual = @RC, @Message = 'Oops.'

END

[4] Now run the "test Make sure a sproc fails" test in SQL Test. This is the output:

Quote:

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
[test].[test Make sure a sproc fails] failed: Oops. Expected: <-1> but was: <NULL>

You can see 2 problems. The transaction count error and notice that SQL Test thinks the @RC value is NULL instead of -1.

Even if I remove the "BEGIN TRANSACTION" line in [dbo].[uspMakeError], the SQL Test test will still fail:

Quote:

Test Procedure: [MyDB].[test].[test Make sure a sproc fails] on MyServer
[test].[test Make sure a sproc fails] failed: Oops. Expected: <-1> but was: <NULL>

RE: How to test a trigger?

stanori — Fri, 29 Mar 2013 21:22:56 GMT

Hi AdamY,

Thanks for your post. It looks like something simliar has been covered in stackoverflow:

http://stackoverflow.com/questions/13553928/faketable-with-tsqlt-remove-triggers

It looks like there is a workaround solution to make this happen.

Best Regards,
Steve

How to test a trigger?

AdamY — Thu, 28 Mar 2013 20:19:02 GMT

TableA has Trigger1 that inserts data into TableB. I'd like to test that the trigger works correctly. Can I do that with SQL Test? I haven't been able to find a way yet. Does FakeTable handle triggers?

RE: Window corruption

Manfred.Castro — Thu, 21 Feb 2013 19:04:41 GMT

Thank you for reporting this to us.
I have logged this issue in our bug tracking system under the internal reference number of SQT-279.

Window corruption

simonjmartin — Wed, 20 Feb 2013 10:01:38 GMT

When I start SSMS the SQL Test window / tab is corrupted very much as in which is referenced in http://sqltest.uservoice.com/forums/140716-sql-test-forum/suggestions/3576855-tsqlt-window-popup-fail

It is fixed by opening the window but does happen again if minimising...

RE: Setup method?

datacentricity — Mon, 14 Jan 2013 17:16:01 GMT

You can try quickly throwing a PRINT statement into the SetUp procedure and exceute the test(s) acheck the output in SSMS see if it is definitely being called.

As with other users, specifying the schema correctly in all references is a common typo/gotcha.

Are you running against a case-sensitive instance/database? There were a couple of historic bugs around casing for the Setup procedure name - fixed in different versions.

Otherwise, try posting your code and we'll see if we can help

RE: Setup method?

rocky111 — Mon, 14 Jan 2013 12:35:43 GMT

there are other similar failures on all my tests. It looked to me like the method simply wasn't being called. The class I've got for all my tests is BoardSummary, I created a new test in the class then renamed it to SetUp so I'm confident it is in the right class - but either I've massively mucked up the setup method or it isn't being called before the tests... I'm not sure.

RE: Suggestion: Leave inactive test lists collapsed

casadyc — Fri, 19 Oct 2012 17:18:00 GMT

Yes, this would be exceptionally useful.

RE: Generate Reports from XML Output file

Brian Donahue — Mon, 01 Oct 2012 11:05:38 GMT

I'd think every build platform could understand XML, so it shouldn't be a problem to integrate the report with a build system. The people at tSQLt have documented how you would put the reports into CruiseControl:

http://tsqlt.org/177/integrating-tsqlt-with-cruise-control/

You may need to write a bit of XSL if you want it to look nice.