Validate syntax


In some cases, it’s useful to just ensure that the syntax of a query is valid. It’s often the case for queries stored in reports or automatically generated by some .Net assemblies. When applying some renamings to the database or the cube, we often forget to apply these renamings to the corresponding reports.

NBi will not execute your query but will ask SQL Server to check the syntax of your query (sql, mdx or dax). It means that if your query is a truncate or an insert, don’t panic, your query will not be executed! The content of your database will not be updated even temporary. To achieve this, NBI doesn’t make usage of transactions but rather rely on specific commands.

If some errors are found during the syntax parsing, the test result will mention them.

For this kind of test, you’ll need to define your system under test as a query and your assertion as an syntacticallyCorrect constraint.

System Under Test

If you’re not familiar with how to setup a system-under-test as a query, please read first the documentation about Test equivalence of two result sets

Assert

Once you system under test is defined, you’ll need to specify that you want to assert the syntax of your query. This done by the xml element named syntacticallyCorrect

<assert>
	<syntacticallyCorrect/>
</assert>

A whole example would be:

<test name="...">
    <system-under-test>
        <execution>
            <query name="MDX" connectionString="...">
                SELECT
                    [Measures].[Reseller Order Count] ON 0,
                    EXCEPT(
                       {[Date].[Calendar Year].Children}
                       ,{[Date].[Calendar Year].[CY 2006]}
                    ) ON 1
                FROM
                    [Adventure Works]
            </query>
        </execution>
    </system-under-test>
    <assert>
        <syntacticallyCorrect/>
    </assert>
</test>