Code Coverage 0.0% for TSQL Projects(Contains Stored Procedures, Tables, Scripts etc)

We are trying to capture the Code Coverage metrics in SonarQube for SQL Projects but it doesn’t seem to be working.

We are able to scan the SQL Project repository and get metrics around Bugs, Code Smells, Duplications but not Code Coverage.

We are using tSQLt for writing Unit Tests for our SQL Projects. We have generated the Code Coverage file using SQLCover. However SonarQube cant read this coverage file and generate the code coverage metrics.

Any suggestions/steps to coverage TSQL projects in Sonar Cloud will be appreciated.

Any help you can give would be greatly appreciated.

We have used MSTest as testing framework for writing all Stored Procedures unit test case for TSQL projects.
Dynamic Code Coverage Tools > CodeCoverage.exe : Should the sonar cloud will cover the code coverage for all Stored Procedures?

Its showing now 0.0% coverage for all Stored Procedures though all are having the valid Unit Test cases.

Is it a bug or something wrong we are doing as part of code coverage process.

Below steps followed as part of the code coverage Azure/Sonar configuration.

  • Used task with standalone scanner to run the analysis
  • Task to generate .trx (test result) file and coverage file
  • Convert Coverage File into .coverageXML using CodeCoverage.exe tool
  • Task to upload the .coverageXML results into the SonarCloud

Can you please clarify what you mean by “cant read this coverage file”? Did an error message tell you this in the output of the scanner? Can you please copy paste the line with the message, and any relevant lines before it?

Can you please clarify how you did this last step?

Can you share the complete scanner command used? And any additional configuration related to running the scanner, for example in sonar-project.properties file.

Hello @janos

Thanks for reaching us to provide quick guidance. We are doing the below process

Implementing code coverage for Microsoft SQL Server Database Project

How we are doing?

  1. “SQL Server Database” Project which contains stored procedure inside “Transact-SQL” script file
  2. “MSTest Unit Test” project which contains unit tests for Sql files ( Overview -Test cases to execute the stored procedure by passing its parameter values)
  3. Upload the results to the SonarCloud server using azure devops classic build pipeline as follows
  4. “Prepare analysis on sonarcloud ” task with standalone scanner to run the analysis
  5. “Visual Sudio Test” task to generate trx(test result) file and coverage file
  6. Convert Coverage File into CoverageXML using CodeCoverage.exe
  7. “Run Code Analysis” task to upload the CoverageXML results to the SonarCloud server
    Sonar Cloud Code Coverage Report:

Expected: Expected 100% coverage to Transact-SQL script file as we written unit test case for stored procedure execution.

Actual : Showing 0.0% Coverage to Transact-SQL script file. We gone through the file and we noticed that with highlighted red color saying “Not covered by Tests” in every SQL (Ex: Select) statement and so on…

HELP NEEDED TO KNOW : Does sonar cloud code coverage of Transact-SQL script file cover as a whole or Individual statement inside the file?

In order to see test coverage data on SonarCloud, the following is needed:

  • Test coverage report files, in some format supported by SonarCloud.
  • The scanner needs to find the report files to import them. Usually it can find the report files at typical locations, when in doubt, it’s best to specify the locations explicitly.

To answer the first requirement (report files in some supported format), I reviewed our documentation on Test Coverage, which mentions nothing about SQL. Indeed our teams confirm that our TSQL plugin doesn’t have specific logic to directly import TSQL coverage reports such as the ones mentioned in this thread.

In such case, the alternative is to use Generic Test Data. This is a generic format that we support, explained on that doc page. That is, in order to see test coverage data for TSQL, you need to convert your reports from coveragexml format to this custom format, and tell the scanner the path to the generated report files using the sonar.coverageReportPaths parameter.

A quick search led me to this tool:

I’m not sure if this will work with your current report format, or if changing your report format to OpenCover is possible for you. I hope this may help you get started.

Thanks @janos for your quick help. We will explore all the possible ways you mentioned and will comeback with our outcome soon.