[BizTalker] Polling with the Oracle Adaptor

[Andrew Hatch]

Hi Everyone
 
Part of our BizTalk solution requires polling an Oracle 8i database to check for the existence of records in an interface table which then need to be sucked out.
 
As you would all be aware in a clustered BizTalk environment BizTalk manages all its internal resources correctly but external resources are another kettle of fish.
 
This article goes into detail into solving this problem with the SQL Adapter and carefully crafted SQL Stored Procedures with table locks;
http://adambowron.blogspot.com/2007/02/avoid-deadlocks-using-sql-adapter-and.html
 
However the issue we have with the Oracle Adapter using it against an Oracle 8i database is that we have to use select statements and don;t have the luxury of getting a cursor returned (I have heard this problem is not present in 10g) 
 
Our requirement of the Oracle adapter is to ensure all records are processed once and only once;

*	
	No Missing Records
*	
	No Duplicate Records

So currently these are our issues;

*	
	Records may be deleted by the Post Poll command before these records are retrieved.  ie; Missing records.
*	
	Records may be processed twice if two receive locations execute the same sql with a short timeframe.  ie; Duplicate records.

The Adapter we are using is the one supplied with the Microsoft Line of Business Adapters.
 
Has anyone come across this issue and implemented a resolution?
 
Many Thanks!
 
Andrew Hatch
 

IMPORTANT
1.	This email and any attachments are confidential.  Any unauthorised dissemination or other use of these materials is prohibited.  If received in error, please contact us and delete all copies.  
2.	Before opening or using attachments, check them for viruses and defects.  Our liability is limited to resupplying any affected attachments. 
3.	Protecting your privacy is important to us.  Our privacy statement and further information is available at www.oakton.com.au.


to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net