var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Log Usage of stored proc
My question is how can we log the usage of a stored proc in a specific table. for example let's take a stored proc
CREATE PROCEDURE dbo.Hills_GetO_ID
(@P_ID as Varchar(20))
Set Nocount On
Case When ISNUMERIC(O_ID)=1
End As O_ID
From dbo.Orders With(nolock)
I need to log the usage of this stored proc in to this table
CREATE TABLE dbo.ProcedureLog
LogDate SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
CREATE CLUSTERED INDEX cx_LogDate ON dbo.ProcedureLog(LogDate);
All i need to do is modify the store proc to log its usage in this table.
Thanks in Advance,
Create an insert statement to insert a row in to the table and add it to the procedure. You will have to use TRY...CATCH in order to trap any errors.
We do this sort of thing, however we don't just log sproc execution, we log each stage of the sproc. This is invaluable for debugging. A handy hint to keep your code generic:
The person executing needs VIEW_DEFINITION permissions if there are nested sprocs IIRC.
SELECT @proc_name = OBJECT_NAME(@@PROCID)
ur codings are working excelent.