Hi All,
Hope you can help.
Is there a way to reliably detect when a stored proc is being run on SQL Server without altering the SP itself?
Here's the requirement. We need to track users running reports from our enterprise data warehouse as the core product we use doesn't allow for this. Both core product reports and a slew of in-house ones we've added all return their data from individual stored procs.
We don't have a practical way of altering the parts of the product webpages where reports are called from. We also can't change the stored procs for the core product reports. (It would be trivial to add a logging line to the start/end of each of our inhouse ones).
What I'm trying to find therefore, is whether there's a way in SQL Server (2005 / 2008) to execute a logging stored proc whenever any other stored procedure runs, without altering those stored procedures themselves.
We have general control over the SQL Server instance itself as it's local, we just don't want to change the product stored procs themselves.
Any one have any ideas? Is there a kind of "stored proc executing trigger"? Is there an event model for SQL Server that we can hook custom .Net code into?
(Just to discount it from the start, we want to try and make a change to SQL Server rather than get into capturing the report being run from the products webpages etc)
Thoughts appreciated
Thanks
