locked
Transaction Ended in Trigger - Trying to Change AS2 Agreement AS2-From ID RRS feed

  • Question

  • What is the get-around for this confusing and unhelpful error?  Other than running SQL profiler, is there anyway to actually understand what the error is complaining about? 

    I had similar problem with EDI agreements, and I ended up using T-SQL to a delete row (or two) from the BusinessIdentity table, which was a little scary. 

    When you click the down arrow to show the available items to select from, where is that being populated from?  

    Thanks,

    Neal Walters 



    Friday, June 16, 2017 6:33 PM

All replies

  • Hi Neal

    It should be in the [BizTalkMgmtDb].[tpm].[Agreement] table.


    Thanks Arindam

    Saturday, June 17, 2017 2:28 AM
  • But isn't this a bug - the tool shouldn't end with trigger errors. 

    Is it safe to delete rows from these tables? 

    Neal 

    Monday, June 19, 2017 2:01 PM
  • Hi Neal

    Probably in a dev environment - but can't be sure.

    Did you get a chance to run SQL Profiler to see what is actually happening?


    Thanks Arindam

    Tuesday, June 20, 2017 1:12 PM
  • it's been a long time since this topic was published but there's no answer so I'll add it in case of someone still needs it.

    The error you're seeing happens when BizTalk is trying to save a party with the same Identities (Name - Qualifier - Value).

    After doing the SQL Profiler, the instruction that is being executed when you click on the save button is the next one.

    exec sp_executesql N'insert [tpm].[BusinessIdentity]

    ([ProfileId], [Name], [Description], [Qualifier], [Value], [AdditionalData], [DateModified])

    values (@0, @1, @2, @3, @4, @5, @6)select [Id], [version]from [tpm].[BusinessIdentity]

    where @@ROWCOUNT > 0 and [Id] = scope_identity()',N'@0 int,@1 nvarchar(256),@2 nvarchar(256),

    @3 nvarchar(64),@4 nvarchar(256),@5 nvarchar(256),@6 datetime2(7)',@0=144,@1=N'QualifierIdentity',

    @2=N'U.S. Federal Tax Identification Number',@3=N'30',@4=N'123456789',@5=N'x12',

    @6='2020-12-10 16:43:55.5420058'

    which generates the error:

    The trigger in the message is executed when trying to insert values in the [BizTalkMgmtDb].[tpm].[BusinessIdentity] table.

    Also, that trigger, validates that there's no duplicated Qualifier + Id, so you can run the following query to verify what's the PartnerId that is generating the error.

    SELECT   PartnerId        FROM tpm.BusinessProfile AS BP 
            inner join tpm.BusinessIdentity AS BI 
            on BP.ProfileId = BI.ProfileId
            WHERE BI.Qualifier = '30' AND BI.Value = '123456789'
    Then, you can run a single query to verify the profile name that is causing the error with the previous PartnerID result.

    SELECT *
      FROM [BizTalkMgmtDb].[tpm].[Partner]
      WHERE [PartnerId] = '123456789'

    It will help you to identify what profile is in conflict with the one you're saving, creating or importing.

    Thanks


    Jay_villarreal


    Friday, December 11, 2020 12:39 PM