SQL-Server:是否有与常规存储过程执行等效的触发器 [英] SQL-Server: Is there an equivalent of a trigger for general stored procedure execution
问题描述
希望您能提供帮助.
是否有一种方法可以可靠地检测到何时在不更改SP本身的情况下在SQL Server上运行存储的proc?
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).
因此，我要寻找的是SQL Server(2005/2008)中是否有一种方法可以在运行任何其他存储过程时执行日志记录存储proc，而无需更改这些存储过程本身.
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.
我们对SQL Server实例本身具有本地控制权，我们只是不想自己更改产品存储过程.
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.
有人有什么想法吗?是否存在一种存储的proc执行触发器"?是否有我们可以将自定义.Net代码挂接到的SQL Server事件模型?(只是从一开始就打折，我们想尝试对SQL Server进行更改，而不是捕获从产品网页等运行的报告)
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
推荐答案
您可以设置在SQL Server初始化时自动运行的后台跟踪.然后，在跟踪中，可以将trace语句输出到表中.
You could setup a background trace that is run automatically when SQL Server initializes. Then, in your trace, you could output the trace statement to a table.
例如，打开SQL Server Profiler.创建所需的跟踪，即包括SP:Starting和所需的列.还可以选择将跟踪保存到表中.
For example, open up SQL Server Profiler. Create the trace you would want, i.e. include SP:Starting and the columns you want. Also choose to save the trace to a table.
现在，在设置脚本后，选择文件/导出/脚本跟踪定义".这将创建一个生成跟踪的SQL语句.
Now, after setting up the script, choose File/Export/Script Trace Definition. This will create a SQL statement that generates the trace.
接下来，创建一个存储过程，该存储过程使用SQL创建此跟踪.将proc安装在master数据库中，并告诉SQL Server在启动时自动运行它:
Next, create a stored procedure that creates this trace using SQL. Install the proc in the master database and tell SQL Server to run it automatically on startup:
exec sp_procoption N'sp_MyProc', N'startup', N'true'
现在，每次SQL Server重新启动时，它将自动配置您的跟踪，并且所有SP调用都将记录到一个表中.
Now every time SQL Server restarts, it will configure your trace automatically, and all SP invocations will be logged to a table.
-编辑-
还要注意，有些动态管理视图(DMV)对于监视存储过程很有用.您可能对以下两个感兴趣:
Also note that there are some Dynamic Management Views (DMVs) useful for monitoring stored procedures. Two you might be interested in:
例如，sys.dm_exec_procedure_stats会告诉您proc上次运行的时间，运行了多少次，执行时间最长等.请注意，这些视图仅影响当前数据库高速缓存中的存储过程.如果proc已卸载，则信息也会如此.
For example, sys.dm_exec_procedure_stats will tell you when the proc was last run, how many times it was run, the longest execution time, etc. Note that these views only affect stored procedures currently in the database cache. If the proc is unloaded, so will be the information.
这篇关于SQL-Server:是否有与常规存储过程执行等效的触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
