SQL Server Unit Test in Visual Studio: Unable to set test condition properties due to "Object reference not set to an instance of an object." error

Question

I've been experimenting with unit tests in Visual Studio for SQL Server Data Tools (SSDT) projects. I've found an issue in the unit test designer that I have no idea how to debug or resolve.

Background:

In Visual Studio 2019 I created a SSDT project and populated it by importing a database. I found the easiest way to create a separate unit test project capable of running SQL Server tests was to right click on a stored procedure in the SSDT project, and select Create Unit Tests..., then select the option to add the unit test to a new project.

To add a new unit test to the test project I right click the project, select Add > New Item... > SQL Server > Sql Server Unit Test.

The Issue:

I was trying to add a Scalar Value test condition to my first SQL Server Unit Test. When I tried to set the Expected Value in the Properties window an error dialog appeared, saying "Property value is not valid". The detailed message was "Object reference not set to an instance of an object."

I found that trying to set any property in the Properties window resulted in the same error. However, there was no indication of what the object was that was null.

I've been able to work around the problem by setting the property value in the underlying C# class, instead of using the Properties window. If I do that the test runs successfully. So it appears the problem is confined to the test designer.

The issue is not confined to just Scalar Value test conditions. I ran into the same problem with a Row Count condition as well - I couldn't set any property in the Properties window because whenever I did the error dialog prevented me from changing the value.

Has anyone come across a similar issue and found a fix for it?

Simon Tewsi Simon Tewsi · Accepted Answer · 2020-10-19T20:17:09

It turns out the issue was caused by a mismatch in the ToolsVersion attribute in the two project files: The SSDT project and the unit test project.

I got the hint the ToolsVersion might be important from this MSDN thread: https://social.msdn.microsoft.com/Forums/en-US/40abddf1-efd8-49cb-afe0-dc1aef6c1492/ssdt-unit-tests-vs2019-object-reference-not-set-to-an-instance-of-an-object?forum=vsunittest

I unloaded then edited each project in Visual Studio. I could see the ToolsVersion of the SSDT project was "4.0", even though it was created from scratch in Visual Studio 2019, while the ToolsVersion of the unit test project created from the SSDT project was "15.0".

I set the ToolsVersion of the SSDT project to "15.0" to match the unit test project.

Once I reloaded each project I was able to use the test designer to set test condition properties without error.

To be clear about what I'm talking about, here's a cut down version of the SSDT project file. You can see the ToolsVersion attribute near the top in the Project tag:

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="15.0">
  <PropertyGroup>
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
    <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
    <Name>SQlServerUnitTestingDemo</Name>
    ...
  </PropertyGroup>
  ...
</Project>

SQL Server Unit Test in Visual Studio: Unable to set test condition properties due to "Object reference not set to an instance of an object." error

1 Answers