sqlvisual-studiodbproj

VS 2019 Db Project - Cant use ChangeTable


I'm having a bit of trouble using change tracking in a vs 2019 db project. I have referenced master in my db project and have ensured the function actually runs, However I cannot build my db project as I keep getting this build error.

the reference is ambiguous because it could refer to any of the following objects: [CT].[SYS_CHANGE_VERSION], [CT].[TrackedChanges]::[SYS_CHANGE_VERSION] or [dbo].[History_table].[TrackedChanges]::[SYS_CHANGE_VERSION].

Here is my sql

SELECT  
        TrackedChanges.SYS_CHANGE_VERSION,
        TrackedChanges.Change_ID,
        HT.Record_ID,
        HT.Field_Name,  
        HT.Changed_By_User_ID,  
        HT.New_Value,  
        HT.Table_Name,  
        HT.Control_ID,  
        HT.Change_DateTime
    FROM  
        dbo.History_table AS HT
    RIGHT OUTER JOIN  
        CHANGETABLE(CHANGES dbo.History_table, 0) AS TrackedChanges  
            ON HT.Change_ID = TrackedChanges.Change_ID
    WHERE 
        Control_ID is not null 
        AND Control_ID <> 0 

Any help is greatly appreciated.


Solution

  • Ok so after searching stack overflow for 'error sql71501' a user suggested doing a schema compare from the db to see if anything was missing. After such I noticed my table was missing 'ALTER TABLE [dbo].[History_Table] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);'

    And it now builds :)