SQLCover Code Coverage for SQL Server T-SQL

Open Source code coverage tool for T-SQL, SQL Server 2008+

What is code coverage?

Code coverage is a way to see how many statements in your database code have been executed when you ran your tests(s). It is a way to see how well covered with tests a particular area is - the better covered with tests, the less likely you will miss issues when you make changes in those areas.

What is code coverage for SQL Server?

SQL Server gives us a great tracing mechanism, either profiler or extended events which details exactly which statements have been run. SQL Server doesn’t do so well at telling us what statements we could possibly run in some code but the Transact Sql Script Dom that is part of the DacFx does give us the ability to break T-SQL code into statements so combining the two we have the ability to take a stored procedure such as:

create procedure abc.def as

–select 100 from a_table but_is_commented_out_so_do_not_count_me
select 100;
select 200
select 300
begin
select 400;
end
if 1=2
begin
select 500
end

go

and we can use the scriptdom to tell us that there are 6 statements or “lines of code” in this procedure and when we run it while covering it we get the details that all of the statements apart from “select 500” are run which gives us a code coverage of 5 out of 6 statements.

Who decides what a statement is?

Partly me but mostly SQL Server itself, if you run a trace with SQL Server/sp_statement_xx you can pretty easily see that the select’s are statements, the begin/end by itself is not a statement and the if is a statement - this makes sense to me, the begin/end doesn’t actually do anything it is just to help you keep your code tidy, for example this is valid SQL and is only 1 statement:

begin begin begin begin select 'wowsers' end end end end

Where can this be used

This version of code coverage is a .net dll with some examples in powershell so it can be used with any build server or test framework, but it was written primarily for tSQLt and has some filtering to filter out tSQLt objects and test procedures so anything that is in a tSQLt test class will be ignored. It was also written to fit into the Redgate DLM automation suite so if you have that then I would recommend using it.

How do I use it?

This first example I will use the SQLRelease module from the DLM suite…

  • 1. Grab the zip from: https://github.com/GoEddie/SQLCover/releases/
  • 2. In powershell dot source the “SQLCover.ps1” file
  • 3. Call “Get-CoverRedgateCITest”, you will need to pass in the path to the “SQLCover.dll”, the connection string to the database, the path to the nuget package to test and a server and database where the code can be deployed to.
  • 4. The return from Get-CoverRedgateCITest is a 2 object array, the first is the Redgate results from the tests themselves “RedGate.SQLRelease.Compare.SchemaTesting.TestResults” which can output a junit xml file. The second is a SQLCover.CoverageResult object which as well as giving the count of statements in the database and covered statements while running the tests lets you output the code coverage results as xml or in the open coverage xml format which can be converted to a pretty report using the https://github.com/danielpalme/ReportGenerator tool.

In powershell code that is:

. .\SQLCover.ps1 $results = Get-CoverRedgateCITest ".\SQLCover.dll" "server=.;initial catalog=database_name;integrated security=sspi;" ".\path\to\nuget.nupkg" "." "database_name" Export-OpenXml $results[1] "c:\output\path\for\xml\results" Start-ReportGenerator "c:\output\path\for\xml\results" "c:\path\to\reportgenerator.exe"

You will need to obviously provide the correct paths, a nupkg file and if you want a pretty report then reportgenerator.exe.

If you don’t have SQLRelease then you can do the same thing but you must deploy the code yourself first:

  • 1. Grab the zip from: https://the.agilesql.club/SQLCover/download.php
  • 2. In powershell dot source the “SQLCover.ps1” file
  • 3. Call “Get-CoverTSql” this returns a CoverageResult object which as well as giving the count of statements in the database and covered statements while running the tests lets you output the code coverage results as xml or in the open coverage xml format which can be converted to a pretty report using the https://github.com/danielpalme/ReportGenerator tool.

In powershell code that is:

. .\SQLCover.ps1 $result = Get-CoverTSql ".\SQLCover.dll" "server=.;initial catalog=database_name;integrated security=sspi;" "database_name" "exec tSQLt.RunAll" Export-OpenXml $result "c:\output\path\for\xml\results" Start-ReportGenerator "c:\output\path\for\xml\results" "c:\path\to\reportgenerator.exe"

If you wanted to call an exe, for example an nunit test runner or msbuild then instead of calling Get-CoverTsql you can call Get-CoverExe which will start the coverage, then start the exe and stop the coverage when it completes. If you want to completely control when coverage sessions are started or stopped then SQLCover.dll has a .Start() and .Stop() methods, see Get-CoverRedgateCITest for examples.

What output do you get?

You get a CoverageResult object which has two public properties:

public long StatementCount; public long CoveredStatementCount;

These give you the ability to react to a poor coverage result or you can convert the CoverageResult to:

  • 1. Basic Html report “$result.Html()”
  • 2. Open Cover Xml format which can be converted to something pretty or you can parse it yourself

I would strongly suggest you grab a copy of reportgenerator so you get reports that look like:

Code coverage report generated using reportgenerator.exe

Code coverage detail generated using reportgenerator.exe

Oooh pretty….

SSDT Dev Pack

The ssdt dev pack already has code coverage but it is only within an ssdt project and it isn’t possible to generate code coverage results as part of a build process so although I will still work on that, I will migrate that over to using this at some point.

Finally.

I just wanted to say a big thanks to David Atkinson at Redgate, he sponsored the project which meant that I could focus on it and provided some great feedback and guidance along the way :)

Comments:

Shanmuga

June 2, 2016 - 13:32

SQL cover not working

Hi,
I am trying to run the following to test my tSQLT coverage.

$result = Get-CoverTSql “.\SQLCover.dll” “server=server_name;initial catalog=db_name;User id=uesr_namer;Password=password;” “db_name” “exec tSQLt.RunAll”
Export-OpenXml $result “c:\output\path\for\xml\results”

All I get is this error:

You cannot call a method on a null-valued expression.At C:\Users\\Desktop\SQLCover.0.2\SQLCover.ps1:89 char:5
+ $result.OpenCoverXml() | Out-File $xmlPath
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.At C:\Users\chitipir\Desktop\SQLCover.0.2\SQLCover.ps1:90 char:5
+ $result.SaveSourceFiles($outputPath)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

My guess that it it is not able to connect to the database, but it is not returning any error related to that. Generally it takes around 20 min to run tSQLt.RunAll, but when I run the above command within 1 second, I see the error.

Please advise. Thanks a lot.

Regards,
Shanmuga

Gaurav

September 1, 2016 - 00:11

By default, SQLCover creates

By default, SQLCover creates trace files in log directory and never gets cleared. I was facing this issue and I cleared all trace files from “D:\MSSQL\100\MSSQL10_50.MSSQLSERVER\MSSQL\Log” location and it started working again.

Regards,
Gaurav

Shanmuga

June 2, 2016 - 18:04

SQl cover- error

sorry , I typed my email address wrong in my previous comment related to
this error:

PS C:\Users\\SQLCover.0.2> $result = Get-CoverTSql “.\SQLCover.dll” “server=server;initial catalog=db;User id=user_name;Password=pwd;” “db” “exec tSQLt.RunAll”
Export-OpenXml $result “c:\output\path\for\xml\results”

You cannot call a method on a null-valued expression.At C:\Users\Desktop\SQLCover.0.2\SQLCover.ps1:89 char:5
+ $result.OpenCoverXml() | Out-File $xmlPath
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.At C:\UsersDesktop\SQLCover.0.2\SQLCover.ps1:90 char:5
+ $result.SaveSourceFiles($outputPath)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Please use this email address for your response.

Ed Elliott

June 20, 2016 - 22:03

Can you email me a copy of

Can you email me a copy of the schema of the project and test so I can test it?

Rod

June 16, 2016 - 06:39

Great stuff, Ed. Having this

Great stuff, Ed. Having this error coming up after running 100 tSQLts. Any ideas how to make it work? Must be hitting a limit somewhere in powershell.
`
[DBG]: PS P:>> $coverageResults = $coverage.Stop()
Exception calling “Stop” with “0” argument(s): “Exception of type ‘System.OutOfMemoryException’ was thrown.“At line:1 char:1

  • $coverageResults = $coverage.Stop()
  • CategoryInfo : NotSpecified: (:) [], MethodInvocationException
  • FullyQualifiedErrorId : OutOfMemoryException
    `

Ed Elliott

June 20, 2016 - 21:56

Can you send me the schema /

Can you send me the schema / sample to see if I can see why it is getting an OOM?

Gaurav

September 1, 2016 - 00:14

Hi,

Hi,

It’s a bug!! I looked at SQLCover.dll code from git and found that command timeout is set to 30 sec. So, it is quitting sql query after 30 sec.

Ed please fix it. It is deal backer for me.

Regards,
Gaurav

Slava

August 15, 2016 - 23:24

What do I do wrong?

Hello. I executed this
$result = Get-CoverTSql “.\SQLCover.dll” “server=.;initial catalog=db_name;integrated security=sspi;” “db_name” “exec tSQLt.RunAll” Export-OpenXml $result “D:\TSQL_Code_Coverage\results”

I can see that there was an event session started then stopped, actually all looks ok in the trace, but there is neither output nor results file, nor error, nor log. How can I troubleshoot?

Otto Barea

November 3, 2017 - 01:17

Perhaps this is the problem you are facing?

I started running the tool and it worked properly when connecting to my local DB.
At the moment I attempted to connect to the development DB, the tool simply could not connect.

So after looking at the source code I found that there is a trace mode.

Run the application with logs enabled and this is what I found:

PS C:\tsqlt>
PS C:\tsqlt> .\tsqlt_dv07.ps1
Starting Code Coverage
Error starting trace: System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object ‘xp_readerrorlog’, database ‘mssqlsystemresource’, schema ‘sys’. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

In other words, your account needs to have execute right on master.dbo.xp_readerrorlog system stored procedure.

To fix it, have the dba run the following command on your login:

GRANT EXECUTE ON master.dbo.xp_readerrorlog TO [login-name]

Pavel Vakar

August 26, 2016 - 18:14

Hi guys!

Hi guys!
Thank you for the great tool!

Is it any way to exclude system or custom sps from DB coverage calculation (like sp that support replication, or some test sps) ?
I don’t want to cover and see them. thanks

thanks

Billy Switzer

September 26, 2017 - 18:28

Exclude Procs

An admittedly tedious solution I found is to list each system proc individually, escaping the square brackets with a backslash:

$excludeArray = “\[dbo\].\[sp_sysproc1\]”,”\[dbo\].\[sp_sysproc2\]”
$coverage = new-object SQLCover.CodeCoverage($connectionString, $database, $excludeArray, $logging)

Hope this helps!

Billy

ronnie kapoor

March 25, 2018 - 11:43

How can i read this array of objects to exclude from a json ?

How can i read this array of objects to exclude from a json to automate things?

Luciano Evarist…

September 26, 2016 - 21:04

Is it possible to use SQLCover in SSDT projects

Is it possible to use SQLCover in SSDT projects? Maybe call SQLCover in pre-build and post-build event command line. I just don’t know how and if post-build would be triggered before or after tests are run.

Ed Elliott

September 27, 2016 - 19:15

Hi Luciano,

Hi Luciano,

I guess you could but then you would be monitoring the deployment which seems unusual!

The normal thing would be to do a deployment and then run your unit tests - if you either start SQLCover, then run your tests and then stop SQL Cover or use one of the built in helpers to run the tests it should work better?

Feel free to email/twitter me if you get stuck!

ed

Bishnu Priya

March 22, 2017 - 07:18

Hi Ed,

Hi Ed,

Could you explain in more detail about how this is to be done! I am stuck up.

Thanks!

Sachid

February 14, 2017 - 19:55

Error: Named SQL instance not working

Hi Team,

I am getting following error while starting SQL Cover Trace against SQL server with Named instance. Everything works fine if I start trace against default SQL instance. Please let me know any tricks so that we can start SQL trace against names SQL server instance

1] Code throws error
string SqlConnectionString = @“Server=<>\<>;initial catalog=<>;integrated security=sspi; “;

SQLCover.CodeCoverage obj = new SQLCover.CodeCoverage(SqlConnectionString, DbName);
obj.Start();

Error: An un-handled error occured. Please check configuration and retryExecution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

1] Code works fine

string SqlConnectionString = @“Server=<>;initial catalog=<>;integrated security=sspi”;

SQLCover.CodeCoverage obj = new SQLCover.CodeCoverage(SqlConnectionString, DbName);
obj.Start();

Regards,
Sachid

Bishnu Priya

March 22, 2017 - 07:15

Unable to use SQLCover for the db unit test cases in SSDT

Hi Ed,

I am unable to use SQLCover for the unit test cases that I have written in SSDT. I am creating an exe for the same but seems like things are not working out!

Thanks in advance fr the help:)

Ed Elliott

March 28, 2017 - 15:29

Hi Bishnu Priya,

Hi Bishnu Priya,

what is the exe? how are you using SQLCover - what’s not working?

Sravanthi Mailavaram

March 29, 2017 - 21:35

null-values expression

I was trying to use this tool for code coverage and when I try to run sqlcover.ps1, I am seeing

You cannot call a method on a null-valued expression.

+ $result.Html() | Out-File $xmlPath
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.

+ $result.SaveSourceFiles($outputPath)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Ed Elliott

April 24, 2017 - 19:11

Hi - it looks like $result is

Hi - it looks like $result is null - can you include a full demo? a github repo that can repro it would be best.

If you raise it as a github issue it is easier to help you:

https://github.com/goeddie

ed

Shradha Barpute

May 9, 2017 - 06:03

Get-CoverExe is not working when I run the tests using mstest

Hi Ed,

Following is the PowerShell command, that I am trying to run. I am sure there is some issue with syntax here. Could you please point it out.

$result = Get-CoverExe “.\SQLCover.dll” “server=server_name;initial catalog=db_name;User id=uesr_namer;Password=password;” “db_name” “exec cmd \mstest /testcontainer:..\myContainer.dll”

Thanks,
Shradha

Ed Elliott

May 31, 2017 - 12:14

the “exec cmd \mstest” doesn

the “exec cmd \mstest” doesn’t look right - I would try something like “%fullPathTo%\mstest.exe”

Ely Labovitz

July 18, 2017 - 13:56

SQL 2014 and SQL Cover

I created two identical databases. One on a SQL Server running SQL 2014. Once running SQL 2012. The coverage report on 2012 looks perfect. The coverage report on 2014 shows that most code is not covered. Is there a known issue with SQL 2014. Is there some configuration for 2014 that I need to enable in order to show the proper coverage? Thanks.

Otto Barea

November 3, 2017 - 01:32

Do you plan to add support to update project to latest nupkg?

Hi there,

Great tool!

I downloaded 4.1 source code to check on the debug logic.
I build it with VS2015 (.Net 4.5 target). While in there I updated all NUGET packages to latest release and the project broke for some of the bundled dlls.

SQLCover still works as is, so noting to do in this regard.
But I noticed that Microsoft released Microsoft.SqlServer.TransactSql.ScriptDom.14.0.3660.1.nupkg. The release bundles an older DLL file.

Quick question, do you plan to release an updated version that includes all latest and greatest nupkgs?

Once again, thanks a lot for a great work.

Regards,
Otto