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

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.
Tagged:

Answers

  • a.higginsa.higgins Posts: 70 Bronze 1
    Since the code formatter appears not to have liked my line breaks, I'm pasting the relevant script here in an unformatted block:

    CREATE TABLE dbo.RG_Recovery_1_FactChecklistCompletion
        (
            ChecklistCompletion_SK         INT          NOT NULL IDENTITY(1, 1)
           ,ChecklistItem_SK               INT          NOT NULL
           ,Person_SK                      INT          NOT NULL
           ,ChecklistItemCompletionDate    DATETIME2(3) NULL
           ,ChecklistItemCompletionDateKey AS (CONVERT(INT, CONVERT(CHAR(8), FactChecklistCompletion.ChecklistItemCompletionDate, (112)), (0)))
        )
    GO


  • Hi @a.higgins,

    I've reproduced it here as well and raised the issue under internal reference SC-10863 with the developers.  I will post here when I have an update on the issue, but for now as you have seen, removing the 2 part naming of the columns used in the computed column is what you will need to do to get around this.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Looking into this a bit more with the team - is there a reason you use the two part naming for the columns in the computed column?  It doesn't seem like you can actually reference columns in another table in that way so it appears to be redundant.


    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • a.higginsa.higgins Posts: 70 Bronze 1
    Hey Alex - no particular reason specific to computed columns, we usually qualify object names (and use SQL Prompt to auto-format all code to use it) in general, which means it's probably getting introduced at some point in the development process and then just carried through to the table definition.
  • Alex BAlex B Posts: 905 Diamond 3
    I'm not able to get SQL Prompt to qualify the columns in the computed column.  I have all three qualify options ticked, and in the Styles section I have "Qualify object names" ticked, but it doesn't qualify QtyAvailable and UnitPrice in the below table when I format this T-SQL

    create table [dbo].[Products]
    (
        [ProductID] int identity (1, 1) not null
      ,[QtyAvailable] smallint
      ,[UnitPrice] money
      ,[InventoryValue] as [QtyAvailable] * [UnitPrice]
    );

    Nor can I get it to insert the columns qualified by the table name when using auto-complete so I'm not sure if it's SQL Prompt - or if it was, if it still would be doing it. 

    It doesn't seem to remove it if it's there, but won't add it for me, so that may be the way forward here - to remove it so that going forward it doesn't cause a problem.
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • a.higginsa.higgins Posts: 70 Bronze 1
    Alex, thanks for your support on this. 

    I'm guessing, given what you just laid out, that at some point in the development process someone wrote it as a SELECT statement, just to try out the logic of the expression, and then simply copied the expression into the CREATE or ALTER TABLE script.

    At this point I'd say that it's more of a development thing and, unless SQL Compare can remove aliases during the table rebuild process, the best approach for my team going forward will be to find and remove those cases!

    Thanks again for your help!
  • Alex BAlex B Posts: 905 Diamond 3
    Not a problem!

    SQL Compare can't currently and shouldn't really be modifying the code without being told to do so. I suppose it could be an option to specify, but I don't think it would meet the threshold to make it as a new option (not enough people needing it essentially since they don't want to crowd the number of options there).

    Take care!
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • a.higginsa.higgins Posts: 70 Bronze 1
    Thanks Alex!  I certainly understand your perspective on this, and agree that SQL Compare shouldn't be modify the code unnecessarily.

    There definitely is an opportunity for a fix here, though, since legitimately-functioning code results in a deployment error when sent through the tool.  I'm not necessarily pushing for a change that strips out the unnecessary qualified reference, but there's at least a known limitation of the tool that means "good but weird" code can't get deployed under certain circumstances.
Sign In or Register to comment.