SQL-Server: Is there an equivalent of a trigger for general stored procedure execution -

Hope you can help.

What one reliably shows that when a stored procedure is running on the SP server without changing itself?

Here we need to track users running reports from our enterprise data warehouse because the main products we use do not allow this. Whatever the major product reports and many homes we have added, they all get back to their data from personal storage process.

We do not have a practical way to replace parts of the product webpage, where the report says that we can not change the stored procs for core product reports. (It will be trivial to add the logging line for the start / end of each of our in-house).

So what I am trying to find, is that there is a way to execute a stored procedure in SQL Server (2005/2008), whenever any stored procedure is stored in those stored procedures Change without changing / P>

We normally have general controls on the SQL Server instance because it is local, we do not want to change only the product we store.

Anyone have any ideas? Is there a type of "stored execution trigger"? What is an event model for SQL Server, which we custom Can hook code into net code? (To get rid of it from the beginning, we try and make changes to SQL Server, rather than to capture the reports running products from webpages).

Appreciate thoughts / thank you

You can set up a background trace that Automatically running when SQL Server starts. Then, in your trace, you can output a trace statement to a table. For example, open SQL Server Profiler. You want to create a trace, that is, SP includes: Getting Started and Columns You Want Also choose to save the trace in a table.

Now, after installing the script, choose File / Export / Script Trace Definition. This will create a SQL statement which generates a trace.

Next, create a stored procedure that creates this trace using SQL. Install Proc in the master database and tell SQL Server to run it automatically at startup:

  exec sp_procoption N'sp_MyProc ', N'startup', N'true ' 

Each time the SQL Server restarts, it will automatically configure your trace, and all the SP disables will be logged in a table.

- Edit

Also note that there are some dynamic management views (DMVs) useful for monitoring stored procedures, for example, proc will tell you when you last run: For example, sys.dm_exec_procedure_stats will tell you how often it was run, the longest execution time, etc. Note that these views only affect the procedures stored in the database cache. If proc is loading, then information will be there.