Record Usage of a Stored Procedure
-
We have a couple of stored procedures that usage of must bee tracked and recorded. Example:
SmithJ2 executed sp_who2 at 15:22 1/22/2005. This information would be stored in a table. I prefer not to use profiler.
Any suggestions for this type of recording.
Thank you
Stacey W. A. Gregerson -
if you are not planing to use profiler and do not plan to modify the sps the only thing I know will be Entegra
HTH
* Noel -
yes I can modify the procedures. But how?
Stacey W. A. Gregerson -
if you are willing to do that then is just a matter of opening the procedure on query analyzer and add an extra line :
insert into DBA.dbo.Access ( Who, When, What ) values ( suser_sname(), getdate(), object_name(@@procid) )
where
DBA is a globaldatabase
Access is a table with three fileds Who vachar(50) when datetime and what nvarchar(128)
Now be aware that you are changing ms stored procedures and that may lead to loose support from them. I understand that this may not be a big problem but at that point you are on your own
HTH
* Noel -
Thank you I will give it a try. I have used a trigger before on tables befor just never for procedures. Did not know about the (@@procid)
Thanks again
Stacey W. A. Gregerson -
..noeld wrote:
..Now be aware that you are changing ms stored procedures and that may
..lead to loose support from them. I understand that this may not be a big
..problem but at that point you are on your own
A solution to this is to deny access and execute privileges to anyone but you, and replace de ms sps with sps which call ms sp and save te record... obvioulsy this sp encrypted. so, e.g., instead of using "spWho", your users must use "spWho_", if you create a convention (e.g. spname + "_") it would not be too much difficult to use...once anyone knows about it...
The big deal is that is a bit hard to implement... but it would be automated...
-
Hi there,
We ran across the same situation - wanting to record use of a stored procedure, including its final status. Originally, we just added a line to the stored procedure that recorded its own execution in a log table, but that failed if the transaction rolled back for anything.
Now we have a "header" procedure that we run at the top of each stored procedure. We pass in the calling procedure's name. The header procedure uses osql to write a log entry, and returns an identity value that indicates the row created in the log table. That way, even if the SP fails, we have a record that it tried to run.
We also have a "footer" procedure that can take the identity value passed in from the header procedure and post the final status of the procedure's execution. Also uses osql so it doesn't rollback on failure.
Kimber
-
I've seen the Header-Footer idea implemented before but with no need for osql if you save the results on a table DATATYPE it won't be involved in any transaction rollback and at the end you could log the results from that table
HTH
* Noel -
In our case, we couldn't do the memory table (table datatype) because we the procedure could be being called by another one, and it could be inside a transaction that it doesn't have any control over.
-
Kimberly,
Again, a Table Datatype variables are NOT affected by transaction rollbacks, so it really does not matter and btw table variables are not a memory-only always
check this out HTH
* Noel
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply