5

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.

6
+100

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 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.