SQL Test GUI Transaction workaround

TomAtAdama

Found a feature in SQL Test GUI that prevented the use of msdb.dbo.sp_send_dbmail. The SQL Test GUI would queue and e-mail and rollback the queued email. The workaround is to add this code to the test driver:

1) IF @@TRANCOUNT > 0 ROLLBACK
2) BEGIN TRANSACTION

For example:

USE DBNAME
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--  Comments here are associated with the test.
--  For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/

ALTER PROCEDURE [test_class].[test send email]
AS
BEGIN
  IF @@TRANCOUNT > 0 ROLLBACK -- Red-Gate TRANSACTION workaround
  --Assemble
  --  This section is for code that sets up the environment. It often
  --  contains calls to methods such as tSQLt.FakeTable and tSQLt.SpyProcedure
  --  along with INSERTs of relevant data.
  --  For more information, see http://tsqlt.org/user-guide/isolating-dependencies/

  --Act
  --  Execute the code under test like a stored procedure, function or view
  --  and capture the results in variables or tables.
    
    -- Send email
        EXEC msdb.dbo.sp_send_dbmail @profile_name = 'MAILPROFILE',
                                     @recipients = 'productmanager@red-gate.com',
                                     @body = 'Dear product manager, please make transaction wrapping optional/configurable per test',
                                     @subject = 'SQL Test Feature request',
                                     @from_address = 'noreply@red-gate.com'

  --Assert
  --  Compare the expected and actual values, or call tSQLt.Fail in an IF statement.  
  --  Available Asserts: tSQLt.AssertEquals, tSQLt.AssertEqualsString, tSQLt.AssertEqualsTable
  --  For a complete list, see: http://tsqlt.org/user-guide/assertions/

  BEGIN TRANSACTION -- Red-Gate TRANSACTION workaround
END;