Options
Change of column from Date To DateTime with change to column name fails
duncanbatchelor
Posts: 4 New member
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:
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'.
Tagged:
Best Answer
-
Options
Jessica 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!
Answers
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.
Redgate Software
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?
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
______________________