Geek asks and answers

Can I find out who called the warehouse procedure in SQL Server 2005

Is it possible to find out who called the store procedure ?, I use the following query to determine the amount of execution, etc., but I can’t determine which task / trigger / process calls it. Any ideas on this

SELECT  a.execution_count ,OBJECT_NAME(objectid) Name,
(CASE WHEN a.statement_end_offset = -1 
THEN
 len(convert(nvarchar(max), b.text)) * 2
ELSE
 a.statement_end_offset
END - a.statement_start_offset)/2 ) ,b.dbid ,dbname = db_name(b.dbid) , b.objectid
 ,a.creation_time,a.last_execution_time,a.* 
FROM  sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b
WHERE OBJECT_NAME(objectid) = 'Rebuild_Indexes' ORDER BY a.last_execution_time
ESCquery_text = SUBSTRING(b.text,a.statement_start_offset/2,
+5
source share
4 answers

Use Adam Machanic Who is the active stored procedure - this returns all information about the active statements, including the user who started them.

+3
source

, , - SP:Completed. , , .

: BOL SP:Completed

+3

, SQL, NT, , . , . .

+2

Use the Dynamic Management Views option . DMVs provide a simple and familiar relational interface for collecting critical system information from your SQL Server.

SELECT DB_NAME(der.database_id) AS databaseName,
       OBJECT_NAME(objectid),
       der.session_id,
       login_name,
       USER_NAME(der.user_id) AS user_name,
       der.command,
       dest.text AS [CommandText],
       des.login_time,
       des.[host_name],
       dec.client_net_address,
       des.[program_name],       
       der.status
FROM sys.dm_exec_requests der
  INNER JOIN sys.dm_exec_connections dec ON der.session_id = dec.session_id
  INNER JOIN sys.dm_exec_sessions des ON der.session_id = des.session_id
  CROSS APPLY sys.dm_exec_sql_text (sql_handle) AS dest
WHERE des.is_user_process = 1 
  --AND OBJECT_NAME(objectid) = 'Rebuild_Indexes'
+1
source

More articles:


All Articles