pae2
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_VE RSION();
How can I get the change tracking / current version of UDB without creating a sproc or function in UDB?
Thanks!
pae2
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_VE
How can I get the change tracking / current version of UDB without creating a sproc or function in UDB?
Thanks!
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
I tried this on master database
and I got this
so I guess this is what you are trying to do...
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;
and I got this
db
EE
db
master
so I guess this is what you are trying to do...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent - thank you Sir!
SELECT udb.dbo.CHANGE_TRACKING_CU