0

I am running this query

IF NOT EXISTS (SELECT 1 FROM sys.change_tracking_tables 
               WHERE object_id = OBJECT_ID('Person.[Address]'))
BEGIN
    ALTER TABLE Person.[Address]
    ENABLE CHANGE_TRACKING  
    WITH (TRACK_COLUMNS_UPDATED = ON)  
END

SELECT 1 
FROM CHANGETABLE( CHANGES Person.[Address], 1) as E

I was expecting this would give me correct result, even if change tracking is disabled on table initially.

But I get this error instead:

Msg 22105, Level 16, State 1, Line 9
Change tracking is not enabled on table 'Person.Address'.

But when I run these queries separately, it works fine. Is there some asynchronous type nature in SQL?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
harpal
  • 426
  • 4
  • 12
  • 1
    Are you executing this in SSMS? Try inserting a `GO` after the if-begin-end so that the select statement is in a separate batch. – AlwaysLearning Apr 18 '22 at 09:55
  • Thanks @AlwaysLearning, it worked, today I got to know power of `GO` . – harpal Apr 18 '22 at 10:02
  • Explanation about `GO` https://stackoverflow.com/a/56370223/6310485 – harpal Apr 18 '22 at 10:19
  • 3
    Just to clarify what's happening: this statement is parsed and validated (checks on object existence etc), but conditions are not evaluated. At the moment of parsing the error is thrown, because at that time change tracking was not enabled, and the parsing doesn't know you are enabling it in the same statement. `GO` separates the statements, so on parsing the `SELECT` all is well in the eyes of sql server – HoneyBadger Apr 18 '22 at 12:33

0 Answers0