Build problem after changing a static data table

swinghouse

After changing the structure (we added two fields) of one of the tables that we've defined as static data in SQL Source Control, we get the following error when we try to build a database deployment package in TeamCity:

[17:17:57][Generating 0.20 upgrade script] SQL Compare Command Line V10.3.8.406
[17:17:57][Generating 0.20 upgrade script] ==============================================================================
[17:17:57][Generating 0.20 upgrade script] Copyright ¸ Red Gate Software Ltd 1999-2012
[17:17:57][Generating 0.20 upgrade script]
[17:17:57][Generating 0.20 upgrade script] Registering data sources
[17:18:12][Generating 0.20 upgrade script] Creating mappings
[17:18:12][Generating 0.20 upgrade script] Comparing
[17:18:13][Generating 0.20 upgrade script]
[17:18:13][Generating 0.20 upgrade script] Applying Command Line Items
[17:18:13][Generating 0.20 upgrade script]
[17:18:15][Generating 0.20 upgrade script] Error: Den angivna nyckeln finns inte i uppslagslistan.
[17:18:17][Generating 0.20 upgrade script] ##teamcity[buildStatus status='FAILURE' text='Generating 0.20 upgrade script failed with exit code 126']
[17:18:22]sqlCI error occurred: RedGate.SQLCI.Engine.Error: Generating 0.20 upgrade script failed with exit code 126[/quote]

Sorry for the Swedish error message in there! It translates to "The provided key is not present in the dictionary".

After unlinking the changed table from the static data definition in SQL Source Control, the build succeeds (barring the InvalidOperationException that I suspect you've fixed in the latest version of the TeamCity plugin - we're still on v1.0.0.926.)

Modifying data in static tables works smoothly, it's when you change the structure of a static data table that this error occurs.

/Mattias

swinghouse

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

swinghouse

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

swinghouse

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

Emma A

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

swinghouse

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

csmith

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

swinghouse

Thanks for the prompt response, Chris!

/Mattias

Mark J

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

swinghouse

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