Geek asks and answers

How to return the name of the remote database that calls the stored process in another database?

I have several different databases on the same SQL Server 2008 R2. For arguments, let's call them DB_A, DB_B, and DB_C. I was asked to develop as a stored procedure that will work on DB_A. This saved proc will be used to delete and create indexes, as well as to store additional index information in a table on DB_A. When this stored proc is called from DB_C or DB_C, it will be able to drop and create indexes in the calling database, but store additional information about the index in a table on DB_A.

Here is what I would like to do: I would like the stored process to be able to get the name of the calling database WITHOUT having to query the database name as a parameter.

Here is a simple example:

USE [DB_A]

CREATE PROC sp_WhatDatabaseAmICallingFrom
AS 
BEGIN
      DECLARE @calling_db NVARCHAR(128)
      SET @calling_db = DB_NAME()
      PRINT 'calling database: ' + @calling_db
END

When I execute the stored procedure in DB_A ...

EXEC sp_WhatDatabaseAmICallingFrom

... it returns: " calling database: DB_A "

When I execute the stored procedure in DB_B ...

USE DB_B
GO

EXEC DB_A.dbo.sp_WhatDatabaseAmICallingFrom

... it returns: " calling database: DB_A ".

After reading the various SQL Server metadata functions, this is exactly what it should do. But I would like to change the code so that it sets @calling_db to the name of the calling database, so my stored procedure example will print: " database call: DB_B ".

Unfortunately, I cannot find any metadata functions that can do this. Any ideas on how to do this?

+6
source share
3 answers

SP , SP master .

USE MASTER 
GO 

CREATE PROC sp_WhatDatabaseAmICallingFrom
AS 
BEGIN
      DECLARE @calling_db NVARCHAR(128)
      SET @calling_db = DB_NAME()
      PRINT 'calling database: ' + @calling_db
END
GO

EXEC sp_ms_marksystemobject 'sp_WhatDatabaseAmICallingFrom'
GO

, :

USE [DB_A]
GO

EXEC sp_WhatDatabaseAmICallingFrom
GO
+4

, , -, .

DB_A DB_B, :

USE DB_A
EXEC db_b.dbo.sproc

, , " ", - sys.dm_tran_locks . request_session_id spid, resource_type DATABASE, request_owner_type SHARED_TRANSACTION_WORKSPACE. . :

SELECT resource_database_id FROM sys.dm_tran_locks WHERE request_session_id = @@SPID and resource_type = 'DATABASE' and request_owner_type = 'SHARED_TRANSACTION_WORKSPACE'

, SERVER STATE . , ..

+3

, , Sql2012.

, ('VIEW SERVER STATE'), , , .

USE [DB_A];
GO

CREATE FUNCTION dbo.GetCallingDbCatName()
RETURNS nvarchar(128) 
WITH EXECUTE AS SELF
AS
BEGIN
    DECLARE @result nvarchar(128);
    SELECT TOP 1 @result = DB_NAME(resource_database_id) 
        FROM sys.dm_tran_locks 
        WHERE request_session_id = @@SPID 
            AND resource_type = 'DATABASE' 
            AND request_owner_type = 'SHARED_TRANSACTION_WORKSPACE' 
        ORDER BY IIF(resource_database_id != DB_ID(), 0, 1);
    RETURN @result;
END
GO

USE [DB_A];
SELECT DB_A.dbo.GetCallingDbCatName(); --"DB_A"
USE [DB_B];
SELECT DB_A.dbo.GetCallingDbCatName(); --"DB_B"
USE [DB_C];
SELECT DB_A.dbo.GetCallingDbCatName(); --"DB_C"

, dbcat.

2019-07-26 - : , ( ) dbcat, master tempdb, SHARED_TRANSACTION_WORKSPACE. ( , )

SHARED_TRANSACTION_WORKSPACE - SQL Server EXCLUSIVE_TRANSACTION_WORKSPACE , , , , . , SQL Server tempdb, , . , tempdb, , , , , SHARED_TRANSACTION_WORKSPACE .

, , DB_A DB_B, 2 ; DB_B, , DB_A, /. , (DB_A func DB_B, func DB_C, dbcat; ORDER BY DB_A DB_B.)

Finally, this solution also works with the CLR method. Do not forget to execute the Sql function of the “shell” with sufficient permission to use db_tran_locks, and the attribute SqlFunctionshould have “SystemDataAccess = SystemDataAccessKind.Read”. The context connection uses the same SPID as the caller, and it is located in the dbcat where the assembly lives (and therefore where the wrapper function / sproc lives, which provides the CLR method.)

For reference, here is my test CLR method:

[SqlFunction(IsDeterministic = false, DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read), SqlMethod(OnNullCall = true)]
public static SqlString GetCallingDbcatName()
{
    string sqlResult = "";
    using (var connection = new System.Data.SqlClient.SqlConnection("context connection=true"))
    {
        connection.Open();
        var sqlCmd = connection.CreateCommand();
        sqlCmd.CommandText = @"
SELECT TOP 1 DB_NAME(resource_database_id) 
  FROM sys.dm_tran_locks
  WHERE request_session_id = @@SPID
    AND resource_type = 'DATABASE'
    AND request_owner_type = 'SHARED_TRANSACTION_WORKSPACE'
  ORDER BY IIF(resource_database_id != DB_ID(), 0, 1); ";
        sqlResult = sqlCmd.ExecuteScalar().ToString();
    }
    return sqlResult;
}
0
source

More articles:


All Articles