Error in deployment script for table rebuild with computed columns
When attempting to deploy a change that
- necessitated a table rebuild, and
- included a computed field
My deployment script ran into an error: invalid column name "MyTable.MyColumn".
Investigation eventually revealed that the error was in the portion of the script that did the rebuild. What's happening is that the script creates a new table "RG_Recovery_1_MyTable" that includes the computed field, but as part of the computed field definition retains the table name of the original computation.
So, for example, when trying to rebuild a table named "FactChecklistCompletion" the script included the following (simplified):
CREATE TABLE dbo.RG_Recovery_1_FactChecklistCompletion<br> (<br> ChecklistCompletion_SK INT NOT NULL IDENTITY(1, 1)<br> ,ChecklistItem_SK INT NOT NULL<br> ,Person_SK INT NOT NULL<br> ,ChecklistItemCompletionDate DATETIME2(3) NULL<br> ,ChecklistItemCompletionDateKey AS (CONVERT(INT, CONVERT(CHAR(8), <b>FactChecklistCompletion.</b>ChecklistItemCompletionDate, (112)), (0)))<br> )<br>GO
Note that the definition of the ChecklistItemCompletionDateKey qualifies the attribute in the formula definition with the original table name, "FactChecklistCompletion".
Fixing this on my end involved manually editing the script to remove the table name from the computed column definition, at which point it ran successfully.
0 · Share on Twitter