none
Unable to call a stored proc using BizTalk WCF custom with SQL binding RRS feed

  • Question

  • Hello All,

    I have 2 servers A and B with same db called operations having a SP called SP1.

    I have a BizTalk receive location which when connected to server A using WCF Custom with sql binding is working like a charm but when I use the same receive location to connect to server B it is not running that stored proc and is not even giving any errors anywhere not even in the eventlog.

    From my BizTalk server I am able to connect to the server B as I generated the schema for again from server B using the VS wizard.

    Every property is the same, even the user is the same and has access to both the servers. Only difference is the server. I am able to generate schema but not able to connect from BizTalk.

    Thursday, June 30, 2016 2:52 PM

Answers

  • Can you set useAmbientTransaction property on the adapter binding config to false and check if it makes a difference?

    Wondering if DTC is failing.


    Thanks Arindam



    Friday, July 1, 2016 2:13 PM
    Moderator

All replies

  • Hi Abhinav

    I would suggest to collect a SQL Profiler trace from BizTalk Server to make sure if the PollingStatements are firing. Are you getting the stored proc results if you run the stored proc directly from SSMS using the Receive Location credentials?

    Also when you start the Profiler trace, it's better to set a filter on your custom database name, or else you will see too many trace events. See this on how to filter on a specific database name.

    Refer this on SQL Server Profiler.


    Thanks Arindam




    Thursday, June 30, 2016 3:15 PM
    Moderator
  • Yes arindham, I am able to get the results when I run the stored proc from SSMS using the same credentials.
    Thursday, June 30, 2016 3:30 PM
  • I did the trace from BizTalk server and I can see that the db in question is getting a hit from the user to exec the sp.
    This is really strange as when I run the sp from SSMS it is working but not from BizTalk
    Thursday, June 30, 2016 4:29 PM
  • Hi Abhinav

    Does it show any trace when the ReceiveLocation is polling? How often is the Polling interval? Disable the ReceiveLocation, start the trace and then enable the ReceiveLocation. Wait and check if you see traces to the stored proc.

    Also, filter the profiler beforehand on the db name as mentioned earlier.


    Thanks Arindam

    Thursday, June 30, 2016 4:35 PM
    Moderator
  • Hello Arindm,

    I have set the filter.

    I disabled the receive location and then there were no calls for the execution of receive location and when I enabled the receive location I started getting the execute commands.

    The polling interval is 30 sec by default.

    Friday, July 1, 2016 7:27 AM
  • Hi 

    So it means that the SQL connection to the new DB is working fine as the queries are firing from the Receive Location to the new server.

    If you take the exact same query seen in the trace and run it in SSMS, you get the results?

    If yes, the ReceiveLocation should be getting the message as well - can you create a FILE SendPort with filter on the ReceivePort name just to be sure if data is coming in?


    Thanks Arindam

    Friday, July 1, 2016 7:35 AM
    Moderator
  • Yes the exact same query is running in ssms.

    I just created a file send port with the receive port name as the filter and still not getting any results there.

    Friday, July 1, 2016 8:05 AM
  • Hi Abhinav,

    Can you stop the subscribers and check if the receive location is publishing the message into the message box on the faulty server?

    Regards,


    Mandar Dharmadhikari

    Friday, July 1, 2016 8:25 AM
    Moderator
  • Are you sure that the URI pointed to by the ReceiveLocation and the SQL Server instance that you are logged into from SSMS are the same?

    Can you change the Receive pipeline to PassThruReceive on the WCF-SQL ReceiveLocation and check?

    Something is just not adding up. If the above does not help, WCF adapter tracing is the next step.

    You have to add the below config in the BizTalk config file, restart the Receive Host Instance, and let the ReceiveLocation run for a while. The log files can be opened using SvcTraceViewer tool-

    <system.diagnostics>
        <sources>
          <source name ="System.ServiceModel" switchValue="Verbose">
            <listeners>
              <add name="xml" />
            </listeners>
          </source>
          <source name ="System.ServiceModel.Channels" switchValue="Verbose">
            <listeners>
              <add name="xml" />
            </listeners>
          </source>
          <source name ="System.ServiceModel.MessageLogging" switchValue="Verbose, ActivityTracing">
            <listeners>
              <add name="xml" />
            </listeners>
          </source>
          <source name ="System.Runtime.Serialization" switchValue="Verbose">
            <listeners>
              <add name="xml" />
            </listeners>
          </source>
          <source name="Microsoft.ServiceModel.Channels" switchValue="Verbose">
            <listeners>
              <add name="xml_Adapter" />
            </listeners>
          </source>
          <source name="Microsoft.Adapters.Sql" switchValue="Information">
            <listeners>
              <add name="xml_Adapter" />
            </listeners>
          </source>      
          <source name="System.Transactions" switchValue="Warning">
            <listeners>
              <add name="SystemTxLog" />
            </listeners>
          </source>
        </sources>
        <sharedListeners>
          <add name="xml" type="System.Diagnostics.XmlWriterTraceListener" 
               traceOutputOptions="LogicalOperationStack" 
               initializeData="C:\log\WCFTrace.svclog" />
          <add name="xml_Adapter" type="System.Diagnostics.XmlWriterTraceListener" 
               traceOutputOptions="LogicalOperationStack" 
               initializeData="C:\log\AdapterTrace.svclog" />
          <add name="SystemTxLog" type="System.Diagnostics.XmlWriterTraceListener" 
               traceOutputOptions="LogicalOperationStack" 
               initializeData="C:\log\SystemTx.svclog" />
        </sharedListeners>
        <trace autoflush="true" />
      </system.diagnostics>
      <system.serviceModel>
        <diagnostics>
          <messageLogging 
               logEntireMessage="true" 
               logMalformedMessages="true"
               logMessagesAtServiceLevel="true" 
               logMessagesAtTransportLevel="true"
               maxMessagesToLog="100000"
               maxSizeOfMessageToLog="100000"/>
        </diagnostics>    
      </system.serviceModel>
    


    Thanks Arindam

    Friday, July 1, 2016 8:42 AM
    Moderator
  • Hello Mandar.

    I tried that but there are no messages in BizTalk messagebox

    Friday, July 1, 2016 8:57 AM
  • Yes something is fishy or super weird here.

    The URI is correct as when I change the server name to the old one it is fetching the files and when I change the sp name in inbounded property to xyz it is giving an error that unable to find the sp.

    I tried pass through but still nothing.

    The servers are on different domain so is that causing this? I doubt this as I can see the hit in SQL trace and getting errors in BizTalk when I change the sp name to a dummy one.

    Friday, July 1, 2016 8:59 AM
  • What is the query in the PolledDataAvailableStatement in the adapter?

    Does that query return a non-zero integer for the new SQL Server instance?


    Thanks Arindam

    Friday, July 1, 2016 9:02 AM
    Moderator
  • Yes, I am using that query as select 1 as ready
    Friday, July 1, 2016 9:08 AM
  • Okay, I guess the WCF adapter traces should show more - if there are faults happening on the channel etc. Please use the config shared earlier.

    Thanks Arindam

    Friday, July 1, 2016 10:20 AM
    Moderator
  • I am trying to get an approval to do the same as the machine is a client machine.

    But, I am confused what result we can get from these traces ?

    I mean BizTalk is able to hit the server so the connection should not be an issue.?

    Friday, July 1, 2016 11:57 AM
  • Yes, the connection is not an issue. Do you see both the PollingStatement and PolledDataAvailableStatement SQL queries in the SQL Profiler traces? Also check if there are any errors in the traces - normally they are in red.

    If all of the above is fine, the only thing that can help are the WCF traces - it will show what the adapter is doing after getting the query results. I am still baffled at your situation, though.


    Thanks Arindam

    Friday, July 1, 2016 12:17 PM
    Moderator
  • Yes both these statements are coming in the Trace and there is nothing in red.

    Lemme tell you what the SP is doing. It is fetching data from 1 table and then inserting it to another table with an extra column as pollingid which is getting updated with a unique guid and in the end it is returning all the columns of the 2nd table which had that guid. So, when I execute this SP from ssms it is inserting data in the 2nd table but when I execute it from BizTalk it is not inserting the data in the table. and the logic of the SP cant be wrong as the same SP is working in a diff server.

    Friday, July 1, 2016 12:35 PM
  • Can you set useAmbientTransaction property on the adapter binding config to false and check if it makes a difference?

    Wondering if DTC is failing.


    Thanks Arindam



    Friday, July 1, 2016 2:13 PM
    Moderator
  • Arindam you are a genius!!!! Thanks for not giving up on me and my issue. its working now!!!!! :)
    Friday, July 1, 2016 3:18 PM
  • Glad that I could help Abhinav !

    One final thing - since you are also doing an insert in the SP, it is best to fix the underlying DTC issue between BizTalk and SQL Server. Make sure the settings are as per this. Once DTC starts working fine (you can check using DTCPing), between BizTalk and SQL Server, you can set useAmbientTransaction to true on the adapter.


    Thanks Arindam


    Friday, July 1, 2016 3:44 PM
    Moderator
  • Hi Arindam,

    Can you please elaborate on the concept?

    I am curious to know how it worked. 

    Regards,


    Mandar Dharmadhikari

    Friday, July 1, 2016 4:26 PM
    Moderator
  • Hi Mandar

    So in this polling scenario, when useAmbientTransaction is set to True on the adapter, the adapter initiates a DTC transaction on BizTalk server and flows it to SQL Server - SQL Server joins this parent DTC transaction. In this case, this DTC transaction was not flowing/not committing probably due to DTC security settings. Turning useAmbientTransaction to false meant that the adapter did not try to propagate it's DTC transaction to SQL Server,


    Thanks Arindam

    Friday, July 1, 2016 4:57 PM
    Moderator