Red Gate forums :: View topic - SQL Automation Example
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
Deployment Manager
Deployment Manager forum

SQL Automation Example

Search in Deployment Manager forum
Post new topic   Reply to topic
Jump to:  
Author Message
SteveGTR



Joined: 11 Jan 2012
Posts: 66

PostPosted: Wed Oct 16, 2013 6:59 pm    Post subject: SQL Automation Example Reply with quote

I'm setting up the WidgetShop SQL automation test against TFS as documented on http://documentation.red-gate.com/display/SAP10/Setting+up+continuous+integration+with+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
Back to top
View user's profile Send private message
SteveGTR



Joined: 11 Jan 2012
Posts: 66

PostPosted: Wed Oct 16, 2013 7:22 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
SteveGTR



Joined: 11 Jan 2012
Posts: 66

PostPosted: Wed Oct 16, 2013 9:41 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
robin



Joined: 16 Nov 2007
Posts: 20

PostPosted: Thu Oct 17, 2013 1:43 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
SteveGTR



Joined: 11 Jan 2012
Posts: 66

PostPosted: Thu Oct 17, 2013 1:57 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
SteveGTR



Joined: 11 Jan 2012
Posts: 66

PostPosted: Thu Oct 17, 2013 7:46 pm    Post subject: Reply with quote

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 ).
Back to top
View user's profile Send private message
SteveGTR



Joined: 11 Jan 2012
Posts: 66

PostPosted: Thu Oct 17, 2013 10:00 pm    Post subject: Reply with quote

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))]"
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Mon Oct 21, 2013 12:51 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group