Home Page › Forums › BizTalk 2004 – BizTalk 2010 › BizTalk 2009 SQL WCF Adapter Losing Messages? AKA TypedPolling Not Working
- This topic has 0 replies, 1 voice, and was last updated 9 years, 10 months ago by
community-content.
-
AuthorPosts
-
-
June 8, 2010 at 7:51 PM #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,CUSTFEEINVOICETRANSACTION30005At 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: XmlReceiveGeneral tab:
EndpointAddress: mssql://lmnzlsql003/CRM/FeesEngine?InboundId=PaidInAXUnpaidInFeesEngine
Endpoint Identity – all default valuesBinding 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 minsOther Tab:
Credentials: None
Preserve message order: FalseMesages Tab:
Inbound BizTalk message body: Body
Error handling: All disabledThanks in advance,
Mark
(CORRECTED)
-
-
AuthorPosts
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.