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

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

Best Answer

  • Jessica RJessica R Posts: 1,278 Rose Gold 4
    Accepted Answer

    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!

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


Answers

Sign In or Register to comment.