如何在Sql Server（2008）中向存储过程添加跟踪/调试输出

What is the closest to being able to add log4net style debug information to a set of stored procedures? Some of procedures delegate work to other procedures and I want trace information from both.

能够将log4net样式调试信息添加到一组存储过程的最接近的是什么？一些程序将工作委托给其他程序，我想从两者中获取跟踪信息。

I've sprinkled print and select statements through while developing them, and ideally would like to be able to run the proc in different modes depending on whether it's normal operation of troubleshooting and get more or less output.

我在开发时已经打印了print和select语句，理想情况下希望能够以不同的模式运行proc，具体取决于它是否是正常的故障排除操作并获得更多或更少的输出。

In this particular case the primary stored proc will be run repeatedly from an agent job, but may be run from management studio when troubleshooting.

在此特定情况下，主存储过程将从代理作业重复运行，但可在故障排除时从管理工作室运行。

The procs already use an error log table and email facilities to catch raised errors. The kind of thing I'm looking to be able to track down is where an input data issue means the output data is wrong but the procs don't fail completely, and it would be useful to see exactly what was done at each step.

procs已经使用错误日志表和电子邮件工具来捕获引发的错误。我希望能够跟踪的事情是输入数据问题意味着输出数据错误但是procs没有完全失败，并且确切地看到每个步骤完成了什么是有用的。

What approaches do you know of for producing meaningful and ideally filterable output?

您知道什么方法可以产生有意义且理想的可过滤输出？

One per answer so we can see the final rankings ;-)

每个答案一个，所以我们可以看到最终的排名;-)

Out of the box answers welcome, such as "don't - step through with management studio when you need to"

开箱即用的答案欢迎，例如“不要 - 在你需要的时候通过管理工作室”

3 个解决方案

#1

Piles of print statements

成堆的印刷声明

例如

print 'Doing something...'
INSERT INTO foo(a) VALUES(1)
print @@ROWCOUNT

This answer just for balance, seeing as it's quiet round here.

这个答案只是为了平衡，看到它在这里很安静。

#2

One approach might be to check if the proc is being run from SSMS and fire some Custom user configurable SQL Server Profiler events if so. e.g.

一种方法可能是检查是否从SSMS运行proc并激活一些自定义用户可配置的SQL Server Profiler事件（如果是）。例如

CREATE PROC foo
AS
DECLARE @debug bit = CASE WHEN APP_NAME() = 'Microsoft SQL Server Management Studio - Query' 
                          THEN 1 
                          ELSE 0 END

DECLARE @userinfo nvarchar(128)
DECLARE @userdata varbinary(8000)

--Do some work here

IF @debug = 1
BEGIN
--Do some custom logging 
    SET @userinfo = N'Some custom info'
    SET @userdata = CAST('Some custom data' AS varbinary(8000))
    EXEC sp_trace_generateevent @eventid = 82 /*Use 82-91*/
                               ,@userinfo = @userinfo 
                               ,@userdata = @userdata

END

Though in practice I usually use something like the below. Sometimes with additional code to log the message (and possibly step and status values) to a table depending on requirements. This would allow the "filterable" requirement to be met.

虽然在实践中我通常使用类似下面的东西。有时会根据需要使用其他代码将消息（以及可能的步骤和状态值）记录到表中。这将允许满足“可过滤”的要求。

This stops the message being printed out except if the APP_NAME indicates it is being run in (an English language version of) SSMS and uses NOWAIT so the message gets printed out immediately rather than waiting for the buffer to fill.

这会停止打印出来的消息，除非APP_NAME指示它正在运行（英语版本）SSMS并使用NOWAIT，因此消息会立即打印出来而不是等待缓冲区填充。

CREATE PROCEDURE [dbo].[PrintMessage] @message            NVARCHAR(MAX),
                                      @PrependCurrentTime BIT = 0
AS
    IF APP_NAME() LIKE 'Microsoft SQL Server Management Studio%' 
      BEGIN
          DECLARE @CurrentTimeString VARCHAR(30);

          SET @CurrentTimeString = ''

          IF @PrependCurrentTime = 1
            BEGIN
                SET @CurrentTimeString = CONVERT(VARCHAR(19), GETDATE(), 20) + ' '
            END

          RAISERROR('%s%s',0,1,@CurrentTimeString,@message) WITH NOWAIT

      END

#3

Don't add tracing output, instead just step through as needed with the sql server management studio debugger.

不要添加跟踪输出，而是根据需要使用sql server management studio调试器逐步执行。

(Added to see if people prefer this)

（添加以查看是否有人喜欢这个）

#1