Strange SQL Server Change Tracking Issue

• sean.addington

  Valued Member

  Points: 74

  More actions

  October 20, 2015 at 4:21 pm

  #305787

  Hello everyone,

  Long time reader, first time writer so please be gentle.

  I'm wondering if anyone else has seen this kind of issue before. Recently, we tested and implemented the Change Tracking feature in SQL Server 2008 R2 (yes, I know we're a little behind on versions here). Everything tested fine, and no issues noticed prior to implementing in our production environment. This server hosts a number of different databases, and we only enabled this feature on one of the hosted databases.

  However, today we noticed that we couldn't see the data using the CHANGETABLE function when querying the database. When using a query like the one below, we receive an error reading ""CHANGES" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90."

  Example query:

  SELECT top 10 * FROM CHANGETABLE (CHANGES sales,0) as CT

  I've been through a number of posts in researching this, and everything says to change the compatibility level of the DB, and all DB's hosted on this installation of SQL Server are already at 100, so I don't feel like compatibility is an issue on this. The other strange part of this I've noticed is that if we change to one of the other DB's and query using a 3-part name pointing at the one database using change tracking, it pulls the change data without any errors reported.

  So, right now, I'm at a loss for what may be occurring and was curious if anyone else has seen anything like this before, and my forehead is a little sore from banging it on my desk. Anyone have any thoughts/advice on where else I can check? Thanks to any and all who take the time to respond.

• sean.addington

  Valued Member

  Points: 74

  More actions

  November 3, 2015 at 3:35 pm

  #1837491

  In case anyone looking at this is curious on how to resolve this type of issue, you have to re-do the compatibility mode setting on the DB in question. After speaking with a colleague about this and doing some additional testing, we found that this is the correct resolution to this particular issue. Hopefully this helps anyone facing something similar.