Dec 7, 2014

How to get the calling or invoking application name in SQL server

Sometime there is need to know who is calling or executing a script or stored procedure in SQL server. This is query has executed by application code or by SQL job or has executed manually by using SSMS. We get the application name by below meta data function:

SELECT APP_NAME() 

Below script get the application name of current running queries in last batch:

SELECT
     PROGRAM_NAME() AS ApplicationName,
     EST.text AS SQLScript,
     P.hostname AS HostingServerName,
     P.last_batch AS ExecutionTime    
FROM sys.sysprocesses
CROSS APPLY sys.dm_exec_sql_text(sql_handle) EST

Script to run stored procedure only if called by a specific application:

CREATE PROCEDURE uspLodaData
AS
BEGIN

     IF APP_NAME() = 'ExactHelpApp' BEGIN
    
          --Load data script
         
     END
    
END

No comments:

Post a Comment