BizTalk 2009 SQL WCF Adapter Losing Messages? AKA TypedPolling Not Working

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › BizTalk 2009 SQL WCF Adapter Losing Messages? AKA TypedPolling Not Working

Viewing 0 reply threads
  • Author
    Posts
    • #24918

      Can anybody help me with this amazingly frustrating issue? Firstly, the basiscs. I’m using BizTalk 2009 Standard Edition hosted on Windows Server 2008  Standard (64 bit). The BizTalk databases are stored on a seperate server, running SQL Server 2008 on Windows Server 2008 Enterprise (64 bit).

      I have configured a WCF SQL adapter to poll (using TypedPolling) a SQL 2008 database which in turn activates a relatively simple orchestration. The problem is that the orchestration does not get executed at the specified polling intervals. I have several other similar polling adapters in my application that work
      as expected. Generally it will run correctly once or twice after enabling/disabling the receive location or restarting the host instance. But after that it is completely erratic. If I have it set to run every hour (3600 secs), it might complete succssfully once or twice a day at seemingly random times. On other days it might run seven or eight times during a day.

      I’ve spent heaps of time reading blogs and changing various settings on my receive location and have really run out of ideas. I’ve profiled SQL Server and it seems that the WCF SQL adapter does hit the database at each polling period as specified, using both the polledDataAvailableStatement and the
      pollingStatement. Data is always returned by these queries (a small amount – around 30 records), but they just don’t seem to get to the message box. No errors are ever reported in the Event Log.

      I use a stored procedure for determining if data is available and for returning data. They are both effectively the same statement, one returns a count whilst the other returns the data. The queries are using cross server joins as you can see below. Although I do no updates, it seems that I have to run the receive location with AmbientTransactions turned on. Turning them off just results in a SQL Timeout exception. I don’t understand why this is and would be grateful if someone could explain.

      Data Available (contained in a stored proc):

      SELECT  COUNT(*)
          FROM    [LMNZLSQL002\AX].[LMNZ_AX_LIVE].[dbo].[CRM_CUSTTRANS_ITG] WITH (NOLOCK)
          WHERE    (TRANSTYPE = 8 OR TRANSTYPE = 13)   
          AND SETTLEAMOUNTCUR > 0
          AND CUSTFEEINVOICETRANSACTION30005 IS NOT NULL
          AND CUSTFEEINVOICETRANSACTION30005 <> ”       
          AND CUSTFEEINVOICETRANSACTION30005 IN
          (
              SELECT F.FeeId
              FROM [dbo].[Fees] F WITH (NOLOCK)       
              WHERE F.FeeStatus = 3        –Unpaid
              AND F.Payer IS NOT NULL
          )   

      Select Data (contained in a stored proc):

      SELECT  ACCOUNTNUM, CUSTFEEINVOICETRANSACTION30005, AMOUNTCUR, SETTLEAMOUNTCUR
          FROM    [LMNZLSQL002\AX].[LMNZ_AX_LIVE].[dbo].[CRM_CUSTTRANS_ITG] WITH (NOLOCK)
          WHERE    (TRANSTYPE = 8 OR TRANSTYPE = 13)   
          AND SETTLEAMOUNTCUR > 0
          AND CUSTFEEINVOICETRANSACTION30005 IS NOT NULL
          AND CUSTFEEINVOICETRANSACTION30005 <> ”       
          AND CUSTFEEINVOICETRANSACTION30005 IN
          (
              SELECT F.FeeId
              FROM [dbo].[Fees] F WITH (NOLOCK)
              WHERE F.FeeStatus = 3        –Unpaid
              AND F.Payer IS NOT NULL
          )   
          ORDER BY ACCOUNTNUM,CUSTFEEINVOICETRANSACTION30005

      At one time I suspected that the issue may be related to database locking. So, as you can see, I’ve added NOLOCK locking hints to rule this out. I’m also using a Service Behaviour (SqlAdapterInboundTransactionBehaviour) declaring a transaction level of ReadUncommitted. Obviousy I’d like to move away from the possibility of dirty reads. Also, I’ve checked the SQL activity monitors when I expect the process to run and can see no locks holding it up. Also, I can freely execute the stored procedures from SQL Management Studio at these times.

      I’m not too experienced at monitoring BizTalk, but I wondered if some kind of throttling was occuring. So I’ve moved the receive location (and the orchestration that doesn’t always get initiated) to its own host instance and have done some monitoring via the performance counters in perfmon. I haven’t spotted any thottling going on, but maybe I wasn’t looking at the correct counters (mainly used the Message Agent items).

      Here’s a summary of the receive location properties:

      Transport: WCF-Custom
      Receive Handler: AXPaymentsToFeesEngine (this host instance only hosts this receive location and the orchestration it should be activating).
      Receive pipeline: XmlReceive

      General tab:
      EndpointAddress: mssql://lmnzlsql003/CRM/FeesEngine?InboundId=PaidInAXUnpaidInFeesEngine
      Endpoint Identity – all default values

      Binding Tab:
      Binding Type: sqlBinding
      allowIdentityInsert: False
      batchSize: 20
      chunkSize: 4194304   
      enableBizTalkCompatibilityMode: True
      enablePerformanceCounters: False
      encrypt: False
      inboundOperationType: TypedPolling
      maxConnectionPoolSize: 100
      notificationStatement: Not specified
      notifyListenersOnStart: True
      polledDataAvailableStatement: EXEC [dbo].[bts_PollForAXFeePaymentsNotPresentInFeesEngine_IsDataAvailable]
      polledIntervalInSeconds: 3600
      pollingStatement: EXEC [dbo].[bts_GetFeesMarkedUnpaidThatHaveBeenPaidInAX]
      pollWhileDataFound: False
      UseAmbientTransaction: True
      useDatabaseNameInXsdNamespace: False
      workstationId: Not specified
      xmlStoredProcedureRootNodeName: Not specified
      xmlStoredProcedureRootNodeNamespace: Not specified
      All timeouts: 5 mins.

      Behaviour Tab
      ServiceBehaviour – sqlAdapterInboundTransactionBehaviour, ReadUncommitted, timeout mins

      Other Tab:
      Credentials: None
      Preserve message order: False

      Mesages Tab:
      Inbound BizTalk message body: Body
      Error handling: All disabled

      Thanks in advance,

      Mark

      (CORRECTED)

Viewing 0 reply threads
  • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.