SQL Automation Example

SteveGTRSteveGTR Posts: 91
edited October 21, 2013 7:51AM in Deployment Manager
I'm setting up the WidgetShop SQL automation test against TFS as documented on http://documentation.red-gate.com/displ ... th+TFS2012.

Currently we're using TFS 2010, but the processing seems to work fine as far as matching up with the instructions as you've documented.

The processing is currently failing. Here is the sclCI.log file:

Start of insert
Build started 10/16/2013 1:33:34 PM.
Project "C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj" on node 1 (default targets).
sqlCI:
Starting: (echo set REDGATE_SEND_ERROR_REPORTS=true & echo set RUNNING_IN_MSBUILD=true & echo sqlCI.exe "/scriptsFolder:WidgetShop\Database\Scriptsfolder" "/databaseServer:(local)" "/databaseName:WidgetShopCI" "/databaseIntegratedAuthentication" "/enableTsqlt" "/outputFolder:C:\Builds\1\WidgetShop\WidgetDevelopment\Binaries\") | cmd.exe
(echo set REDGATE_SEND_ERROR_REPORTS=true & echo set RUNNING_IN_MSBUILD=true & echo sqlCI.exe "/scriptsFolder:WidgetShop\Database\Scriptsfolder" "/databaseServer:(local)" "/databaseName:WidgetShopCI" "/databaseIntegratedAuthentication" "/enableTsqlt" "/outputFolder:C:\Builds\1\WidgetShop\WidgetDevelopment\Binaries\") | cmd.exe
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Builds\1\WidgetShop\WidgetDevelopment\Sources>set REDGATE_SEND_ERROR_REPORTS=true

C:\Builds\1\WidgetShop\WidgetDevelopment\Sources>set RUNNING_IN_MSBUILD=true

C:\Builds\1\WidgetShop\WidgetDevelopment\Sources>sqlCI.exe "/scriptsFolder:WidgetShop\Database\Scriptsfolder" "/databaseServer:(local)" "/databaseName:WidgetShopCI" "/databaseIntegratedAuthentication" "/enableTsqlt" "/outputFolder:C:\Builds\1\WidgetShop\WidgetDevelopment\Binaries\"
sqlCI.exe -- Red Gate's SQL Continuous Integration v1.0.1.546

EXEC : sqlCI error occurred: Parsing commandline options failed: [C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj]
EXEC : sqlCI error occurred: Couldn't find output folder C:\Builds\1\WidgetShop\WidgetDevelopment\Binaries" . An error occurred while trying to resolve the full path: Illegal characters in path. [C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj]

C:\Builds\1\WidgetShop\WidgetDevelopment\Sources>
C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.targets(173,5): error MSB3073: The command "(echo set REDGATE_SEND_ERROR_REPORTS=true & echo set RUNNING_IN_MSBUILD=true & echo sqlCI.exe "/scriptsFolder:WidgetShop\Database\Scriptsfolder" "/databaseServer:(local)" "/databaseName:WidgetShopCI" "/databaseIntegratedAuthentication" "/enableTsqlt" "/outputFolder:C:\Builds\1\WidgetShop\WidgetDevelopment\Binaries\") | cmd.exe" exited with code -1. [C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj]
Done Building Project "C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj" (default targets) -- FAILED.

Build FAILED.

"C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj" (default target) (1) ->
(sqlCI target) ->
EXEC : sqlCI error occurred: Parsing commandline options failed: [C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj]
EXEC : sqlCI error occurred: Couldn't find output folder C:\Builds\1\WidgetShop\WidgetDevelopment\Binaries" . An error occurred while trying to resolve the full path: Illegal characters in path. [C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj]
C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.targets(173,5): error MSB3073: The command "(echo set REDGATE_SEND_ERROR_REPORTS=true & echo set RUNNING_IN_MSBUILD=true & echo sqlCI.exe "/scriptsFolder:WidgetShop\Database\Scriptsfolder" "/databaseServer:(local)" "/databaseName:WidgetShopCI" "/databaseIntegratedAuthentication" "/enableTsqlt" "/outputFolder:C:\Builds\1\WidgetShop\WidgetDevelopment\Binaries\") | cmd.exe" exited with code -1. [C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj]

0 Warning(s)
3 Error(s)

Time Elapsed 00:00:07.34
---- End of insert

I was able to drop down to DOS and run the following steps to recreate the problem from the C:\Builds\1\WidgetShop\WidgetDevelopment\Sources directory:

set REDGATE_SEND_ERROR_REPORTS=true
set RUNNING_IN_MSBUILD=true
sqlCI.exe "/scriptsFolder:WidgetShop\Database\Scriptsfolder" "/databaseServer:(local)" "/databaseName:WidgetShopCI" "/databaseIntegratedAuthentication" "/enableTsqlt" "/outputFolder:C:\Builds\1\WidgetShop\WidgetDevelopment\Binaries\"
sqlCI.exe -- Red Gate's SQL Continuous Integration v1.0.1.546

I can get it to work by changing the outputFolder in the command to:

C:\Builds\1\WidgetShop\WidgetDevelopment\Binaries

I've removed the trailing slash and the processing works great, but doesn't continue with the build processing.

How can I correct this?

Thanks,
Steve

Comments

  • I was able to get around this error by manually setting the outputdirectory in the target file and creating the directory on the build agent.

    Everything appeared to work correctly, but after it finished the only thing in my deployment feed (Deployment Manager) was the log file.

    It looked like it only added the changes to the local database WidgetShopCI database. I was expected a nuget package that can be picked up by the Deployment Manager.

    Am I missing the point here?
  • Got a nuget package by specifying both the packageID and packageVersion fields in the target file.

    Excuse me, but this process seems very complicated to simply create a package for the deployment manager.

    The package isn't being placed in the feed, it's sitting in the hard-coded build folder.

    The comments for the packageVersion state:

    "On TFS versions 2010 and later, follow the instructions here: www.red-gate.com/buildnumbertfs and enter $(BuildNumber)"

    I can't access the page, it asks for a login account. I attempt to register and confirm and I'm still not allowed access.

    Tried just the $(BuildNumber) and that got me back to the error about the packageVersion being required.

    I ended up hard-coding a 1 and that seemed to work.

    Like I said before, there has got to be a better way to do this. If forced to do this, I'd rather just manually generate nuget packages using the SSMS plug-in process.

    Why not just have a process that automates the local database and SSMS plug-in process that isn't like an 18-sided rubics cube?

    Yes, I'm frustrated. Sorry for the venting... and I'm going to ask my bosses to shell out another $800 for this when we have 10 source controls and 2 SQL developer pack Red Gate licenses?
  • Hi Steve, unfortunately that link was an out-of-date redirect. We have fixed it, but your browser may have cached the redirect. If it still doesn't work for you, the documentation can be found at http://documentation.red-gate.com/display/sap10/Passing+the+%24BuildNumber+varable+in+MSBuild+to+TFS+2012+and+2010

    Regarding the difficulty you are experiencing creating packages with SQL CI, we are currentlty looking at adding the packaging functionality to the rgpublish tool that ships with deployment manager.

    Robin
    Robin Hellen
    Test Engineer
    DLM Automation
  • Thanks Robin and sorry for the venting. I'm a big fan of Red Gate and was just a little disappointed. Keep up the good work.

    Any idea when this change to RGPublish will be available?
  • Tried your suggested change and that fails. Here's the log:

    Start of log
    Build started 10/17/2013 2:41:40 PM.
    Project "C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj" on node 1 (default targets).
    sqlCI:
    Starting: (echo set REDGATE_SEND_ERROR_REPORTS=true & echo set RUNNING_IN_MSBUILD=true & echo sqlCI.exe "/scriptsFolder:WidgetShop\Database\Scriptsfolder" "/databaseServer:(local)" "/databaseName:WidgetShopCI" "/databaseIntegratedAuthentication" "/packageId:WidgetShop.nupkg" "/packageVersion:WidgetDevelopment_20131017.7" "/outputFolder:c:\MyBuild" "/enableTsqlt") | cmd.exe
    (echo set REDGATE_SEND_ERROR_REPORTS=true & echo set RUNNING_IN_MSBUILD=true & echo sqlCI.exe "/scriptsFolder:WidgetShop\Database\Scriptsfolder" "/databaseServer:(local)" "/databaseName:WidgetShopCI" "/databaseIntegratedAuthentication" "/packageId:WidgetShop.nupkg" "/packageVersion:WidgetDevelopment_20131017.7" "/outputFolder:c:\MyBuild" "/enableTsqlt") | cmd.exe
    Microsoft Windows XP [Version 5.1.2600]
    (C) Copyright 1985-2001 Microsoft Corp.

    C:\Builds\1\WidgetShop\WidgetDevelopment\Sources>set REDGATE_SEND_ERROR_REPORTS=true

    C:\Builds\1\WidgetShop\WidgetDevelopment\Sources>set RUNNING_IN_MSBUILD=true

    C:\Builds\1\WidgetShop\WidgetDevelopment\Sources>sqlCI.exe "/scriptsFolder:WidgetShop\Database\Scriptsfolder" "/databaseServer:(local)" "/databaseName:WidgetShopCI" "/databaseIntegratedAuthentication" "/packageId:WidgetShop.nupkg" "/packageVersion:WidgetDevelopment_20131017.7" "/outputFolder:c:\MyBuild" "/enableTsqlt"
    sqlCI.exe -- Red Gate's SQL Continuous Integration v1.0.1.546

    EXEC : sqlCI error occurred: Parsing commandline options failed: [C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj]

    C:\Builds\1\WidgetShop\WidgetDevelopment\Sources>
    EXEC : sqlCI error occurred: Package version: WidgetDevelopment_20131017.7 is not in a valid format. See www.red-gate.com/dm/versionsyntax for more details [C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj]
    C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.targets(173,5): error MSB3073: The command "(echo set REDGATE_SEND_ERROR_REPORTS=true & echo set RUNNING_IN_MSBUILD=true & echo sqlCI.exe "/scriptsFolder:WidgetShop\Database\Scriptsfolder" "/databaseServer:(local)" "/databaseName:WidgetShopCI" "/databaseIntegratedAuthentication" "/packageId:WidgetShop.nupkg" "/packageVersion:WidgetDevelopment_20131017.7" "/outputFolder:c:\MyBuild" "/enableTsqlt") | cmd.exe" exited with code -1. [C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj]
    Done Building Project "C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj" (default targets) -- FAILED.

    Build FAILED.

    "C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj" (default target) (1) ->
    (sqlCI target) ->
    EXEC : sqlCI error occurred: Parsing commandline options failed: [C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj]
    EXEC : sqlCI error occurred: Package version: WidgetDevelopment_20131017.7 is not in a valid format. See www.red-gate.com/dm/versionsyntax for more details [C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj]
    C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.targets(173,5): error MSB3073: The command "(echo set REDGATE_SEND_ERROR_REPORTS=true & echo set RUNNING_IN_MSBUILD=true & echo sqlCI.exe "/scriptsFolder:WidgetShop\Database\Scriptsfolder" "/databaseServer:(local)" "/databaseName:WidgetShopCI" "/databaseIntegratedAuthentication" "/packageId:WidgetShop.nupkg" "/packageVersion:WidgetDevelopment_20131017.7" "/outputFolder:c:\MyBuild" "/enableTsqlt") | cmd.exe" exited with code -1. [C:\Builds\1\WidgetShop\WidgetDevelopment\Sources\sqlCI.proj]

    0 Warning(s)
    3 Error(s)

    Time Elapsed 00:00:01.03
    End of log

    Problem seems to be because the BuildNumber that is formatted in the DefaultTemplate.xaml of "WidgetDevelopment_20131017.7" is incompatible with what your tool sqlCI.exe expects for the packageVersion parameter. You even have a reference to www.red-gate.com/dm/versionsyntax that outlines that version numbers must have the format n.n.n.n (or n.n.n, n.n, n). The "WidgetDevelopment_20131017" portion is invalid according to your tool's requirements. I can change the xaml format definition, but then the build Name displayed in the Build Explorer is n (for instance 8 ).
  • I was able to coax an acceptable build number out of the xaml processing that uses the date and the revision number in the format of yyyymmdd.n. This is the currently formatted build number minus the leading name (BuildDefinitionName) that your Red Gate tool finds so offensive.

    I changed all references (2 total) of:

    "[String.Format("/p:SkipInvalidConfigurations=true /p:BuildNumber={1} {0}", MSBuildArguments, BuildDetail.BuildNumber)]"

    to:

    "[String.Format("/p:SkipInvalidConfigurations=true /p:BuildNumber={1} {0}", MSBuildArguments, BuildDetail.BuildNumber.SubString(BuildDetail.BuildNumber.IndexOf("_") + 1))]"
  • Thanks for posting back Steve, and sorry you had some trouble getting that working. Looks like we need to improve on the documentation to clarify the example perhaps, I'll let the DM team know to check it over.
    Systems Software Engineer

    Redgate Software

Sign In or Register to comment.