Red Gate forums :: View topic - Build problem after changing a static data table
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

Build problem after changing a static data table

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



Joined: 12 Sep 2011
Posts: 99

PostPosted: Wed Mar 27, 2013 5:44 pm    Post subject: Build problem after changing a static data table Reply with quote

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



Joined: 12 Sep 2011
Posts: 99

PostPosted: Wed Mar 27, 2013 5:59 pm    Post subject: Reply with quote

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



Joined: 12 Sep 2011
Posts: 99

PostPosted: Wed Mar 27, 2013 10:18 pm    Post subject: Reply with quote

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:

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



Joined: 12 Sep 2011
Posts: 99

PostPosted: Thu Mar 28, 2013 10:31 am    Post subject: Reply with quote

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:

Quote:
[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** Smile/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** Smile
[10:20:41][Updating database] Automation License: activated, edition: standard, serial number: **censored by the poster** Smile
[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
Back to top
View user's profile Send private message
Emma A



Joined: 30 Mar 2009
Posts: 40

PostPosted: Thu Mar 28, 2013 10:55 am    Post subject: Reply with quote

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



Joined: 12 Sep 2011
Posts: 99

PostPosted: Thu Mar 28, 2013 2:38 pm    Post subject: Reply with quote

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:

Quote:
[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
Back to top
View user's profile Send private message
csmith



Joined: 30 Jun 2011
Posts: 117
Location: Cambridge

PostPosted: Thu Mar 28, 2013 2:57 pm    Post subject: Reply with quote

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
_________________
Chris Smith
Project Manager
Deployment Manager
Back to top
View user's profile Send private message
swinghouse



Joined: 12 Sep 2011
Posts: 99

PostPosted: Thu Mar 28, 2013 3:05 pm    Post subject: Reply with quote

Thanks for the prompt response, Chris!

/Mattias
Back to top
View user's profile Send private message
mark.jordan



Joined: 08 Dec 2011
Posts: 1

PostPosted: Wed Apr 10, 2013 6:01 pm    Post subject: Reply with quote

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



Joined: 12 Sep 2011
Posts: 99

PostPosted: Mon Apr 22, 2013 7:51 am    Post subject: Reply with quote

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
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