Change of column from Date To DateTime with change to column name fails

duncanbatchelor

We have a table in the database that contains a start date and end date. These have now needed to contain times as well and, as such, the data types have been changed. To meet naming conventions the StartDate and EndDate columns have been changed to StartDateTime and EndDateTime. This has caused the automated deployment to fail during build as there is a NOT NULL constraint in the mix. There is no problem with clearing the table data down before deployment as the data will be re-built. We have tried using a Pre-Deployment script which failed, as did migration script. Just to add a bit more, the table and column is referenced by a View which uses the STARTDATE column, changed to StartDateTime in the deployment.

The only way we could get around it was to run the TRUNCATE of the table, and DROP VIEW before, manually, and then run the deployment. It shouldn't be this difficult though should it?

Excerpt of the log, slightly redacted, below:

2019-03-11T08:06:04.7638105Z Dropping index [IX_xxxxxx] from [xxxx].[xxxxxx]

2019-03-11T08:06:04.7877302Z Rebuilding [xxxx].[xxxxxx]

2019-03-11T08:06:04.8529514Z ##[warning]The error 'Cannot insert the value NULL into column 'StartDateTime', table 'sagittari-test.org.RG_Recovery_1_xxxxxx'; column does not allow nulls. INSERT fails.' occurred when executing the following SQL:

2019-03-11T08:06:04.8534438Z ##[warning]INSERT INTO [org].[RG_Recovery_1_xxxxxx]([ID], [RepID], [StatusID], [CreatedBy], [Created], [ModifiedBy], [Modified]) SELECT [ID], [repID], [StatusID], [CreatedBy], [Created], [ModifiedBy], [Modified] FROM [xxxx].[xxxxxx]

2019-03-11T08:06:04.9916266Z ##[warning]The error 'Invalid column name 'StartDateTime'.

Jessica R

Hi @duncanbatchelor,

We have a feature request for allowing users to define a default value when attemping to add a NOT NULL column to a table, logged with reference SC-1428. There's no ETA for this as of yet, but I've +1'd it with your details.

Thanks for your feedback!

Diogo

@duncanbatchelor

I may have missed some detail but I think you could have kept the data in the table if you had written  written your script to first add the new columns, after that put the data in those new columns and only as a third step you would alter the table to add the default constraint.
Then you could remove the old columns from both the table and the view and make the view use the new columns.

duncanbatchelor

Diogo, I appreciate your response. The issue is that there should be a mapping facility during the migration process, rather than having to jump through hoops. We achieved the result, but the question was posted here to see if there is a SIMPLE way to make the required change as there is in the VS Studio database projects. Thank you for your time and consideration in posting though.

Julia.Hayward

In your Compare project, you can set up a table mapping, and inside that map the source and target columns. Compare will then understand that the correct scripting behaviour is to rename the column and then change the column type (rather than doing a drop and recreate, with resulting data loss)

duncanbatchelor

Thanks you Julia, but this is in Source Control rather than a Schema or Data Compare, so wanted to find an answer for Source Control changes like this. But thank you.

Jessica R

Hi @duncanbatchelor!

I realize this would also be a workaround rather than a built-in SQL Source Control option, but I think adding a default constraint could also help.

duncanbatchelor

Hi Jessica

Thank you, and as a workaround this may work. The point is a bit moot now as we have resolved this manually. It would be appropriate to have this sort of feature built in for future users though.

Kind regards

Duncan

deproajah

great .. thanks
______________________

Desain Website

Jasa Desain Website