none
Unable to Run "Expected Failure" Database Unit Test

    Question

  • I have VS 2010 Database Unit Test that I need to upgrade to SSDT.  The test is an "Expected Failure" test, in which I expect to get a specific T-SQL error when I run the test.  In this case, the expected T-SQL error is a violation of a Unique Key constraint.

    The test fails with an "Object reference not set to an instance of an object" exception.  Yet when I open the "Output" in Test Explorer, I see the T-SQL error I am expecting:

    Test Name:	CheckUniqueConstraintOnParameterDisableHIPAA
    Test Outcome:	Failed
    Result Message:	Object reference not set to an instance of an object.
    Result StandardOutput:	
    Debug Trace:
    Executing pre-test script...
    1 batches, 0 ResultSets, 0 rows affected
    Executing test script...
    Sql Error: 'Violation of UNIQUE KEY constraint 'unqParameterIDParameterValue'. Cannot insert duplicate key in object 'dbo.ParameterDisableHIPAA'. The duplicate key value is (1, BadValue).' (Severity 14, State 1)
    Sql Error: 'The statement has been terminated.' (Severity 0, State 0)

    This is the error I see in Test Explorer:

    Test Name:	CheckUniqueConstraintOnParameterDisableHIPAA
    Test FullName:	RFAppDBTestProject.ParameterDisableHIPAAExpectedFailure.CheckUniqueConstraintOnParameterDisableHIPAA
    Test Source:	d:\EHIT\Main\Database\ApplicationDatabases\Source\RFAppDBTestProject\ParameterDisableHIPAAExpectedFailure.cs : line 45
    Test Outcome:	Failed
    Test Duration:	0:00:00.0502576
    Result Message:	Object reference not set to an instance of an object.
    Result StackTrace:	
    at Microsoft.Data.Tools.Schema.Sql.UnitTesting.ExecutionEngine.ExecuteTest(ConnectionContext ctx, String testSql, DbParameter[] tsqlParameters)
       at Microsoft.Data.Tools.Schema.Sql.UnitTesting.SqlDatabaseTestService.Execute(ConnectionContext scriptExecutionContext, ConnectionContext privilegedExecutionContext, SqlDatabaseTestAction action, DbParameter[] sqlParameters)
       at RFAppDBTestProject.ParameterDisableHIPAAExpectedFailure.CheckUniqueConstraintOnParameterDisableHIPAA() in d:\EHIT\Main\Database\ApplicationDatabases\Source\RFAppDBTestProject\ParameterDisableHIPAAExpectedFailure.cs:line 52

    And here is the code for the unit test:

    using System;
    using System.Text;
    using System.Data;
    using System.Data.Common;
    using System.Collections.Generic;
    using Microsoft.Data.Tools.Schema.Sql.UnitTesting;
    using Microsoft.Data.Tools.Schema.Sql.UnitTesting.Conditions;
    using Microsoft.VisualStudio.TestTools.UnitTesting;
    namespace RFAppDBTestProject
    {
      [TestClass()]
      public class ParameterDisableHIPAAExpectedFailure : SqlDatabaseTestClass
      {
        public ParameterDisableHIPAAExpectedFailure()
        {
          InitializeComponent();
        }
        [TestInitialize()]
        public void TestInitialize()
        {
          base.InitializeTest();
        }
        [TestCleanup()]
        public void TestCleanup()
        {
          base.CleanupTest();
        }
        // Here we modify the TestMethod to add an attribute for the expected SQL exception:
        // Msg 2627, Level 14, State 1, Line 1
        // Violation of UNIQUE KEY constraint 'unqParameterIDParameterValue'. Cannot insert duplicate key in object 'dbo.ParameterDisableHIPAA'.
        // 
        // Add the "ExpectedSqlException" attribute to TestMethod
        // Map the parameters of this attribute to the values returned in the first line of the message of the SQL exception, as follows:
        // MessageNumber = value of "Msg"
        // Severity = value of "Level"
        // State = value of "State"
        // 
        [TestCategory("RFAppDBTests"), TestMethod(), ExpectedSqlException(MatchFirstError = true, MessageNumber = 2627, Severity = 14, State = 1)]
        public void CheckUniqueConstraintOnParameterDisableHIPAA()
        {
          SqlDatabaseTestActions testActions = this.CheckUniqueConstraintOnParameterDisableHIPAAData;
          // Execute the pre-test script
          // 
          System.Diagnostics.Trace.WriteLineIf((testActions.PretestAction != null), "Executing pre-test script...");
          SqlExecutionResult[] pretestResults = TestService.Execute(this.PrivilegedContext, this.PrivilegedContext, testActions.PretestAction);
          // Execute the test script
          // 
          System.Diagnostics.Trace.WriteLineIf((testActions.TestAction != null), "Executing test script...");
          SqlExecutionResult[] testResults = TestService.Execute(this.ExecutionContext, this.PrivilegedContext, testActions.TestAction);
          // Execute the post-test script
          // 
          System.Diagnostics.Trace.WriteLineIf((testActions.PosttestAction != null), "Executing post-test script...");
          SqlExecutionResult[] posttestResults = TestService.Execute(this.PrivilegedContext, this.PrivilegedContext, testActions.PosttestAction);
        }
        #region Designer support code
        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
          Microsoft.Data.Schema.UnitTesting.DatabaseTestAction CheckUniqueConstraintOnParameterDisableHIPAA_TestAction;
          System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(ParameterDisableHIPAAExpectedFailure));
          Microsoft.Data.Schema.UnitTesting.Conditions.ScalarValueCondition scalarValueCondition1;
          Microsoft.Data.Schema.UnitTesting.DatabaseTestAction CheckUniqueConstraintOnParameterDisableHIPAA_PretestAction;
          Microsoft.Data.Schema.UnitTesting.DatabaseTestAction CheckUniqueConstraintOnParameterDisableHIPAA_PosttestAction;
          this.CheckUniqueConstraintOnParameterDisableHIPAAData = new Microsoft.Data.Schema.UnitTesting.DatabaseTestActions();
          CheckUniqueConstraintOnParameterDisableHIPAA_TestAction = new Microsoft.Data.Schema.UnitTesting.DatabaseTestAction();
          scalarValueCondition1 = new Microsoft.Data.Schema.UnitTesting.Conditions.ScalarValueCondition();
          CheckUniqueConstraintOnParameterDisableHIPAA_PretestAction = new Microsoft.Data.Schema.UnitTesting.DatabaseTestAction();
          CheckUniqueConstraintOnParameterDisableHIPAA_PosttestAction = new Microsoft.Data.Schema.UnitTesting.DatabaseTestAction();
          // 
          // CheckUniqueConstraintOnParameterDisableHIPAA_TestAction
          // 
          CheckUniqueConstraintOnParameterDisableHIPAA_TestAction.Conditions.Add(scalarValueCondition1);
          resources.ApplyResources(CheckUniqueConstraintOnParameterDisableHIPAA_TestAction, "CheckUniqueConstraintOnParameterDisableHIPAA_TestAction");
          // 
          // scalarValueCondition1
          // 
          scalarValueCondition1.ColumnNumber = 1;
          scalarValueCondition1.Enabled = true;
          scalarValueCondition1.ExpectedValue = "0";
          scalarValueCondition1.Name = "scalarValueCondition1";
          scalarValueCondition1.NullExpected = false;
          scalarValueCondition1.ResultSet = 1;
          scalarValueCondition1.RowNumber = 1;
          // 
          // CheckUniqueConstraintOnParameterDisableHIPAAData
          // 
          this.CheckUniqueConstraintOnParameterDisableHIPAAData.PosttestAction = CheckUniqueConstraintOnParameterDisableHIPAA_PosttestAction;
          this.CheckUniqueConstraintOnParameterDisableHIPAAData.PretestAction = CheckUniqueConstraintOnParameterDisableHIPAA_PretestAction;
          this.CheckUniqueConstraintOnParameterDisableHIPAAData.TestAction = CheckUniqueConstraintOnParameterDisableHIPAA_TestAction;
          // 
          // CheckUniqueConstraintOnParameterDisableHIPAA_PretestAction
          // 
          resources.ApplyResources(CheckUniqueConstraintOnParameterDisableHIPAA_PretestAction, "CheckUniqueConstraintOnParameterDisableHIPAA_PretestAction");
          // 
          // CheckUniqueConstraintOnParameterDisableHIPAA_PosttestAction
          // 
          resources.ApplyResources(CheckUniqueConstraintOnParameterDisableHIPAA_PosttestAction, "CheckUniqueConstraintOnParameterDisableHIPAA_PosttestAction");
        }
        #endregion
        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        #endregion
        private SqlDatabaseTestActions CheckUniqueConstraintOnParameterDisableHIPAAData;
      }
    }

    It doesn't appear that the "ExpectedSqlException" attribute has changed, but the test doesn't work.

    Tuesday, January 08, 2013 6:46 PM

Answers

  •  

    We have been able to reproduce this issue in VS 2012. The custom ExpectedException attribute is not working in VS 2012 - we are tracking it. At this point, I can suggest following 3 workarounds for the issue. My recommendation is to use #2.

    1. Replace ExpectedSqlException attribute in your code with ExpectedException attribute. For example, you could use ExpectedException(typeof(System.Data.SqlClient.SqlException)). Note that you lose the ability to verify Severity, MessageNumber and State.

    2. If you need to verify Severity, MessageNumber and State, you can put BEGIN TRY and END TRY in our T-SQL code and in the catch block, select ERROR_SEVERITY(), ERROR_NUMBER() and ERROR_STATE(). You can then use ScalarValueCondition / DataChecksum in the test designer to verify the values.  Here is an example:

    If your original script is:

    RAISEERROR ('Error message.', 16, 1 ) WITH LOG;

    You can modify it to:

    BEGIN TRY

    RAISEERROR ('Error message.', 16, 1 ) WITH LOG;

    END TRY

    BEGIN CATCH

    SELECT ERROR_SEVERITY(), ERROR_NUMBER(), ERROR_STATE();

    END CATCH;

    3. This approach is an extension of #1. In your code file, use try catch in the TestMethod. In the catch block, verify the severity, state etc in the exception and then rethrow it so that the ExpectedException attribute ensures that an exception was actually raised during the execution.

    -Vikrant Arora [MSFT]

    • Marked as answer by Ken Powers Wednesday, January 09, 2013 3:19 PM
    Wednesday, January 09, 2013 1:00 AM

All replies

  • Hi Ken,

    Can you verify that you don't see the error message "Object Reference not set to an instance of an object" if you remove the ExpectedSqlException attribute from the code ? What is the VS version you are using - you can find it under help -> about.

     

    Thanks,

    Vikrant Arora [MSFT]

    Tuesday, January 08, 2013 7:28 PM
  • Confirmed.  When I remove the attribute, I just get the T-SQL exception:

    Test Name:	CheckUniqueConstraintOnParameterDisableHIPAA
    Test FullName:	RFAppDBTestProject.ParameterDisableHIPAAExpectedFailure.CheckUniqueConstraintOnParameterDisableHIPAA
    Test Source:	d:\EHIT\Main\Database\ApplicationDatabases\Source\RFAppDBTestProject\ParameterDisableHIPAAExpectedFailure.cs : line 45
    Test Outcome:	Failed
    Test Duration:	0:00:00.025442
    Result Message:	
    Test method RFAppDBTestProject.ParameterDisableHIPAAExpectedFailure.CheckUniqueConstraintOnParameterDisableHIPAA threw exception: 
    System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'unqParameterIDParameterValue'. Cannot insert duplicate key in object 'dbo.ParameterDisableHIPAA'. The duplicate key value is (1, BadValue).
    The statement has been terminated.
    Result StackTrace:	
    at Microsoft.Data.Tools.Schema.Sql.UnitTesting.ExecutionEngine.ExecuteTest(ConnectionContext ctx, String testSql, DbParameter[] tsqlParameters)
       at Microsoft.Data.Tools.Schema.Sql.UnitTesting.SqlDatabaseTestService.Execute(ConnectionContext scriptExecutionContext, ConnectionContext privilegedExecutionContext, SqlDatabaseTestAction action, DbParameter[] sqlParameters)
       at RFAppDBTestProject.ParameterDisableHIPAAExpectedFailure.CheckUniqueConstraintOnParameterDisableHIPAA() in d:\EHIT\Main\Database\ApplicationDatabases\Source\RFAppDBTestProject\ParameterDisableHIPAAExpectedFailure.cs:line 52

    Here is my version info:

    Microsoft Visual Studio Ultimate 2012
    Version 11.0.51106.01 Update 1
    Microsoft .NET Framework
    Version 4.5.50709
    Installed Version: Ultimate
    Architecture and Modeling Tools   04940-004-0038003-02721
    Microsoft Architecture and Modeling Tools
        
    UML® and Unified Modeling Language™ are trademarks or registered trademarks of the Object Management Group, Inc. in the United States and other countries.
    LightSwitch for Visual Studio 2012   04940-004-0038003-02721
    Microsoft LightSwitch for Visual Studio 2012
    Office Developer Tools   04940-004-0038003-02721
    Microsoft Office Developer Tools
    Team Explorer for Visual Studio 2012   04940-004-0038003-02721
    Microsoft Team Explorer for Visual Studio 2012
    Visual Basic 2012   04940-004-0038003-02721
    Microsoft Visual Basic 2012
    Visual C# 2012   04940-004-0038003-02721
    Microsoft Visual C# 2012
    Visual C++ 2012   04940-004-0038003-02721
    Microsoft Visual C++ 2012
    Visual F# 2012   04940-004-0038003-02721
    Microsoft Visual F# 2012
    Visual Studio 2012 Code Analysis Spell Checker   04940-004-0038003-02721
    Microsoft® Visual Studio® 2012 Code Analysis Spell Checker
    Portions of International CorrectSpell™ spelling correction system © 1993 by Lernout & Hauspie Speech Products N.V. All rights reserved.
    The American Heritage® Dictionary of the English Language, Third Edition Copyright © 1992 Houghton Mifflin Company. Electronic version licensed from Lernout & Hauspie Speech Products N.V. All rights reserved.
    Visual Studio 2012 SharePoint Developer Tools   04940-004-0038003-02721
    Microsoft Visual Studio 2012 SharePoint Developer Tools
    NuGet Package Manager   2.2.31210.9045
    NuGet Package Manager in Visual Studio. For more information about NuGet, visit http://docs.nuget.org/.
    PreEmptive Analytics Visualizer   1.0
    Microsoft Visual Studio extension to visualize aggregated summaries from the PreEmptive Analytics product.
    SQL Server Data Tools   11.1.21208.0
    Microsoft SQL Server Data Tools
    Typemock AutoRunner   7.0
    Information about my package
    Web Developer Tools   1.0.30710.0
    Microsoft Web Developer Tools contains the following components:
    Page Inspector: Tool that offers an efficient way to decompose Web Applications and diagnose front-end issues.
    Web Publishing: Extensions required for Web Publishing for both hosted servers as well as on premises.
    Web Form Templates: Includes the default templates for Web Form Applications.
    Editor Extensions: Includes HTML, CSS, and JS editor extensions that greatly enhance the development experience.
    Windows Installer XML Toolset 3.6   
    Windows Installer XML Toolset 3.6.3303.0 
    Copyright © Outercurve Foundation. All rights reserved.
    SQL Prompt 5
    For more information about SQL Prompt, see the Red Gate website at
    http://www.red-gate.com
    For customer support, call 1-866-733-4283.
    Copyright © 2006–2009 Red Gate Software Ltd
    Typemock Isolator
    For more information about Typemock Isolator, see the Typemock website at
    http://www.Typemock.com
    For customer support, use the forums on the site
    Copyright (c) 2005 Typemock ltd.
    Typemock Productivity
    For more information about Typemock, see the Typemock website at
    http://www.typemock.com
    For customer support, contact us at support@typemock.com
    Copyright (c) 2004-2012 Typemock Ltd.

    Thanks for your assistance.

    Tuesday, January 08, 2013 7:53 PM
  •  

    We have been able to reproduce this issue in VS 2012. The custom ExpectedException attribute is not working in VS 2012 - we are tracking it. At this point, I can suggest following 3 workarounds for the issue. My recommendation is to use #2.

    1. Replace ExpectedSqlException attribute in your code with ExpectedException attribute. For example, you could use ExpectedException(typeof(System.Data.SqlClient.SqlException)). Note that you lose the ability to verify Severity, MessageNumber and State.

    2. If you need to verify Severity, MessageNumber and State, you can put BEGIN TRY and END TRY in our T-SQL code and in the catch block, select ERROR_SEVERITY(), ERROR_NUMBER() and ERROR_STATE(). You can then use ScalarValueCondition / DataChecksum in the test designer to verify the values.  Here is an example:

    If your original script is:

    RAISEERROR ('Error message.', 16, 1 ) WITH LOG;

    You can modify it to:

    BEGIN TRY

    RAISEERROR ('Error message.', 16, 1 ) WITH LOG;

    END TRY

    BEGIN CATCH

    SELECT ERROR_SEVERITY(), ERROR_NUMBER(), ERROR_STATE();

    END CATCH;

    3. This approach is an extension of #1. In your code file, use try catch in the TestMethod. In the catch block, verify the severity, state etc in the exception and then rethrow it so that the ExpectedException attribute ensures that an exception was actually raised during the execution.

    -Vikrant Arora [MSFT]

    • Marked as answer by Ken Powers Wednesday, January 09, 2013 3:19 PM
    Wednesday, January 09, 2013 1:00 AM
  • I am able to run the test using Method #2.  Can you forward a link where I can keep track of this bug?
    Wednesday, January 09, 2013 3:20 PM
  • Glad to hear that. Unfortunately, the internal bug cannot be tracked through a link. However, it is a high priority issue for us and we will be fixing it in our upcoming release.

    Wednesday, January 09, 2013 5:25 PM
  • A workaround for Visual Studio 2012 which doesn't require a code change is using TestSettings to execute unit tests

    •          Create a Test Settings: Right click on Solution -> New Item -> Test Settings
    •          Test Menu -> Test Settings ->  Select Test Settings File and point to the file just created

    This will force the unit tests to be executed in legacy mode and ExpectedSqlException works as expected.


    Thanks,
    Vinay


    Tuesday, February 26, 2013 9:48 AM