Is it possible to query the @@DBTS on a database other than the current database?

Question

For a rigorous marker of the source database state, I'd like to capture the @@DBTS of an external database in a sproc. Yeah, I think I could issue


USE ExternalDB
GO

SELECT @myVarbinary8 = @@DBTS
GO

USE OriginalDB
GO

but, even if I could, it seems ugly.

For now, I've embedded a scalar-valued function in the source database to invoke the

SET @Result = SELECT @@DBTS

which worked fine until I forgot to ask the DBA to grant the appropriate rights for a new user, which crashed a process.

Something akin to

SELECT ExternalServer.dbo.@@DBTS

(I know that doesn't work).

See   MSDN @@DBTS documentation

@@DBTS (Transact-SQL)
Returns the value of the current timestamp data type for the current database.
This timestamp is guaranteed to be unique in the database.

Answer 1

one way is to put that scalar function in the master database and mark it as system object. that way it gets called in the context of the current database see here for more info: http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx

Answer 2

Thanks for the info, Mladen, that tip is good to know :)

But while that helps me call a function residing in master from in the current database context "ContextCurrent", what I really want is to be able to call the scalar-valued function from the context of the source database "ContextSource".

While in general, I have my reservations against dynamic sql, I ended up using it here as follows.


DECLARE @sourceDbName nvarchar(128)
SET     @sourceDbName = N'sbaportia1'

DECLARE @strQuery nvarchar(max)
DECLARE @parmDefn nvarchar(max)
DECLARE @DbtsCapture varbinary(8)
SET    @strQuery =
   '
          N' ' + N'USE' + N' ' + @sourceDbName + N' '
        + N' ' + N'SELECT @dbtsCapture = min_active_rowversion()'
   '

SET @parmDefn =
   N'
        @dbName varchar(128),
        @dbtsCapture varbinary(8) OUTPUT
   '

EXEC sp_executesql  @strQuery
                   ,@parmDefn
                   ,@dbName = 'autobahn'
                   ,@dbtsCapture = @dbtsCapture OUTPUT

SELECT @dbtsCapture

Furthermore, since sp_executesql runs in a separate thread, the database context within the script below will be automatically be the same upon the exit of sp_executesql as upon the entry of sp_executesql. (I learned waaayy too much about sp_executesql in the early 2000's.)

Answer 3

so what's wrong with this:

USE master
go
CREATE PROC dbo.sp_GetMinActiveRowVersion
(
    @activeRowVersion BINARY(8) OUT
)
AS
    -- to get it in resultset as well as the output variable
    SELECT @activeRowVersion = min_active_rowversion()
    --SELECT min_active_rowversion()
GO
USE AdventureWorks
DECLARE @activeRowVersion BINARY(8)
EXEC sp_GetMinActiveRowVersion @activeRowVersion out
SELECT @activeRowVersion AS AdventureWorksRowVersion

EXEC [master]..sp_GetMinActiveRowVersion @activeRowVersion out
SELECT @activeRowVersion AS MasterRowVersion

EXEC msdb..sp_GetMinActiveRowVersion @activeRowVersion out
SELECT @activeRowVersion AS MsdbRowVersion

go
USE master 
DROP PROC sp_GetMinActiveRowVersion