"ReadyRoll Run tSQLt Tests" task in VSTS: Errors looking for RunTests.sql and OutputResults.sql

SWalters

Hello all,

I've set up a ReadyRoll database project in VS 2017 with tSQLt and some unit tests, which I'd like to have run during the build process. I'm using VSTS for CI build purposes, and have added the "ReadyRoll Run tSQLt Tests" task to the build definition.

Unfortunately, when the step is executed, the following error messages are produced. I've searched for info on where these two files need to be, and all I found was a StackOverflow thread that doesn't provide a definitive answer. Can someone enlighten me as to where the documentation on RunTests.sql and OutputResults.sql may be found? Where are the two files expected to be??

2017-12-17T14:14:51.6651310Z ##[section]Starting: Run tSQLt Tests on Unit Test Server
2017-12-17T14:14:51.6651310Z ==============================================================================
2017-12-17T14:14:51.6651310Z Task         : ReadyRoll Run tSQLt Tests
2017-12-17T14:14:51.6651310Z Description  : Run and output tSQLt database unit tests
2017-12-17T14:14:51.6651310Z Version      : 0.7.2
2017-12-17T14:14:51.6651310Z Author       : Redgate Software
2017-12-17T14:14:51.6651310Z Help         : [More Information](https://www.red-gate.com/readyroll)
2017-12-17T14:14:51.6651310Z ==============================================================================
2017-12-17T14:14:55.2214358Z ##[command]"sqlcmd.exe" -S sqldev.westus2.cloudapp.azure.com,1433 -d TestDB -U <username> -P <password> -i 'RunTests.sql'
2017-12-17T14:14:56.1965577Z Sqlcmd: ''RunTests.sql'': Invalid filename.
2017-12-17T14:14:56.3058822Z ##[command]"sqlcmd.exe" -S sqldev.westus2.cloudapp.azure.com,1433 -d TestDB -U <username> -P <password> -i 'OutputResults.sql' -o C:\agent\_work\4\s\tsqlt-test-results.xml
2017-12-17T14:14:56.3996295Z Sqlcmd: ''OutputResults.sql'': Invalid filename.
2017-12-17T14:14:57.3706897Z ##[error]Cannot find path 'C:\agent\_work\4\s\tsqlt-test-results.xml' because it does not exist.
2017-12-17T14:14:57.4332114Z ##[section]Finishing: Run tSQLt Tests on Unit Test Server

Sergio R

Hi,

Here's the link for a documentation page with a tutorial on how to use this add-on:
https://documentation.red-gate.com/rr1/worked-examples/tutorial-integrating-tsqlt-tests-with-visual-studio-team-services

I suspect that this might be related to how you have setup your phase, it should be set to run on the agent:
https://docs.microsoft.com/en-us/vsts/build-release/concepts/process/phases#agent-phase

Thank you,

SWalters

Hello Sergio, thanks for the response.

However, I've already seen the tutorial for the add-on. It doesn't address the issues I mentioned in the original post. And the phase in which this error occurs is not agentless.

A bit of digging reveals that this task is running a PowerShell script, RunDatabaseTests.ps1, found in the _work/_tasks/RunDatabaseTests_<guid>/<version> folder. The two required sql files (RunTests.sql and OutputResults.sql) are in this folder. However, the syntax of the command is off. As it's a PowerShell script... there's a problem going on with the quoting. If I log in to my build server and run this from the command line (without the single quotes around the file names and referencing the relative paths for the required files), it works fine.

For reference - I'm not the only one who's found this problem with the task's script syntax. It's also mentioned here.
https://stackoverflow.com/questions/47711693/how-to-integrating-tsqlt-tests-with-visual-studio-team-services

Greg_Smulko

This issue is now fixed in the version 2.1.4 of VSTS ReadyRoll extension.

The problematic lines:

Invoke-VstsTool -FileName 'sqlcmd.exe' -Arguments "-S $databaseServer -d $databaseName -i 'RunTests.sql'"
Invoke-VstsTool -FileName 'sqlcmd.exe' -Arguments "-S $databaseServer -d $databaseName -i 'OutputResults.sql' -o $outputPath"

were replaced by:

$connectionParams = "-S ""$databaseServer"" -d ""$databaseName"""
Invoke-VstsTool -RequireExitCodeZero -FileName 'sqlcmd.exe' -Arguments "-b $connectionParams -i RunTests.sql"
Invoke-VstsTool -RequireExitCodeZero -FileName 'sqlcmd.exe' -Arguments "-b $connectionParams -i OutputResults.sql -o ""$outputPath"""

Apologies for any inconvenience caused.

SWalters

Greg: Thanks for the update.

I've just tried a build with the updated task and it is working as expected. Awesome!