SQL Automation Example

Automated deployment for web applications and databases

SQL Automation Example

Postby SteveGTR » Wed Oct 16, 2013 6:59 pm

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
SteveGTR
 
Posts: 69
Joined: Wed Jan 11, 2012 11:19 pm

Postby SteveGTR » Wed Oct 16, 2013 7:22 pm

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?
SteveGTR
 
Posts: 69
Joined: Wed Jan 11, 2012 11:19 pm

Postby SteveGTR » Wed Oct 16, 2013 9:41 pm

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?
SteveGTR
 
Posts: 69
Joined: Wed Jan 11, 2012 11:19 pm

Postby robin » Thu Oct 17, 2013 1:43 pm

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
Deployment Manager
robin
 
Posts: 20
Joined: Fri Nov 16, 2007 12:02 pm

Postby SteveGTR » Thu Oct 17, 2013 1:57 pm

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?
SteveGTR
 
Posts: 69
Joined: Wed Jan 11, 2012 11:19 pm

Postby SteveGTR » Thu Oct 17, 2013 7:46 pm

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 ).
SteveGTR
 
Posts: 69
Joined: Wed Jan 11, 2012 11:19 pm

Postby SteveGTR » Thu Oct 17, 2013 10:00 pm

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))]"
SteveGTR
 
Posts: 69
Joined: Wed Jan 11, 2012 11:19 pm

Postby james.billings » Mon Oct 21, 2013 12:51 pm

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.
james.billings
 
Posts: 1120
Joined: Wed Jun 16, 2010 11:10 am
Location: Red Gate


Return to Deployment Manager

Who is online

Users browsing this forum: No registered users and 0 guests