让sp_tracegenerateevent在存储过程中工作

Question

我有一个艰难的时间调试从BizTalk调用的存储过程。

在之前的线程中，有人建议使用sp_trace_generateevent。[当外部进程调用存储进程时，使用SQL调试器

由于我需要显示一个变量，我想出了以下方法，它是有效的，我可以在SQLProfiler中看到值（with EventClass=UserConfigurable： 0"

Declare @message nvarchar(128)
Set @message = 'Hello World 2 ' 
exec sp_trace_generateevent @event_class=82, @userinfo = @message 

但是，当我将它放在问题存储过程的“BEGIN CATCH”中时，我在分析器中看不到任何东西:

BEGIN CATCH 
   DECLARE @message nvarchar(128)
   SET @message = LTRIM(STR(ERROR_MESSAGE())) 
   exec sp_trace_generateevent @event_class=82, @userinfo = @message 
   SET @CatchErrors = 
            'Catch: [LTL].[CreateShipment]  - ErrorNumber: '+LTRIM(STR(ERROR_NUMBER()))
            + ' ErrorSeverity: '+LTRIM(STR(ERROR_SEVERITY()))
            + ' ErrorState: '+LTRIM(STR(ERROR_STATE()))
            + ' ErrorProcedure: '+LTRIM(STR(ERROR_PROCEDURE()))
            + ' ErrorLine: '+LTRIM(STR(ERROR_LINE()))
            + ' ErrorMessage: '+LTRIM(STR(ERROR_MESSAGE()))

END CATCH

然后我在Catch中放了一个Catch：

BEGIN CATCH 
   BEGIN TRY 
      DECLARE @message nvarchar(128)
      SET @message = LTRIM(STR(ERROR_MESSAGE())) 
      exec sp_trace_generateevent @event_class=82, @userinfo = @message 
   END TRY 
   BEGIN CATCH 
      SET @Message = 'Error in sp_trace_generateevent' 
   END CATCH 
   SET @CatchErrors = 
            'Catch: [LTL].[CreateShipment]  - ErrorNumber: '+LTRIM(STR(ERROR_NUMBER()))
            + ' ErrorSeverity: '+LTRIM(STR(ERROR_SEVERITY()))
            + ' ErrorState: '+LTRIM(STR(ERROR_STATE()))
            + ' ErrorProcedure: '+LTRIM(STR(ERROR_PROCEDURE()))
            + ' ErrorLine: '+LTRIM(STR(ERROR_LINE()))
            + ' ErrorMessage: '+LTRIM(STR(ERROR_MESSAGE()))


END CATCH

现在我可以在分析器中看到“SET @ Message = ' Error in sp _ trace _ generate event '”，但我真的需要看到错误的原因。

在SSMS中测试时，我遇到的问题无法重现，只有在从BizTalk调用时才能重现。我的意图是将@CatchErrors（作为输出参数）冒泡回BizTalk，但它也不起作用。

此外-BizTalk正在运行的用户SQLSysAdmin（它在我的开发机器上）。

使用母版时也有相同的结果..sp _ tracegeneratedevent

根据@耶鲁安的回复，我切换到这个，但仍然得到一些错误捕获。

   DECLARE @message nvarchar(128)
   BEGIN TRY 
      SET @message = Convert(nvarchar(128),SUBSTRING(ERROR_MESSAGE(),1,128))
      exec sp_trace_generateevent @event_class=82, @userinfo=@message 
   END TRY 

更新#1：这让我发疯。当我在SQL中测试时，它可以工作，但当我在BizTalk中测试时却不行。所以我真的想要一个调试功能。我现在有了我的渔获量，我的渔获物…他们都在捕鱼，我不知道为什么。在除以零的简单示例中，相同的代码工作良好。更复杂的是，这是一个存储过程，由BizTalk调用的存储过程调用。如果捕获错误，我应该能够在主存储过程和子存储过程中名为@CatchErrors的输出参数中将其返回到BizTalk。

BEGIN CATCH 
   DECLARE @message nvarchar(128)
   BEGIN TRY 
      SET @message = Convert(nvarchar(128),SUBSTRING(ERROR_MESSAGE(),1,128))
      exec sp_trace_generateevent @event_class=82, @userinfo=@message 
   END TRY 
   BEGIN CATCH 
      SET @Message = 'Error in sp_trace_generateevent' 
   END CATCH 

   BEGIN TRY 
       SET @CatchErrors = 
            'Catch: [RG].[CreateShipment]  - ErrorNumber: '+CAST(ERROR_NUMBER() AS VARCHAR(35)) 
            + ' ErrorSeverity: '+CAST(ERROR_SEVERITY() AS VARCHAR(35))
            + ' ErrorState: '+CAST(ERROR_STATE() AS VARCHAR(35)) 
            + ' ErrorProcedure: '+CAST(IsNull(ERROR_PROCEDURE(),'') AS VARCHAR(200)) 
            + ' ErrorLine: '+CAST(ERROR_LINE() AS VARCHAR(35)) 
            + ' ErrorMessage: '+CAST(ERROR_MESSAGE() AS VARCHAR(4000)) 
   END TRY 
   BEGIN CATCH 
      BEGIN TRY 
          SET @Message = 'Error in Set @CatchErrors=' 
          SET @CatchErrors = 
                'Catch: [LTL.CreateShipmentStopLineItem]- Error: ' + CAST(ERROR_MESSAGE() AS VARCHAR(4000)) 
      END TRY 
      BEGIN CATCH 
         SET @Message = 'Error in Set @CatchErrors2' 
      END CATCH 
   END CATCH 
END CATCH

更新#2 -在SSMS测试:

我正在SSM中测试，所有捕获都没有问题。如果我多次运行此命令，它会在Print语句中违反主键。

 Declare @shipstopline LTL.TT_ShipmentStopLineItem
 DECLARE @messageID bigint
 DECLARE @CatchErrorsResult varchar(max) 
 insert into @shipstopline values ('2', '1', 'Eggs','1','2','3','1','100','1','12','1','1','1','10','20','1')

 EXEC LTL.CreateShipmentStopLineItem @MessageId = 2, @ShipmentStopID=1, @CreateBy=108004, @ShipmentStopLineItem=@shipstopline, @loopid=1, @catchErrors=@CatchErrorsResult OUT 
 select RowCreated, * from LTL.ShipmentStopLineItem order by LTL.ShipmentStopLineItem.RowCreated desc
 print @CatchErrorsResult

Answer 1

事实证明，BizTalk调用了Stored Proc两次，一次使用FmtOnly=On，一次使用FmtOnly=Off。第一次调用后我得到了无效的强制转换异常，所以Stored Proc基本上没有真正执行。由于FmtOnly=On，我被分析器误导了，以为它实际上是在执行我在那里看到的语句。因此，sp_TraceGenerateEvent实际上并没有在第一次使用FmtOnly=On时运行，但后来当我克服其他错误时，它在第二次使用FmtOnly=off时运行良好。

参考:我发布的类似问题，关于为什么SQL事件探查器在相同存储过程的SSMS运行和BizTalk运行之间看起来不同:SQL Begin Try/Catch会对我撒谎吗(在事件探查器中)？

Answer 2

好吧，如果你试试这个：

SET XACT_ABORT ON;
BEGIN TRY   
    SELECT 1 / 0;
END TRY
BEGIN CATCH
    DECLARE @message nvarchar(128);
    SET @message = LTRIM(STR(ERROR_MESSAGE()));
    exec sp_trace_generateevent @event_class=82, @userinfo = @message
END CATCH

您将获得关于问题的即时反馈:

Msg 8114，级别16，状态5，第8行将数据类型nvarchar转换为float时出错。

这是因为您得到的STR（）调用不是正确的使用方式——STR格式化浮点值，并且仅限这些。（对于灵活的转换，请使用FORMAT和/或CONCAT，后者总是隐式地将事物转换为字符串而不抱怨。）

除此之外，存储过程的第一个参数是@eventid，而不是@event_class（这通常是一个错误，但扩展存储过程以这种方式更灵活 - 您可以将参数调用@banana，它仍然有效）。不过，为了清楚起见，我们应该使用记录的名称。所以：

SET XACT_ABORT ON;
BEGIN TRY   
    SELECT 1 / 0;
END TRY
BEGIN CATCH
    DECLARE @message nvarchar(128) = ERROR_MESSAGE();
    EXEC sp_trace_generateevent @eventid=82, @userinfo = @message;
END CATCH

在我的profiler中，这将生成一个＜code＞UserConfigurable:0＜code＞事件

除以零遇到的错误。

请注意，如果没有TRY/CATCH，您应该仍然能够通过Exception事件获得错误，即使没有生成自己的跟踪事件。