Build problem after changing a static data table

Comments

• swinghouse Posts: 120 Bronze 2

  March 27, 2013 12:59PM

  I upgraded to v1.0.0.1039 of your TeamCity plugin. The error mentioned above ("The provided key is not present in the dictionary") occurs in this version as well.
  
  /Mattias

  0 · Share on Twitter
• swinghouse Posts: 120 Bronze 2

  March 27, 2013 5:18PM

  Unlinking the problematic table from the static data list in SQL Source Control and building a database deployment package with NuGet, resulted in another problem when I tried to deploy the package with Deployment Manager:
  

  2013-03-27 17:59:46 DEBUG Performing post-deploy validation; allowPostDeployDatabaseValidation variable evaluated to true
  2013-03-27 17:59:46 INFO Validating arrr_here_be_one_of_them_servers\BagheraData against db\state
  2013-03-27 17:59:46 INFO Registering target database
  2013-03-27 17:59:51 INFO Registering scripts folder
  2013-03-27 18:00:00 INFO - Comparing schemas
  2013-03-27 18:00:00 INFO - Comparing static data
  2013-03-27 18:00:03 WARN Static data differences found, validation failed
  2013-03-27 18:00:03 DEBUG [dbo].[TblProdDatesCalcMode] 3

  
  So even though I unlinked the table in question from the static data feature, Deployment Manager still seems to treat it as static data!
  
  All in all, there seems to be some rough edges to Deployment Manager's handling of static data. It would be great if you could look into them!
  
  /Mattias

  0 · Share on Twitter
• swinghouse Posts: 120 Bronze 2

  March 28, 2013 5:31AM

  Having a rough time trying to get back in the right database build groove...
  
  I reverted the structural changes in the offending table, but unfortunately this causes the TeamCity plugin to fail on another step in the build process:
  

  [10:20:38]Updating database (1m)
  [10:20:38][Updating database] Starting: C:\TeamCity\buildAgent\plugins\RedGateSQLServer\assets\SC\SQLCompare.exe /include="Identical" /include="StaticData" /scripts1="C:\TeamCity\buildAgent\temp\buildTmp\gd4o0spi.fz2\db\state" /activateSerial:**censored by the poster** :-)/server2="Bagheratest2011\SQLEXPRESS" /database2="BagheraDataCI" /synchronize
  [10:20:41][Updating database] /activateSerial is being ignored because the license is already activated with that serial key
  [10:20:41][Updating database] SQL Compare: activated, edition: professional, serial number: **censored by the poster** :-)
  [10:20:41][Updating database] Automation License: activated, edition: standard, serial number: **censored by the poster** :-)
  [10:20:42][Updating database] SQL Compare Command Line V10.3.8.406
  [10:20:42][Updating database] ==============================================================================
  [10:20:42][Updating database] Copyright ¸ Red Gate Software Ltd 1999-2012
  [10:20:42][Updating database]
  [10:20:42][Updating database] Registering data sources
  [10:21:24][Updating database] Creating mappings
  [10:21:25][Updating database] Comparing
  [10:21:25][Updating database]
  [10:21:25][Updating database] Applying Command Line Items
  [10:21:25][Updating database]
  [10:21:27][Updating database] Retrieving migration scripts
  [10:21:27][Updating database] Checking for identical databases
  [10:21:27][Updating database] Creating SQL
  [10:21:28][Updating database] Inserting static data SQL into deployment script
  [10:21:28][Updating database] Deploying changes (from DB1 to DB2)
  [10:21:36][Updating database] Error: Synchronization of 'Scripts.state' and
  [10:21:36][Updating database] 'Bagheratest2011\SQLEXPRESS.BagheraDataCI' failed: Invalid column name
  [10:21:36][Updating database] 'OrderMode'. Invalid column name 'CalcModeImage'.
  [10:21:38][Updating database] ##teamcity[buildStatus status='FAILURE' text='Updating database failed with exit code 126']
  [10:21:41]sqlCI error occurred: RedGate.SQLCI.Engine.Error: Updating database failed with exit code 126

  
  The TeamCity plugin doesn't seem to acknowledge that I've reverted the table in question (by dropping the columns "OrderMode" and "CalcModeImage").
  
  What's a good way to get back on the right track?
  
  /Mattias

  0 · Share on Twitter
• Emma A Posts: 42

  March 28, 2013 5:55AM

  Hi Mattias,
  
  Currently you are doing what we call a static upgrade, where the script for upgrading the database is pre-generated and put inside the package. This sql script is then run at deployment time.
  The TeamCity plugin determines what versions of the package are deployed from Deployment Manager and then gets the package for that version. It generates the new upgrade script based on the db/state folder inside the package and the source controol repository containing the changes.
  So even though you have changed the database, the information inside the package is still there.
  
  If you uncheck the Generate Upgrade Scripts checkbox in the teamcity plugin and save your changes. In Deployment Manager you will need to add the variable allowDynamicDatabaseUpgrade with a value of true to the project.
  Then run then build step again this will perform what we call a dynamic upgrade, where the upgrade is handle at deployment time by SQL Compare technology.
  Once you have done the build and deployment you should be back in a good state. If you have more than one deployment of this project you would need to deploy to all the environments using the release that contains the package which will use dynamic upgrades
  
  Was there a specific reason why you have been generating the upgrade scripts? If so, after a successful deployment you will be able to recheck the Generate Upgrade script check box and go back to static deployments. If there is a static script in a project this will always be used even if the allowDynamicDatabaseUpgrade variable is still set to true.
  
  Hope this helps
  Kind Regards

  0 · Share on Twitter
• swinghouse Posts: 120 Bronze 2

  March 28, 2013 9:38AM

  Hi Emma,
  
  Thanks for your help. Most appreciated.!
  
  Aha! I had set allowDynamicDatabaseUpgrade to true, but failed to realize that I had to deactivate the database upgrade scripts generation in the TeamCity plugin in order to switch to dynamic database upgrades!
  
  I deslected that option plus the "Update a database on every build" setting.
  
  After these changes TeamCity managed to build a deployment package that Deployment Manager could use without any hiccups.
  
  So far, so good!
  
  However, when I re-enabled the "Update a database on every build" setting the TeamCity build failed anew with the same error as before:
  

  [13:03:46]Updating database (24s)
  [13:04:09][Updating database] Error: Synchronization of 'Scripts.state' and
  [13:04:09][Updating database] 'Bagheratest2011\SQLEXPRESS.BagheraDataCI' failed: Invalid column name
  [13:04:09][Updating database] 'OrderMode'. Invalid column name 'CalcModeImage'.
  [13:04:13]sqlCI error occurred: RedGate.SQLCI.Engine.Error: Updating database failed with exit code 126

  
  Luckily, that step is the least important step in the build process, but what could I do to get it to work again?
  
  /Mattias

  0 · Share on Twitter
• csmith Posts: 138 Bronze 1

  March 28, 2013 9:57AM

  Hi Mattias
  
  We've managed to reproduce the problem you experienced when enabling "Update a database on every build" and a developer is looking into it.
  
  We probably won't have a further update for you until after Easter now, though.
  
  Best regards,
  Chris

  Divisional Development Lead
  Redgate Software

  0 · Share on Twitter
• swinghouse Posts: 120 Bronze 2

  March 28, 2013 10:05AM

  Thanks for the prompt response, Chris!
  
  /Mattias

  0 · Share on Twitter
• Mark J Redgate › Posts: 125 Bronze 5

  April 10, 2013 1:01PM

  Hi Mattias
  
  I'm one of the developers that's been looking at these problems. The "provided key is not present in the dictionary" error we've managed to track down and we'll probably release an updated version of sqlCI once we're happy with the fix.
  
  The "invalid column name" error is a bit more tricky, though, and we're not sure exactly what the problem is. One thing we found it might be is if you added a stored procedure or similar that depended on the new fields. After reverting the changes to the table, the stored procedure would be referencing fields that are no longer present - hence the error when updating the database.
  
  If that turns out not to be the problem, then some indication of what SQL is running when the error is produced would be very helpful. Could you try running SQL Profiler while the teamcity plugin is running?
  
  Thanks,
  Mark

  0 · Share on Twitter
• swinghouse Posts: 120 Bronze 2

  April 22, 2013 2:51AM

  Hi Mark,
  
  Thanks for the update on the "Provided key is not present in the dictionary" error!
  
  As to the "Invalid column name" error, I'm pretty sure you've nailed the cause of it. Alas, it went away after I undid the database change that triggered the "Provided key..." error and I didn't analyze it any further.
  
  /Mattias

  0 · Share on Twitter