SQL-Server：是否有相当于一般存储过程执行的触发器
希望你能帮忙。
有没有一种方法可以可靠地检测存储过程何时在 SQL Server 上运行，而无需更改 SP 本身？
这是要求。我们需要跟踪从我们的企业数据仓库运行报告的用户，因为我们使用的核心产品不允许这样做。核心产品报告和我们添加的大量内部报告都从单独的存储过程返回数据。
我们没有实用的方法来更改产品网页中调用报告的部分。我们也无法更改核心产品报告的存储过程。 （在我们每个内部日志的开头/结尾添加一条日志记录线是很简单的）。
因此，我试图找到的是，SQL Server（2005 / 2008）中是否有一种方法可以在任何其他存储过程运行时执行日志记录存储过程，而无需更改这些存储过程本身。
我们对 SQL Server 实例本身具有一般控制权，因为它是本地的，我们只是不想更改产品存储过程本身。
有人有什么想法吗？是否有一种“存储过程执行触发器”？是否有一个 SQL Server 事件模型可供我们将自定义 .Net 代码挂钩？ （只是为了从一开始就打折扣，我们想尝试对 SQL Server 进行更改，而不是捕获从产品网页等运行的报告）
感谢您的想法
谢谢
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以设置一个在 SQL Server 初始化时自动运行的后台跟踪。然后，在跟踪中，您可以将跟踪语句输出到表中。
例如，打开 SQL Server Profiler。创建您想要的跟踪，即包括 SP:Starting 和您想要的列。还可以选择将跟踪保存到表中。
现在，设置脚本后，选择“文件/导出/脚本跟踪定义”。这将创建一个生成跟踪的 SQL 语句。
接下来，创建一个使用 SQL 创建此跟踪的存储过程。在master数据库中安装proc并告诉SQL Server在启动时自动运行它：
现在每次SQL Server重新启动时，它都会自动配置您的跟踪，并且所有SP调用都将记录到表中。
-- 编辑 --
另请注意，有一些动态管理视图 (DMV) 对于监视存储过程很有用。您可能感兴趣的两个：
例如，sys.dm_exec_procedure_stats 会告诉您 proc 上次运行的时间、运行了多少次、最长执行时间等。请注意，这些视图仅影响当前在数据库缓存中的存储过程。如果进程被卸载，信息也将被卸载。
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.
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.
Now, after setting up the script, choose File/Export/Script Trace Definition. This will create a SQL statement that generates the trace.
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:
Now every time SQL Server restarts, it will configure your trace automatically, and all SP invocations will be logged to a table.
-- EDIT --
Also note that there are some Dynamic Management Views (DMVs) useful for monitoring stored procedures. Two you might be interested in:
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 Profiler：没有可以使用的执行挂钩或“执行触发器”。
也就是说，您可以间接但不能 100% 可靠地监视计划缓存。请参阅此链接。但是，如果您有 OPTION RECOMPILE，那么它会在使用后从缓存中删除。此外，统计信息会在服务器启动后重置，您需要运行 dmv 来查看缓存中的内容。
这取决于你需要有多彻底......
You can only use SQL Profiler reliably: there is no execute hook or "execution trigger" to use.
Saying that, you can indirectly but not 100% reliably monitor the plan cache. See this link. However, if you have OPTION RECOMPILE then it is removed from the cache after use. Also, stats are reset after server start and you need to run the dmv to see what's in the cache too.
It depends how thorough you need to be...
您可以使用SQL Log Rescue（免费）等工具来查询您的SQL日志文件
http://www.red-gate.com/products/SQL_Log_Rescue/index.html嗯
它可以告诉您哪些存储过程已运行以及哪些存储过程已运行。什么时候。
这是最简单的方法，因为它不涉及修改任何现有代码，也不会增加应用程序和应用程序的开销。服务器。
You can use a tool such as SQL Log Rescue(free) to query your SQL Log files
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm
It can tell you which stored procedures have been run & when.
This is the easiest method, since it doesn't involve modifying any existing code and doesn't increase the overhead of the applications & server.