Trouble Building with SQL CI in Jenkins

IKneale · April 28, 2016 5:28AM

Hello,

I am looking into my companies database life cycle and have been attempting to trial the DLM Automation Suite. I have reached the point of attempting to build, test and deploy using SQL CI integreated in to Jenkins using our local SVN repository as per this tutorial.

It seems to build the NuGet package okay, however I get the following error when it comes to the testing step and I am unsure how to proceed:

Error: Comparison of 'Scripts.state' and
'Server.sqlCI_a974e875-4ef8-456e-b3a8-970aa4dba282' failed: Could not
find a part of the path 'C:WindowsTEMPSQL CImyidtvcu.lc2dbstate'.

The command I run is:

sqlci.exe Test /temporaryDatabaseServer=Server /temporaryDatabaseUserName=*** /temporaryDatabasePassword="*******" /package=E:PackagesNewCIPackage.1.%BUILD_NUMBER%.nupkg

Did I install incorrectly and I am missing a file? Am I missing a parameter to pass in or pass one in incorrectly? I have attempted searching for this exact error but could not find anything, I am not familiar with this software enough to hazard any guesses. Any help would be greatly appreciated.

Thank you.

IKneale · May 3, 2016 3:36AM

Bump.

Anyone have any suggestions? Thanks.

PeterG · May 3, 2016 10:59AM

Hi,

This likely because the NuGet package is being built incorrectly. Do you have the command that is called in build?

You can also look at the contents of the zip folder to see if it looks correct (My guess is that it empty aside from a file in the root). You can either use NuGet package explorer or just change the extension to .zip and unzip it.

IKneale · May 4, 2016 5:46AM

Thanks for the reply. The command I use to build is:

sqlci.exe Build /scriptsFolder=ScriptsFolder /packageId=NewCIPackage /packageVersion=1.%BUILD_NUMBER% /temporaryDatabaseServer=Server /temporaryDatabaseUserName=*** /temporaryDatabasePassword="****" /outputFolder=E:Packages

I do not believe the built package is empty. I see "NewCIPackage.nuspec" within it and three folders.

PeterG · May 4, 2016 6:53AM

The build command looks right, but the test command cannot seem to find the db/state folder inside the package. This would happen if the original scripts folder was empty.Could you check to see if that folder exists?

This could happen if you point the build step at the wrong folder, in particular from the blog post:

Note: for the /scriptsfolder= argument you should include the relative path from where you told Jenkins your source repo was to the folder your scripts folders are stored.

This would also happen if the ScriptsFolder had no files in initially.

IKneale · May 4, 2016 7:20AM

You are correct, the Scripts folder contains nothing. Forgive my ignorance, but what should be in the scripts folder? Where do these scripts originate from? Are they auto-generated by the build and I'm simply pointing to the wrong folder, or should I have provided them through some other mechanism?

Thanks.

PeterG · May 4, 2016 7:29AM

These scripts are the ones produced by SQL Source Control (essentially a bunch of .sql files defining the creation scripts of each object). If you don't have access to SOurce Control there is a free trial for it (http://www.red-gate.com/products/sql-de ... e-control/) or you can use sql compare to create a scripts folder of an existing database.

If you just want to get the build working without having to set up the scripts folder with the correct creation files then you can just put an empty file into the scripts folder and check that into source control.

IKneale · May 4, 2016 10:52AM

Ah, I think I understand now, thank you! It appears to build correctly now.

However now I receive the following error:

Running unit tests failed with error: The package does not contain the tSQLt schema or tests.

The obvious answer would be that I haven't installed the tSQLt schema onto the database and committed them to SVN, however I have and I can see the tSQLt objects in Source Control. Is there another step?

Thanks.

PeterG · May 4, 2016 11:01AM

Can you check if the tSQLt files are stored in source control and the NuGet package it might be you have set the option to ignore them in Source Control. A good indicator is the file `SecuritySchemas SQLt.sql`.

You also require at least one test to be run otherwise you get this error. (You can either look in the folder or use SQL Test in SSMS to run all tests to see if there any)

IKneale · May 6, 2016 4:42AM

When I right click and run tests there are at least 30 "default" tests that appear to run, and is all committed to Source Control. This is most peculiar.

IKneale · May 6, 2016 5:55AM

I re-tried by creating a new database and installing the tSQLt objects before even linking it to Source Control however I'm still getting the same error.

PeterG · May 6, 2016 6:10AM

Here's a couple of things you can try:

Investigate the test database

On the server where you are doing the test add a new database "jenkins_test"

Run the following sql

ALTER DATABASE &#91;jenkins_test&#93; SET TRUSTWORTHY ON;
sp_configure 'clr enabled', 1;
RECONFIGURE;

Modify your test step to add the parameter "/temporaryDatabaseName=jenkins_test"
Run the jenkins build
Try and run the tests manually on jenkins_test

Try using the SQL CI Jenkins plugin
We also have a Jenkins plugin that may be of use https://wiki.jenkins-ci.org/display/JEN ... +CI+Plugin

Hopefully one of these will help fix or diagnose your issue. If not would you be willing to share your NuGet package with me (DLMAutomationSupport@red-gate.com)

IKneale · May 6, 2016 8:09AM

I attempted what you suggested, however I am still getting the same error as before, even though the jenkins_test database literally only contains the tSQLt objects!

I am wondering, as I call sqlci.exe from the same SVN directory containing all of the schema objects, could that interfere somehow and cause it not to "see" the tSQLt schema? I'm grasping at straws here.

As you can tell I'm not overly familiar with Jenkins either, I'm afraid.

PeterG · May 6, 2016 8:19AM

Could you try running

EXEC &#91;tSQLt&#93;.&#91;RunAll&#93;

against jenkins_test

IKneale · May 6, 2016 9:31AM

Yes, that ran, though I do get this summary message:

Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 48 test case(s) executed, 18 succeeded, 30 failed, 0 errored.

Although I would have thought the error message would be different it some tests merely failed, not that it couldn't find the tests at all?

PeterG · May 6, 2016 9:42AM

I am struggling to try and work out why this is failing for you. Would you be able to send a copy of the NuGet package to DLMAutomationSupport@red-gate.com for me to diagnose?

IKneale · May 9, 2016 5:40AM

I have submitted an email to DLMAutomationSupport@red-gate.com and attached my NuGet package.

PeterG · May 19, 2016 6:05AM

The solution to the original issue was doing a double checkout in Jenkins, and the sqlci exe was overwriting the scripts folder. The solution was to do one of the following:

Do not include the executable in source control and instead install DLM automation onto each agent. Then update the calls to reference the installed location of sqlci.exe
Check the binaries and scripts folder into separate folders eg. sqlci and ScriptsFolder, then modify the steps to call sqlcisqlci.exe, and for the build step set the /scriptsFolder=ScriptsFolder
Use the same repository for the scripts folder and binaries and update the locations as required similar to solution 2.

belgrave · August 30, 2016 12:41PM

I'm also having this issue using SQL CI in Jenkins. Was there a solution? Thanks

Running unit tests failed with error: The package does not contain the tSQLt schema or tests.

Also, if I run EXEC [tSQLt].[RunAll] I get:

Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 48 test case(s) executed, 18 succeeded, 30 failed, 0 errored.

Trouble Building with SQL CI in Jenkins

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk