使sp_tracegenerateevent在存储过程中工作 [英] Getting sp_tracegenerateevent to work in a stored procedure

问题描述

我很难调试从BizTalk调用的存储过程.

在先前的线程中，有人建议使用sp_trace_generateevent.[

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

  BEGIN CATCH声明@message nvarchar(128)SET @消息= LTRIM(STR(ERROR_MESSAGE()))exec sp_trace_generateevent @ event_class = 82，@userinfo = @消息SET @CatchErrors ='捕获:[LTL].[CreateShipment]-错误号:'+ LTRIM(STR(ERROR_NUMBER()))+'ErrorSeverity:'+ LTRIM(STR(ERROR_SEVERITY()))+'ErrorState:'+ LTRIM(STR(ERROR_STATE()))+'ErrorProcedure:'+ LTRIM(STR(ERROR_PROCEDURE()))+'ErrorLine:'+ LTRIM(STR(ERROR_LINE()))+'错误消息:'+ LTRIM(STR(ERROR_MESSAGE()))结束观看 

因此，我接着在Catch中放置了一个Catch:

  BEGIN CATCH开始尝试声明@message nvarchar(128)SET @消息= LTRIM(STR(ERROR_MESSAGE()))exec sp_trace_generateevent @ event_class = 82，@userinfo = @消息结束尝试开始比赛SET @Message ='sp_trace_generateevent中的错误'结束观看SET @CatchErrors ='捕获:[LTL].[CreateShipment]-错误号:'+ LTRIM(STR(ERROR_NUMBER()))+'ErrorSeverity:'+ LTRIM(STR(ERROR_SEVERITY()))+'ErrorState:'+ LTRIM(STR(ERROR_STATE()))+'ErrorProcedure:'+ LTRIM(STR(ERROR_PROCEDURE()))+'ErrorLine:'+ LTRIM(STR(ERROR_LINE()))+'错误消息:'+ LTRIM(STR(ERROR_MESSAGE()))结束观看 

现在我可以在探查器中看到"SET @Message ='sp_trace_generateevent中的错误'，但是我确实需要查看错误的原因.

仅当我从BizTalk呼叫时，在SSMS中进行测试时无法重现我遇到的问题.我的意图是将@CatchErrors(作为输出参数)冒泡回BizTalk，但它也不起作用.

也-BizTalk与具有SQL SysAdmin的用户一起运行(在我的开发计算机上).

使用master..sp_tracegenicevent时的结果也相同

基于@Jeroen的回复，我切换到了此选项，但仍然遇到了一些错误.

 声明@message nvarchar(128)开始尝试SET @消息=转换(nvarchar(128)，SUBSTRING(ERROR_MESSAGE()，1,128))exec sp_trace_generateevent @ event_class = 82，@ userinfo = @消息结束尝试 

更新1:这让我很生气.当我在SQL中进行测试时，它可以工作，但是当我从BizTalk中进行测试时，则不能.所以我真的想要一个调试功能.现在我的鱼钩上有鱼钩了……它们都在捕获，我不知道为什么.在零除简单示例中，相同的代码也可以正常工作.更复杂的是，这是一个存储过程，由BizTalk调用的存储过程调用.如果发现错误，则应该能够在主存储过程和子存储过程中将其返回到BizTalk的输出参数@CatchErrors中.

  BEGIN CATCH声明@message nvarchar(128)开始尝试SET @消息=转换(nvarchar(128)，SUBSTRING(ERROR_MESSAGE()，1,128))exec sp_trace_generateevent @ event_class = 82，@ userinfo = @消息结束尝试开始比赛SET @Message ='sp_trace_generateevent中的错误'结束观看开始尝试SET @CatchErrors ='捕获:[RG].[CreateShipment]-错误号:'+ 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))+'错误消息:'+ CAST(ERROR_MESSAGE()AS VARCHAR(4000))结束尝试开始比赛开始尝试SET @Message ='Set @CatchErrors中的错误='SET @CatchErrors ='渔获:[LTL.CreateShipmentStopLineItem]-错误:'+ CAST(ERROR_MESSAGE()AS VARCHAR(4000))结束尝试开始比赛SET @Message ='设置@ CatchErrors2中的错误'结束观看结束观看结束观看 

当前分析器结果:

更新#2-在SSMS中进行测试:

我正在SSMS中进行测试，但没有一个问题.如果我多次运行此命令，它将在Print语句中获得违反主键的情况.

 声明@shipstopline LTL.TT_ShipmentStopLineItem声明@messageID bigint宣告@CatchErrorsResult varchar(max)插入@shipstopline值('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按LTL.ShipmentStopLineItem.RowCreated desc的顺序从LTL.ShipmentStopLineItem中选择RowCreated，*打印@CatchErrorsResult 

解决方案

事实证明，BizTalk两次调用了存储过程，一次是使用FmtOnly = On，一次是使用FmtOnly = Off.第一次调用后，我得到了无效的强制转换异常，因此Stored Proc本质上并没有真正执行.由于FmtOnly = On，我被探查器误导了，认为它实际上是在执行我在那里看到的语句.因此，在FmtOnly = On的情况下，sp_TraceGenerateEvent实际上并未在第一遍运行，但是稍后，当我克服了其他错误时，在FmtOnly = Off的第二遍运行中，sp_TraceGenerateEvent仍然可以正常运行.

参考:我张贴了类似的问题，询问为什么SQL Profiler在具有相同存储过程的SSMS运行和BizTalk运行之间看起来有所不同:Use SQL Debugger when stored proc called by an external process

Since I need to display a variable, I came up with the following, and it works, I can see the value in SQL Profiler (with EventClass=UserConfigurable:0"

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

But when I put it in a "BEGIN CATCH" in the problem stored proc, I don't see anything in the profiler:

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

So then I put a Catch within the 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

And now I can see "SET @Message = 'Error in sp_trace_generateevent' " in the profiler, but I really need to see the reason for the error.

The problem I've having cannot be reproduced when testing in SSMS, only when I call from BizTalk. My intent is to bubble the @CatchErrors (as an output parameter) back to BizTalk, but it's not working either.

Also - BizTalk is running with a user that has SQL SysAdmin (it's on my development machine).

Also same result when using master..sp_tracegeneratedevent

Based on @Jeroen's reply, I switched to this, but still getting some error caught.

   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 

Update #1: This is driving me batty. When I test in SQL it works, but when I test from BizTalk it doesn't. So I really want a debug feature. I now have catch on my catches on my catches... and they are all catching and I don't know why. Same code works fine in the divide by zero simple example. To further complicate, this is a stored proc, called by a stored proc, called by BizTalk. If I catch the error, I should be able to return it to BizTalk in the output parameter called @CatchErrors in my main and sub-stored proc.

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

Current Profiler Result:

Update #2 - Testing in SSMS:

I'm testing in SSMS, and none of the catches have issues. If i run this more than once it gets Violation of Primary Key in the Print statement.

 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

解决方案

It turns out that BizTalk calls the Stored Proc twice, once with FmtOnly=On and once again with FmtOnly=Off. I was getting the invalid cast exception after the first call, so the Stored Proc was essentially not really executing. I was mislead by the profiler because of the FmtOnly=On, thinking that it was actually executing the statements I saw there. Thus the sp_TraceGenerateEvent doesn't actually run on the first pass with FmtOnly=On, but later when I got past the other errors, it works fine in the second pass with FmtOnly=Off.

Reference: Similar questoin I posted about why the SQL Profiler was looking different between an SSMS run and a BizTalk run of same stored proc: Can SQL Begin Try/Catch be lying to me (in the profiler)?

这篇关于使sp_tracegenerateevent在存储过程中工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！