Problem deploying using V2 Migration Scripts

youngcrazedpeeling

Hi, I'm trying out migration scripts using deployment manager but I've run into a problem when deploying my published db. I'm not sure if I'm doing anything wrong as the change I'm making is fairly simple.

This may be in the wrong forum but I feel it could be a deployment problem.

Scenario:
I've got a db with two tables - Table123 and TableXYZ. I'm trying to move column X with its data from XYZ to 123.

Steps I followed:
I added column X to Table123 and committed the changes.
I added a migration script to update column X in XYZ with the values from column X in 123 and committed the changes.
I deleted column X from TableXYZ and committed the changes.
Published the database to deployment manager and created a new release.

However when the deployment runs I end up with the following error:

2014-07-18 10:22:05 +01:00 ERROR Error while executing job: Dynamic deployment failed
2014-07-18 10:22:05 +01:00 ERROR Error while running migration script:
2014-07-18 10:22:05 +01:00 ERROR Invalid column name 'ColumnX'.
2014-07-18 10:22:05 +01:00 ERROR RedGate.Migrations.Core.Deployment.InvalidMigrationScriptException: Dynamic deployment failed
2014-07-18 10:22:05 +01:00 ERROR Error while running migration script:
2014-07-18 10:22:05 +01:00 ERROR Invalid column name 'ColumnX'.
2014-07-18 10:22:05 +01:00 ERROR at ##.#mj5c(IDeployLog , String , String , Action )
2014-07-18 10:22:05 +01:00 ERROR at ##.#1Gfd(#R21c )
2014-07-18 10:22:05 +01:00 ERROR at ##.#iz3c(#Ty3c , String , String , #FG3c , #8gL , IDeployLog )
2014-07-18 10:22:05 +01:00 ERROR at RedGate.Deploy.SqlServerDbPackage.AgentPlugin.DbPackageConvention.Apply(IConventionContext context)
2014-07-18 10:22:05 +01:00 ERROR at RedGate.Deploy.Agent.Deployment.Conventions.ConventionRunner.<>c__DisplayClassf.<ApplyConventions>b__e(IConvention convention)
2014-07-18 10:22:05 +01:00 ERROR at System.Collections.Generic.List`1.ForEach(Action`1 action)
2014-07-18 10:22:05 +01:00 ERROR at RedGate.Deploy.Agent.Deployment.Conventions.ConventionRunner.ApplyConventions(IConventionContext context)
2014-07-18 10:22:05 +01:00 ERROR at RedGate.Deploy.Agent.Deployment.DeploymentController.Execute(PackageMetadata package, IVariableDictionary variables, IFolderTracker folderTracker)
2014-07-18 10:22:05 +01:00 ERROR at RedGate.Deploy.Agent.Deployment.DeployPackageJobExecutor.Execute(DeployPackageJob job)
2014-07-18 10:22:05 +01:00 ERROR at CallSite.Target(Closure , CallSite , Object , Object )
2014-07-18 10:22:05 +01:00 ERROR at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid2[T0,T1](CallSite site, T0 arg0, T1 arg1)
2014-07-18 10:22:05 +01:00 ERROR at RedGate.Deploy.Agent.Services.Jobs.JobRunner.ExecuteJob(IJob job, String& errorMessage, ILogScope additionalLogTarget)
2014-07-18 10:22:05 +01:00 ERROR Command RedGate.Deploy.Agent.Commands.SingleShotDeploymentCommand failed

It seems to me that it is trying to deploy the migration script changes before it has added ColumnX to table123?

Any help would be appreciated!

toby.smyth

Hi there,
It sounds like your migration script might be not have the correct guard clause: http://documentation.red-gate.com/displ ... on+scripts.

In this case the guard clause should make sure that the migration script only runs if ColumnX exists in TableXYZ and Table123. This will prevent the migration from being applied too early or after you have deleted the old column.

Does that make sense? If you post your migration script I can have a look at it.

Best wishes,

youngcrazedpeeling

Hi Toby, this is my migration script -

IF COL_LENGTH('dbo.Table123','ColumnX') IS NOT NULL
BEGIN
	UPDATE y
	SET y.ColumnX = x.ColumnX
	FROM dbo.TableXYZ AS x INNER JOIN dbo.Table123 y ON x.id = y.Id
end

I think I thought that as I committed the migration script before I removed ColumnX from TableXYZ it wouldn't need to check both tables.

I'll try updating the guard clause and let you know.

Thanks

youngcrazedpeeling

I updated it to this:

IF (COL_LENGTH('dbo.Table123','ColumnX') IS NOT NULL AND COL_LENGTH('dbo.TableXYZ','ColumnX') IS NOT NULL)
BEGIN
	UPDATE y
	SET y.ColumnX = x.ColumnX
	FROM dbo.TableXYZ AS x INNER JOIN dbo.Table123 y ON x.id = y.Id
end

But still get the same problem.

Maybe I'm writing my guard clauses incorrectly?

youngcrazedpeeling

I've managed to sort it out now - I had to add the dropping of the column into my script too.

toby.smyth

I'm glad you got it working. The solution I came up with looks like this:

--If table123 doesnt have ColumnX then add it
IF(COL_LENGTH('dbo.Table123','ColumnX') IS NULL) 
BEGIN
	ALTER TABLE dbo.Table123
	ADD ColumnX NCHAR(10)
END
GO

--If the TableXYZ or ColumnX dont exist do nothing
IF (COL_LENGTH('dbo.TableXYZ','ColumnX') IS NOT NULL)
BEGIN 
	--Do the migration
	UPDATE y 
	SET y.ColumnX = x.ColumnX 
	FROM dbo.TableXYZ AS x INNER JOIN dbo.Table123 y ON x.Id = y.Id
END

The idea is that there is no guarantee that any of the tables or ColumnX's exist when the migration script runs and the guards should do something appropriate in that case. My script could probably be improved by checking that Table123 actually exists.

I hope that helps,