Sql-server – using Change Tracking functions from another database

change-trackingsql server

If I turn on change tracking on a database called Production, can I use the functions to get the change data from another database?

For example: CHANGE_TRACKING_CURRENT_VERSION();

I have tried the following, and also tried replacing the schema with sys but I don't think this approach is working:

Use [Analysis]
GO
SELECT Production.dbo.CHANGE_TRACKING_CURRENT_VERSION()

I have worked around it by creating a stored procedure on Production that calls this function. Then I can call that stored procedure from other databases on that SQL Server. But would like to know if there is a more direct way.

I am trying to migrate data from one system to another (over a period of a week or two, then shut down the old system). Change tracking is enabled on the other database. I am limited by the version of SQL Server, CDC requires Enterprise, Developer or Evaluation.

Best Answer

CHANGE_TRACKING_CURRENT_VERSION is a built in function not a UDF so Production.dbo.CHANGE_TRACKING_CURRENT_VERSION() isn't allowed.

It accepts no parameters to specify the database.

I would imagine that either of the following would work.

The first calls Production.sys.sp_executesql and executes the function. The second calls sys.sp_executesql in the current database and adds an explicit USE statement to set the context. I haven't set up change tracking and tested explicitly but both approaches work as desired with DB_NAME()

DECLARE @CHANGE_TRACKING_CURRENT_VERSION BIGINT;

EXEC Production.sys.sp_executesql
  N'SELECT @ReturnValue = CHANGE_TRACKING_CURRENT_VERSION()',
  N'@ReturnValue BIGINT OUTPUT',
  @ReturnValue = @CHANGE_TRACKING_CURRENT_VERSION OUTPUT

SELECT @CHANGE_TRACKING_CURRENT_VERSION

DECLARE @CHANGE_TRACKING_CURRENT_VERSION BIGINT;

EXEC sys.sp_executesql
  N'USE Production;
    SELECT @ReturnValue = CHANGE_TRACKING_CURRENT_VERSION()',
  N'@ReturnValue BIGINT OUTPUT',
  @ReturnValue = @CHANGE_TRACKING_CURRENT_VERSION OUTPUT

SELECT @CHANGE_TRACKING_CURRENT_VERSION
Related Question