Link to home
Start Free TrialLog in
Avatar of pae2
pae2Flag for United States of America

asked on

exec SQL Server Change Tracking CurrentVersion() across dbs

I setup SQL Server Change Tracking (not Change Data Capture) on the 'UDB' database.

The 'UDB' is the vendor's db. So all of the Change Tracking sprocs and tables are stored in the master db.

Everything works, EXCEPT when trying to check the change tracking / current version of 'UDB'.

In other words, I can't do this in a sproc or function:

USE master
GO
CREATE PROCEDURE AS
USE UDB -- this is not allowed
GO
SELECT CHANGE_TRACKING_CURRENT_VERSION();

How can I get the change tracking / current version of UDB without creating a sproc or function in UDB?

Thanks!

pae2
Avatar of HainKurt
HainKurt
Flag of Canada image

does this work?

SELECT udb.dbo.CHANGE_TRACKING_CURRENT_VERSION();
Avatar of pae2

ASKER

Huseyin - that does not work. Any other ideas?
try this

DECLARE @db SYSNAME, @sql NVARCHAR(4000)
SET @db='UDB'
SELECT @sql = @db + '..sp_executesql N''CHANGE_TRACKING_CURRENT_VERSION()'''
EXEC sp_executesql @sql

Open in new window

I tried this on master database

DECLARE @db SYSNAME, @sql NVARCHAR(4000)
SET @db='EE'
SELECT @sql = @db + '..sp_executesql N''select db_name() db'''
EXEC sp_executesql @sql

select db_name() db;

Open in new window


and I got this

db
EE

db
master

Open in new window


so I guess this is what you are trying to do...
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pae2

ASKER

Excellent - thank you Sir!