Invalid object name 'CHANGETABLE'

Question

I just enabled change tracking by turning on ALLOW_SNAPSHOT_ISOLATION, executing this query to turn on change tracking for the database

ALTER DATABASE [DatabaseName] 
SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS,AUTO_CLEANUP = ON)

and by finally enabling it on the table

ALTER TABLE [TableName] 
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF)

Now when I call

SELECT * 
FROM CHANGETABLE(CHANGES, 0)

I get this error message:

Invalid object name 'CHANGETABLE'.

The database version is SQL Server 2012. How can CHANGETABLE be invalid and how can I make it work? I assumed it would work out of the box because it's a system function. It's even highlighted in SQL Server Management Studio. I modified a little bit of data between turning on change tracking and calling CHANGETABLE().

Typically you should prefix functions with their schema... have you tried sys.changetable() or dbo.changetable()? — pmbAustin, Mar 21 '19 at 15:43
Did you check the [documentation examples](https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/changetable-transact-sql?view=sql-server-2017#examples) ? `CHANGETABLE (CHANGES)` requires a table name, eg `select * FROM CHANGETABLE (CHANGES Employees, @last_sync_version) AS C` — Panagiotis Kanavos, Mar 21 '19 at 15:44
Prefixing changetable with dbo or sys, both return invalid object name — Cambesa, Mar 21 '19 at 15:45
@Cambesa the query you wrote is wrong. You forgot the table name. — Panagiotis Kanavos, Mar 21 '19 at 15:45
@PanagiotisKanavos Thank you very much, adding the table name to the function makes it work. I used an example that did not include the table name in the function. — Cambesa, Mar 21 '19 at 15:48

score 6 · Accepted Answer · answered Mar 21 '19 at 15:49

The correct syntax is

SELECT * FROM CHANGETABLE(CHANGES MyTableName,@last_version) as SomeAlias

This is shown in the documentation examples :

DECLARE @last_sync_version bigint;  
SET @last_sync_version = <value obtained from query>;  

SELECT [Emp ID], SSN,  
    SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION,  
    SYS_CHANGE_COLUMNS, SYS_CHANGE_CONTEXT   
FROM CHANGETABLE (CHANGES Employees, @last_sync_version) AS C;

Forgetting the table name results in Invalid object name 'CHANGETABLE'

Forgetting the table alias results in A table returned by the CHANGETABLE function must be aliased.

Invalid object name 'CHANGETABLE'

1 Answers1