CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012change-trackinginvalid-object-name
Cambesa
Cambesa

Reputation: 456

Invalid object name 'CHANGETABLE'

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().

Upvotes: 4

Views: 1933

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131180

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.

Upvotes: 6

Related Questions