How to get Calling DB name within a stored procedure

  • Hi All,

    I have a log database where I have all the stored procedure for logging to log tables.

    There are two databases - Dev and Test , which has stored procedures where we will be calling the LogDB stored procedures for logging.

    In log tables, we have a column to specify whether the logging is for Test or Dev DB.

    I need to know, if there is some mechanism by which I can get the name of the Calling DB in my logging procedures.

    Eg.

    I am executing sp1 from TestDB and this sp1 is calling SPLog in LOGDB.

    Can I know the name of TestDB in SPLog, so that I can differentiate whether this execute request came from TestDB or DevDB

  • er.mayankshukla (10/8/2014)


    Hi All,

    I have a log database where I have all the stored procedure for logging to log tables.

    There are two databases - Dev and Test , which has stored procedures where we will be calling the LogDB stored procedures for logging.

    In log tables, we have a column to specify whether the logging is for Test or Dev DB.

    I need to know, if there is some mechanism by which I can get the name of the Calling DB in my logging procedures.

    Eg.

    I am executing sp1 from TestDB and this sp1 is calling SPLog in LOGDB.

    Can I know the name of TestDB in SPLog, so that I can differentiate whether this execute request came from TestDB or DevDB

    Quick question, can you pass those information using a parameter in the stored procedure?

    😎

    On the originating server, grab the info like this

    USE tempdb;

    GO

    SET NOCOUNT ON;

    GO

    SELECT

    DB_NAME() AS DATABASE_NAME

    ,@@SERVERNAME AS SERVER_NAME;

    Another option is to use the applicable Security Functions (Transact-SQL) as a default value in the destination table

    DECLARE @LOG_STUFF TABLE

    (

    LOGSTUFF_ID INT NOT NULL

    ,CALLING_USER VARCHAR(50) NOT NULL DEFAULT (USER_NAME())

    );

    INSERT INTO @LOG_STUFF(LOGSTUFF_ID) VALUES(1);

    SELECT * FROM @LOG_STUFF

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply