Search code examples
biztalkbiztalk-2013

SP execution from BizTalk dynamic send port

I have a sp which I am executing from BizTalk orchestration.

When I am executing the SP in SQL server it's taking 10 sec, but when I am executing it from orchestration using below code, it's taking 1 hour.

  sqlStoredProcRequestXml = reportRequestXml;

  sqlStoredProcRequestXml(WCF.Action)= sqlProcedureName;
  sqlStoredProcRequestXml(WCF.BindingType)="sqlBinding";
  sqlStoredProcRequestXml(WCF.EnableTransaction) = false;

  sqlStoredProcRequestXml(WCF.BindingConfiguration)= @"<binding name=""sqlBinding"" sendTimeout=""02:30:00"" />";

SndRcvSqlStoreProcRequestResponsePort(Microsoft.XLANGs.BaseTypes.Address)="mssql://" + dbServer + "//" + dbDatabase; SndRcvSqlStoreProcRequestResponsePort(Microsoft.XLANGs.BaseTypes.TransportType)="WCF-SQL";

Can anyone explain why there is 1 hour diff in execution of the SP.

Solution

  • There is nothing specific to BizTalk delaying the sp call so much. You can try following to find the root cause:

    1. Use SQL profiler to see the actual execution time of SP via BizTalk
    2. Check for other delays in your environment, such as SQL performance, msgbox db, BizTalk host throttling
    3. Check event viewer log for any warning or error on send port host
    4. Make sure you use same data and environment to test the SP outside BizTalk to compare result, sometime depending on size of data, yr query can run for long time in diff env.
    5. You can also take DBAs help to sql profile your sp call