none
xml.modify in store procedure leads to an issue with WCF-SQL Adapter RRS feed

  • Question

  • Hi,

    we are callling a store procedure with a WCF-SQL Adapter via typed polling in wich we have something like

    DECLARE

    @xml XML;

    SELECT

    @xml =(

    SELECT <something> )

    SET @xml.modify(...)

    SELECT @XML AS XML

    after deploying the BTS application we get every time

    Error details: System.Data.SqlClient.SqlException (0x80131904): Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

    After a comment out of the @XML.modify all works fine and if it once work fine we can uncomment it back in the store procedure and it still works fine till we redploy the BTS application. This behavior is more than strange to us.

    Any one else facing this issuer or knowing this issue?

    KR

    Peter






     



    Wednesday, February 17, 2016 1:39 PM

Answers

All replies

  • Refer the article: https://bekanpete.wordpress.com/2010/05/04/biztalk-the-microsoft-distributed-transaction-coordinator-ms-dtc-has-cancelled-the-distributed-transaction/

    This error related to DTC timeout and you can tweak this setting inside machine.config (%Windows%\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config).



    Rachit Sikroria (Microsoft Azure MVP)

    Wednesday, February 17, 2016 1:56 PM
    Moderator
  • But why is it only Happening after redploying the application? I don't think that we are running in a time out. If I do the hack like described above already for the first run all worked fine.
    Wednesday, February 17, 2016 3:11 PM
  • But why is it only Happening after redploying the application? I don't think that we are running in a time out. If I do the hack like described above already for the first run all worked fine.
    I suggest testing MSDTC on your BizTalk server and database server using DTCPing or DTCTester, see Steef's post BizTalk and MSDTC.

    I still believe the issue is with DTC timeout. I have seen this particular behavior with Biztalk Server that it consistently demonstrates without fail. It takes a dreadful amount of time to service a “cold” request, however once “warmed”, it hums. Maybe that the reason you see this post redeployment.

    There is no harm try the solution provided in the link below. You can revert the changes if doesn't help.


    https://bekanpete.wordpress.com/2010/05/04/biztalk-the-microsoft-distributed-transaction-coordinator-ms-dtc-has-cancelled-the-distributed-transaction/


    Rachit Sikroria (Microsoft Azure MVP)

    Thursday, February 18, 2016 6:40 AM
    Moderator
  • So the issue seems to be after redploying the application or database, BizTalk seems to start a test run like

                                 exec [dbo].[sp_name] @Var1=NULL,@Var2=NULL,@Var3=NULL

    with all parameters set to NULL even if valid Parameter values are provided

    In the SQL profiler you can see after this run, a second call on the store procedure is sent with the valid parameters

    1. any idea why we have this "test call"?

    2. how can we avoid this "test call"

    Wednesday, February 24, 2016 3:44 PM
  • IMHO this is how BizTalk works, on first call BizTalk tries to cache the WCF proxy objects and the WCF adapter obtains metadata about the stored procedure before it calls it thus effectively doubling the number of calls to SQL Server, and running a relatively slow query to boot. Try increasing the DTC Timeout and this will work.

    Refer: Performance Tips for the WCF SQL Adapter for BizTalk Server



    Rachit Sikroria (Microsoft Azure MVP)

    Thursday, February 25, 2016 3:31 AM
    Moderator