Trouble Building with SQL CI in Jenkins

IKnealeIKneale Posts: 14
edited August 30, 2016 12:41PM in SQL CI 2
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.

Comments

  • Bump.

    Anyone have any suggestions? Thanks.
  • 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.
    Peter Gerrard

    Software Engineer
    Redgate Software
  • 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.
  • 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.
    Peter Gerrard

    Software Engineer
    Redgate Software
  • 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.
  • 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.
    Peter Gerrard

    Software Engineer
    Redgate Software
  • 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.
  • 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)
    Peter Gerrard

    Software Engineer
    Redgate Software
  • 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.
  • 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.
  • 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 [jenkins_test] 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 ([email protected])
    Peter Gerrard

    Software Engineer
    Redgate Software
  • 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.
  • Could you try running
    EXEC [tSQLt].[RunAll]
    
    against jenkins_test
    Peter Gerrard

    Software Engineer
    Redgate Software
  • 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?
  • 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 [email protected] for me to diagnose?
    Peter Gerrard

    Software Engineer
    Redgate Software
  • I have submitted an email to [email protected] and attached my NuGet package.
  • 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:
    1. 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
    2. 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
    3. Use the same repository for the scripts folder and binaries and update the locations as required similar to solution 2.
    Peter Gerrard

    Software Engineer
    Redgate Software
  • 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.
Sign In or Register to comment.