Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2016
    Posts
    17

    Answered: sql not compiling: referencing non-existent column

    I have the following script that runs when I deploy my database project from Visual Studio 2015:

    -- Use @_dbVersion to check DB version:
    DECLARE @_dbVersion int
    select @_dbVersion = [Version] from [dbo].[DBVersion]

    IF @_dbVersion = 1
    BEGIN
    ...
    -- Update DB Version:
    UPDATE [dbo].[DBVersion]
    SET [Version] = 2
    SET @_dbVersion = 2
    END

    IF @_dbVersion = 2
    BEGIN
    ...
    -- Update DB Version:
    UPDATE [dbo].[DBVersion]
    SET [Version] = 3
    SET @_dbVersion = 3
    END

    IF @_dbVersion = 3
    BEGIN
    ...
    -- Update DB Version:
    UPDATE [dbo].[DBVersion]
    SET [Version] = 4
    SET @_dbVersion = 4
    END

    IF @_dbVersion = 4
    BEGIN

    IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='CauseToRiskDataDetails')
    BEGIN
    ...
    PRINT('Copying tolerable frequencies from RiskData to BowtieLoop.')
    UPDATE [dbo].[BowtieLoop]
    SET TolerableFrequency = RD.TolerableFrequency
    FROM [dbo].[RiskData] RD
    JOIN [dbo].[BowtieLoop] BL ON RD.BowtieLoopId = BL.BowtieLoopId

    PRINT('Dropping CauseToRiskDataDetails')
    DROP TABLE [dbo].[CauseToRiskDataDetails]
    ...
    END

    -- Update DB Version:
    UPDATE [dbo].[DBVersion]
    SET [Version] = 5
    SET @_dbVersion = 5
    END

    This script essentially performs incremental updates to the database based on a database version. It starts by getting the version from the DBVersion table and assigning it to the variable @_dbVersion. This way, whatever version the database is on, it will skip all incremental changes before that version and perform all incremental changes after that version, incrementing the version for every change.

    For versions 1 to 4, I have skipped the script for convenience, leaving the general structure of checking the versions for clarity. It's what happens when the version is 4 that I'm having problems with.

    We have recently removed the column TolerableFrequency from our RiskData table. But you can see that the script for version 4 makes reference to TolerableFrequency. This is causing an error when I try to deploy the database from Visual Studio 2015. It tells me that TolerableFrequency is an invalid reference.

    This makes sense as we no longer have TolerableFrequency but I'd like to keep the script as the whole purpose of going through these incremental changes is for cases in which we are dealing with older versions of the database, and some of these versions may still have TolerableFrequency on RiskData.

    I tried putting a check for TolerableFrequency before running the script above like so:

    IF EXISTS(SELECT * FROM sys.columns WHERE Name = N'TolerableFrequency' AND Object_ID = Object_ID(N'RiskData'))
    BEGIN
    PRINT('Copying tolerable frequencies from RiskData to BowtieLoop.')
    UPDATE [dbo].[BowtieLoop]
    SET TolerableFrequency = RD.TolerableFrequency
    FROM [dbo].[RiskData] RD
    JOIN [dbo].[BowtieLoop] BL ON RD.BowtieLoopId = BL.BowtieLoopId
    END

    But this doesn't seem to help. It seems as though the error is a compile error as there is no way the above code could be run (even without the check for TolerableFrequency on RiskData, it shouldn't run).

    Is there a way to tell the SQL compiler only to compile this code if TolerableFrequency exists on RiskData?

  2. Best Answer
    Posted by weejas

    "Why not just comment out that section of code? Or is this a column that is sometimes present? (If the latter, I would look at building the update statement on the fly and executing via a variable - not best practice, though.)"


  3. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,046
    Provided Answers: 10
    Why not just comment out that section of code? Or is this a column that is sometimes present? (If the latter, I would look at building the update statement on the fly and executing via a variable - not best practice, though.)
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 1

  4. #3
    Join Date
    Dec 2016
    Posts
    17
    Quote Originally Posted by weejas View Post
    Why not just comment out that section of code? Or is this a column that is sometimes present? (If the latter, I would look at building the update statement on the fly and executing via a variable - not best practice, though.)
    Yes, I ended up doing something like this:

    EXEC('UPDATE...')

    Seems to work. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •