GetFailedMessagesForProcessing

[Carlos Tijerina]

Hello,

   Is the below a Neuron fail message procedure?  Looks like the new one expects a ROW parameter and I was checking to see how I set this value.  I have a C# step where I set the exception message, then call a process that will call the stored procedure.  I've attached a screenshot.

 

Before Upgrade

ALTER PROCEDURE [dbo].[GetFailedMessagesForProcessing]

AS

BEGIN

      SET NOCOUNT ON;

      SET XACT_ABORT ON

      SET TRANSACTION ISOLATION LEVEL READ COMMITTED

     

      DECLARE @AuditTable table (

                  Sequence int NOT NULL,

                  Topic nvarchar(355) NULL,

                  MessageId nvarchar(36) NOT NULL,

                  Party nvarchar(100) NOT NULL,

                  Created datetime NOT NULL,

                  Machine nvarchar(15) NOT NULL,

                  Upn nvarchar(255) NULL,

                  failure_type nvarchar(100) NOT NULL,

                  failure_detail nvarchar(500) NOT NULL,

                  failure_date datetime NOT NULL,

                  MessageBody nvarchar(max) NOT NULL,

                  BodyType nvarchar (100) NULL);

 

      INSERT @AuditTable

      SELECT Sequence,  'Topic'=

 

After Upgrade

ALTER PROCEDURE [dbo].[GetFailedMessagesForProcessing]

      @Rows int

AS

BEGIN

      SET NOCOUNT ON;

      SET XACT_ABORT ON

      SET TRANSACTION ISOLATION LEVEL READ COMMITTED

     

      IF(@Rows < 1)

            BEGIN

                  SET @Rows = 1

            END

           

      DECLARE @AuditTable table (

                  Sequence int NOT NULL,

                  Topic nvarchar(355) NULL,

                  MessageId nvarchar(36) NOT NULL,

                  Party nvarchar(100) NOT NULL,

                  Created datetime NOT NULL,

                  Machine nvarchar(15) NOT NULL,

                  Upn nvarchar(255) NULL,

                  failure_type nvarchar(100) NOT NULL,

                  failure_detail nvarchar(500) NOT NULL,

                  failure_date datetime NOT NULL,

                  MessageBody nvarchar(max) NOT NULL,

                  BodyType nvarchar (100) NULL);

 

      INSERT @AuditTable

      SELECT TOP (@Rows) Sequence,  'Topic'=

            CASE

 

EXCEPTION CODE

//get the pipeline exception

Exception ex = (PipelineException)context.Properties["PipelineException"];

string

msg = "Exception: ";

if

(ex.InnerException != null)

{

msg += ex.InnerException.Message;

}

else

{

msg += ex.Message;

}

//trace to designer window

context.Instance.TraceError(msg);

Thanks,

[Martin Wasznicky]

Hi Carlos,

Why are you calling this stored procedure directly?  This returns N number of rows of failed messages to display in our Failed messages screen within the Neuron ESB Explorer

[Carlos Tijerina]

We received the code from Joe or Jereme, I cannot recall, but we needed a way to capture the message and receive an email alert.  We do not receive many errors, but when we do of course we get several.

[Carlos Tijerina]

Do you have another way so we can receive email alerts when an failed error message occurs?

[Carlos Tijerina]

Marty,

  Can you look at the SqlAdapterProcedure screen shot I attached?  You can call a procedure within the adapater but how do I pass a parameter in this adapter?