最佳做法-存储过程记录 [英] Best Practices - Stored Procedure Logging

问题描述

如果您的SP运行时间很长，您会以某种方式记录它的操作还是只是等待此消息?

If you have a long running SP, do you log somehow its actions or just wait for this message?

命令已成功完成."

"Command(s) completed successfully."

我认为，在这个问题上可以有很多解决方案，但是有没有最佳实践-经常使用的简单解决方案?

I assume, that there can be plenty solutions on this subject, but is there any best practice - a simple solution that is frequently used?

编辑

我找到了与此主题有关的有趣链接

I've found an interesting link on this subject

http://weblogs.sqlteam.com/brettk/存档/2006/09/21/12391.aspx

文章介绍了如何使用日志表，但是存在问题

Article describes using a log table, but there's an issue

日志记录过程必须在任何事务之外执行

The logging procedure must be executed outside of any transaction

由于我使用了游标，所以无法在外部调用该插入操作.

I can't call that insert outside, because of cursor that I use and insert a line to that table on every row.

有什么想法吗?

编辑2

挖掘..

有一个 xp_logevent 在SQL Server中.你试过了吗?

there's a xp_logevent in SQL Server. Did you try it?

SQL Server Profiler 如何?

还有为存储过程创建日志文件

推荐答案

您如何调用存储过程?如果是通过Management Studio进行的，则可以按照以下步骤轻松打印出消息

How are you invoking the stored procedure? If it is through Management Studio then you can easily print out the message as follows

RAISERROR ('Some debugging info', 0, 1) WITH NOWAIT

这比使用PRINT更可取，因为该消息将立即显示.通过为 Connection.InfoMessage 事件.

This is preferable to using PRINT as the message will appear immediately. These messages can also be caught in ADO.NET by wiring up a handler for the Connection.InfoMessage event.

我看到您已经列出了SQL Profiler作为一种可能.您可能想知道您可以记录您自己的用户可配置事件，该事件可在SQL Profiler中看到.

I see that you have already listed SQL Profiler as a possibility. You might be interested to know that you can log your own user configurable events that can be seen in SQL Profiler.

这篇关于最佳做法-存储过程记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！