Connecting to Oracle via SQL Adapter

Friday, March 15, 2013

In a specific scenario, we needed two receive location bounded to Oracle db.
One was simple, we needed a table rows to start a process. This was eazy using BizTalk Oracle Adapter. Installing Oracle client, defining TNS, and connection establish.

The other data we needed was a bit more complex. We needed the employee courses including subject learned in the course.

The Oracle adapter can not receive XML queries as result, so we preffered to define a linked server from SQL to Oracle, define the query SQL with the linked server like that:

Select *
OpenQuery(Ora,"Select empid,courseid,x1....xn from courses") course,
OpenQuery(Ora,"Select empid,courseid,y1...ym from subjects") subject
course.empid = subject.empid and
course.courseid = subject.courseid
for xml auto

the query worked fine in SQL Managment studio. But the receice location query failed in the adapter since the BizTalk tried to add the Oracle DB to the distributed transaction belongs to the SQL Server, over the linked server.

When searching this issue, I came accross a blog, Joe unfiltered: BizTalk, SQL linked servers and DTC trying to do the same.

Following the post solution, we created new linked server, adding the string "DistribTX=0" to Provider settings (and selecting Oracle OLE DB Provider - instead of MSDAORA), which solve the issue.

No comments:

Post a Comment

Post Your Comment...