What are the challenges you face when working across database platforms? Take the survey

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

  • Options
    Jessica RJessica R Posts: 1,319 Rose Gold 4

    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?


  • Options

    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.
  • Options
    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.
  • Options
    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)
    Tech Lead, SQL Toolbelt
    Redgate Software
  • Options
    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.
  • Options
    Jessica RJessica R Posts: 1,319 Rose Gold 4
    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.

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?

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

Sign In or Register to comment.