RE: [BizTalker] Polling with the Oracle Adaptor

[Bill Chesnut]

Andrew,

>From the lack of other replies this seems to be an issue similar to what
happens with FTP receives in a multiple server environment, with FTP you
need to create a separate host to run the FTP receives and create a host
instance on each server in the group, but disable the host on all but one
server, then use something like Microsoft Operation manager to watch when
the host that is running the oracle host instance goes down, it then
disables the host on that server and enable and start the host on one of the
other server.

I know that this is not the answer you were looking for, but maybe this will
help, I can dig up some more info on the way they recommend setting up FTP
in a multiple server group.

Bill Chesnut
BizTalk Server MVP 

-----Original Message-----
From: biztalkerlist@xxxxxxxxxxxxxxxxxxxxxx
[mailto:biztalkerlist@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Andrew Hatch
Sent: Friday, 7 December 2007 1:13 PM
To: BizTalker@xxxxxxxxxxxxxxxxxxxxxx
Subject: [BizTalker] Polling with the Oracle Adaptor

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






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