Drop Column and insert rows into table with linked data

instalectual

I seem to have encountered an issue when I drop a column in a table with linked data and also add rows to the table in the same commit. When I go to deploy the package, the deployment manager seems to alter the table appropriately but fails when inserting rows into the table because the insert still references the now dropped columns.

-Daniel

Chris.Allen

Can you post the commit so that I can see exactly what the script is please?

instalectual

My description was a little off, it isn't the same commit, its two separate commits but both changes are included in the same deployment. The actions taken during the commits are below:

1st commit inserts the following static data into this table:

Table:
CREATE TABLE [active].[lk_CaseListHeaderDefault]
(
[CaseListHeaderDefaultId] [int] NOT NULL IDENTITY(1, 1),
[LabelID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HeaderName] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Position] [int] NOT NULL,
[LabelTypeID] [int] NOT NULL,
[DateCreated] [datetime] NOT NULL CONSTRAINT [DF__active_lk_CaseListHeaderDefault__DateCreated] DEFAULT (getutcdate()),
[DateModified] [datetime] NOT NULL CONSTRAINT [DF__active_lk_CaseListHeaderDefault__DateModified] DEFAULT (getutcdate())
) ON [PRIMARY]
ALTER TABLE [active].[lk_CaseListHeaderDefault] ADD
CONSTRAINT [pk__active_CaseListHeaderDefault] PRIMARY KEY CLUSTERED ([CaseListHeaderDefaultId]) ON [PRIMARY]
GO

Data:
SET IDENTITY_INSERT [active].[lk_CaseListHeaderDefault] ON
INSERT INTO [active].[lk_CaseListHeaderDefault] ([CaseListHeaderDefaultId], [LabelID], [HeaderName], [Position], [LabelTypeID], [DateCreated], [DateModified]) VALUES (17, 'DataFieldLabel1', N'Data Field Label 1', 17, 4, '2014-07-22 21:38:06.760', '2014-07-22 21:38:06.760')
INSERT INTO [active].[lk_CaseListHeaderDefault] ([CaseListHeaderDefaultId], [LabelID], [HeaderName], [Position], [LabelTypeID], [DateCreated], [DateModified]) VALUES (18, 'DataFieldLabel2', N'Data Field Label 2', 18, 4, '2014-07-22 21:38:06.760', '2014-07-22 21:38:06.760')
INSERT INTO [active].[lk_CaseListHeaderDefault] ([CaseListHeaderDefaultId], [LabelID], [HeaderName], [Position], [LabelTypeID], [DateCreated], [DateModified]) VALUES (19, 'DataFieldLabel3', N'Data Field Label 3', 19, 4, '2014-07-22 21:38:06.760', '2014-07-22 21:38:06.760')
INSERT INTO [active].[lk_CaseListHeaderDefault] ([CaseListHeaderDefaultId], [LabelID], [HeaderName], [Position], [LabelTypeID], [DateCreated], [DateModified]) VALUES (20, 'DataFieldLabel4', N'Data Field Label 4', 20, 4, '2014-07-22 21:38:06.760', '2014-07-22 21:38:06.760')
INSERT INTO [active].[lk_CaseListHeaderDefault] ([CaseListHeaderDefaultId], [LabelID], [HeaderName], [Position], [LabelTypeID], [DateCreated], [DateModified]) VALUES (21, 'DataFieldLabel5', N'Data Field Label 5', 21, 4, '2014-07-22 21:38:06.760', '2014-07-22 21:38:06.760')
INSERT INTO [active].[lk_CaseListHeaderDefault] ([CaseListHeaderDefaultId], [LabelID], [HeaderName], [Position], [LabelTypeID], [DateCreated], [DateModified]) VALUES (22, 'DataFieldLabel6', N'Data Field Label 6', 22, 4, '2014-07-22 21:38:06.760', '2014-07-22 21:38:06.760')
INSERT INTO [active].[lk_CaseListHeaderDefault] ([CaseListHeaderDefaultId], [LabelID], [HeaderName], [Position], [LabelTypeID], [DateCreated], [DateModified]) VALUES (23, 'DataFieldLabel7', N'Data Field Label 7', 23, 4, '2014-07-22 21:38:06.760', '2014-07-22 21:38:06.760')
INSERT INTO [active].[lk_CaseListHeaderDefault] ([CaseListHeaderDefaultId], [LabelID], [HeaderName], [Position], [LabelTypeID], [DateCreated], [DateModified]) VALUES (24, 'DataFieldLabel8', N'Data Field Label 8', 24, 4, '2014-07-22 21:38:06.760', '2014-07-22 21:38:06.760')
INSERT INTO [active].[lk_CaseListHeaderDefault] ([CaseListHeaderDefaultId], [LabelID], [HeaderName], [Position], [LabelTypeID], [DateCreated], [DateModified]) VALUES (25, 'DataFieldLabel9', N'Data Field Label 9', 25, 4, '2014-07-22 21:38:06.760', '2014-07-22 21:38:06.760')
INSERT INTO [active].[lk_CaseListHeaderDefault] ([CaseListHeaderDefaultId], [LabelID], [HeaderName], [Position], [LabelTypeID], [DateCreated], [DateModified]) VALUES (26, 'DataFieldLabel10', N'Data Field Label 10', 26, 4, '2014-07-22 21:38:06.760', '2014-07-22 21:38:06.760')
INSERT INTO [active].[lk_CaseListHeaderDefault] ([CaseListHeaderDefaultId], [LabelID], [HeaderName], [Position], [LabelTypeID], [DateCreated], [DateModified]) VALUES (27, 'DataFieldLabel11', N'Data Field Label 11', 27, 4, '2014-07-22 21:38:06.760', '2014-07-22 21:38:06.760')
INSERT INTO [active].[lk_CaseListHeaderDefault] ([CaseListHeaderDefaultId], [LabelID], [HeaderName], [Position], [LabelTypeID], [DateCreated], [DateModified]) VALUES (28, 'DataFieldLabel12', N'Data Field Label 12', 28, 4, '2014-07-22 21:38:06.760', '2014-07-22 21:38:06.760')
SET IDENTITY_INSERT [active].[lk_CaseListHeaderDefault] OFF

The subsequent commit modifies that table as follows:
ALTER TABLE [active].[lk_CaseListHeaderDefault]
DROP COLUMN [DateCreated]

ALTER TABLE [active].[lk_CaseListHeaderDefault]
DROP COLUMN [DateModified]

The behavior is such that when I deploy the package that contains these changes, the columns are dropped as specified, but the data insert still contains the values for the dropped columns resulting in the following error:
2014-07-24 17:08:37 -07:00 ERROR RedGate.Deploy.SqlServerDbPackage.Shared.Exceptions.InvalidSqlException: Dynamic deployment failed
2014-07-24 17:08:37 -07:00 ERROR Invalid column name 'DateCreated'.
2014-07-24 17:08:37 -07:00 ERROR Invalid column name 'DateModified'. ---> System.Data.SqlClient.SqlException: Invalid column name 'DateCreated'.
2014-07-24 17:08:37 -07:00 ERROR Invalid column name 'DateModified'.

I was eventually able to deploy by reverting my changes and then recommitting and deploying each change separately. While this works it may not always be a viable solution. Hope this helps.

-Daniel

Chris.Allen

Thanks for the clear steps. I'm not having any issues with these steps in that order. I guess the logic of what you are seeing is, for some odd reason, the INSERTS are happening as the last of these steps but i can't for the life of me think of:

1) why its happening at all
2) why its not happening for me

I can only imagine we are using a different technique to create the nuget package. I am using TeamCity addins- are you using the SSMS addin?

instalectual

I am using SSMS and SQL Source Control to execute and commit the changes. At the time of commit CruiseControl.net executes a series of powershell scripts which build and publish the packages using sqlCI. I am using the v11 beta of SQLCompare as part of this step.

Chris.Allen

Are you able to get hold of the full build log please?