WCF LOB Adapter SDK and BizTalk Adapter Pack

Inserting parent child records with Identity column using WCF-SQL Adapter in one transaction

SandeepP — Tue, 13 Oct 2009 03:58:00 GMT

The following blog post by Atin nicely explains how to go about doing this

http://blogs.msdn.com/biztalkcpr/archive/2009/10/05/inserting-parent-child-records-with-identity-column-using-wcf-sql-adapter-in-one-transaction.aspx

Using the WCF SQL Adapter to submit messages to SSB queues from BizTalk

SandeepP — Wed, 07 Oct 2009 07:11:00 GMT

This post is a follow-up to http://blogs.msdn.com/adapters/archive/2008/06/30/using-the-wcf-sql-adapter-to-read-messages-from-ssb-queues-and-submit-them-to-biztalk.aspx and explains how to push a message using the WCF SQL Adapter from BizTalk to a SQL Service Broker (SSB) queue.

Scenario

1. An XML message is dropped to a file share

2. This XML message is made available to the WCF SQL Adapter by using the File Adapter

3. The WCF SQL Adapter then pushes this XML message to a preconfigured SSB queue by invoking a Stored Procedure

Create the database artifacts required for the SSB conversation

1. A message type, which denotes the format of the message in the queue

2. A contract, which denotes the conversation between a sender and a receiver and also includes the type of message flowing between them

3. The Initiator & Target queues, where messages are stored

4. The Initiator & Target services, which utilize the above queues

USE master;

ALTER DATABASE <your db name here>

SET ENABLE_BROKER;

USE <your db name here>;

CREATE MESSAGE TYPE

[//SqlAdapterSSBSample/RequestMessage]

VALIDATION = WELL_FORMED_XML;

CREATE CONTRACT [//SqlAdapterSSBSample/SampleContract]

([//SqlAdapterSSBSample/RequestMessage]

SENT BY INITIATOR

);

CREATE QUEUE InitiatorQueue1DB;

CREATE SERVICE

[//SqlAdapterSSBSample/InitiatorService]

ON QUEUE InitiatorQueue1DB;

CREATE QUEUE TargetQueue1DB;

CREATE SERVICE

[//SqlAdapterSSBSample/TargetService]

ON QUEUE TargetQueue1DB

([//SqlAdapterSSBSample/SampleContract]);

5. A stored procedure, say InitiatorSP, that will take the message as an argument and push it to the SSB queue. Let’s use the name RequestMsg for the argument.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[InitiatorSP]

@RequestMsg xml

BEGIN

DECLARE @DlgHandle UNIQUEIDENTIFIER;

BEGIN DIALOG @DlgHandle

FROM SERVICE

[//SqlAdapterSSBSample/InitiatorService]

TO SERVICE

N'//SqlAdapterSSBSample/TargetService'

ON CONTRACT

[//SqlAdapterSSBSample/SampleContract]

WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @DlgHandle

MESSAGE TYPE

[//SqlAdapterSSBSample/RequestMessage]

(@RequestMsg);

END

Create the BizTalk artifacts

1. Start the BizTalk Server 2009 Administration Console

2. Create a new BizTalk application, say SSBSendApplication

3. Create a new Receive Port, say FileReceivePort and add a new Receive Location, say FileReceive

a. Set the Type to File and configure the Receive Folder to point to a local share, say c:\in

4. Create a new Static One-way Send Port, say SqlSendPort

a. In the General tab,

i. Set the Type to WCF-SQL

ii. Click Configure and set the properties as follows

1. In the General tab, set

a. Address – the format is “mssql://<servername>/<instancename>/<databasename>”. For example, on my machine (using the default instance of SQL server), mssql://localhost//SSBTestDb (where SSBTestDb is the name of my database)

b. Action – the format is “TypedProcedure/<schemaname>/<storedprocedurename>”. For example, in my case, it is TypedProcedure/dbo/InitiatorSP

2. In the Messages tab, select Template and fill in the XML box with the following

<InitiatorSP xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">

<RequestMsg>

<bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="string"/>

</RequestMsg>

</InitiatorSP>

*Note that this approach requires that the xml encoding is string.

iii. Leave the other properties as is

b. In the Filters tab, add a filter BTS.ReceivePortName == FileReceivePort

5. Create a new Static One-way Send Port, say FileSendPort

a. In the General tab, set the Type to File and configure the Receive Folder to point to a local share, say c:\out

b. In the Filters tab, add a filter BTS.SPName == SqlSendPort

6. At this point the configuration of BizTalk application is completed, so start the application.

Send the message to SSB queue

1. Drop a request file to the c:\in share (one that file receive port is using). Note that this exact message will show up in the SSB queue. Here’s a sample message

<RequestMessage>Hello World</RequestMessage>

Consume the message from SSB queue

1. You can now consume the message from the SSB queue. On running the below query, you will see the above message.

DECLARE @DlgHandle UNIQUEIDENTIFIER;

DECLARE @RecvMsg XML;

RECEIVE TOP (1)

@DlgHandle=conversation_handle,

@RecvMsg = CAST(message_body as XML)

FROM TargetQueue1DB;

IF NOT (@DlgHandle IS NULL)

BEGIN

END CONVERSATION @DlgHandle;

SELECT @RecvMsg AS ReceivedMessage;

END

Introducing the Migration Tool for Microsoft BizTalk Adapters for Enterprise Applications

Nitin Mehrotra_MSFT — Tue, 16 Jun 2009 18:03:00 GMT

For customers currently using the Microsoft BizTalk Adapters for Enterprise Applications (aka the LOB adapters) and willing to migrate their existing projects to the WCF-based LOB adapters, here's a tool that will help you migrate your projects to work with BizTalk Adapter Pack 2.0.

Before we start talking about the tool and the adapter projects it can be used against, let us set the ground by laying the rules for the naming convention that we will be using here:

Microsoft BizTalk Adapters for Enterprise Applications adapters are referred to as non-WCF LOB adapters.
Adapters part of BizTalk Adapter Pack 2.0 are referred to as WCF-based LOB adapters.

What does the tool do?

The migration tool accepts BizTalk project/Solution files containing schemas generated by non-WCF LOB adapters and generates corresponding new BizTalk project/solution files having schemas corresponding to the WCF-based LOB adapters, with the maps and orchestrations modified accordingly. This tool helps you to migrate the projects for the following adapters:

BizTalk ODBC Adapter for Oracle Database
BizTalk Adapter v2.0 for mySAP Business Suite
SQL adapter

Where to get the tool from?

You can download the tool from http://go.microsoft.com/fwlink/?LinkID=153328. The tool also includes a migration guide that describes:

How to run the tool
What steps to follow after the tool has finished migrating the project
Limitations of the migration tool.

Why does the OracleEBS adapter say “Could not retrieve User ID, Responsibility ID, and Application ID”?

MSDNArchive — Wed, 03 Jun 2009 20:59:00 GMT

Sometimes, the Oracle EBS adapter throws this error while invoking artifacts like Concurrent Programs, Interface Tables, Interface Views and a few PL-SQL APIs.

The message in the event viewer looks like the following (snipped some portions to improve readability):

Event Type: Warning
Event Source: BizTalk Server 2009
...

Description:
The adapter failed to transmit message going to send port "<your_send_port_name>" with URL "oracleebs://<YourURI>". It will be retransmitted after the retry interval specified for this Send Port.

Details:"Microsoft.ServiceModel.Channels.Common.ConnectionException: Could not retrieve User ID, Responsibility ID, and Application ID. These values are required to set the application context. Make sure that you have specified correct values in the binding properties or the message context properties for setting the application context.

---> Microsoft.ServiceModel.Channels.Common.MetadataException:

---> Oracle.DataAccess.Client.OracleException ORA-01403:

....

at Microsoft.Adapters.OracleEBS.OracleEBSConnection.InitializeApps(...)
at Microsoft.Adapters.OracleEBS.OracleEBSConnection.OpenConnection(...)

The Oracle adapter throws this error when it is not able to set app context using the Responsibility-Name (or responsibility-key), application-short-name and apps-user provided by the user. You can read up on app-context in earlier blogs posts (http://blogs.msdn.com/adapters/archive/2008/10/14/oracle-ebs-adapter-application-context.aspx and http://blogs.msdn.com/adapters/archive/2008/10/17/precedence-while-setting-application-context-in-oracle-ebs-adapter.aspx).

One of the commonly asked questions is "How do I determine the correct responsibility name to be used?".

Here is a tool that you can use to determine the responsibility that you need to set app context. The tool will prompt will prompt you for relevant details like credentials, application short name etc.

Usage: CheckAppContext [/Responsibility:<Responsibility>]

- If the responsibility name is not provided, the tool lists the valid responsibilities that could be used for the apps user <AppsUserName> in the application <AppsShortName>.

- If the responsibility name is provided, the tool checks whether the provided combination of <AppsShortName> and <Responsibilty> can be used to set app context for the Apps User <AppsUserName>.

NOTE: The responsibility should be enclosed in quotes if it has spaces else quotes are not required .

Using Oracle Database or Oracle EBS adapter for Oracle AQ (Advanced Queuing)

manasg — Mon, 25 May 2009 14:31:00 GMT

Here is a simple way to get started on Oracle AQ (Advanced Queuing) using the Oracle Database and/or Oracle E-Business Suite adapters. Here is what I did, right from defining my queue - one step at a time.

Step 1 - Define the queue payload type
CREATE TYPE AQ_MESSAGE_TYPE AS OBJECT (ID VARCHAR2(30), INFO VARCHAR2(200));

Step 2 - Define the queue table
EXEC DBMS_AQADM.CREATE_QUEUE_TABLE(QUEUE_TABLE => 'INFO', QUEUE_PAYLOAD_TYPE => 'AQ_MESSAGE_TYPE');

Step 3 - Create the queue
EXEC DBMS_AQADM.CREATE_QUEUE(QUEUE_NAME => 'AQ_MESSAGE_QUEUE', QUEUE_TABLE => 'INFO');

Step 4 - Start the queue
EXEC DBMS_AQADM.START_QUEUE(QUEUE_NAME => 'AQ_MESSAGE_QUEUE');

Step 5 - Create a function to enqueue
CREATE OR REPLACE FUNCTION ENQUEUE_AQ_MESSAGE_QUEUE(PAYLOAD IN AQ_MESSAGE_TYPE) RETURN RAW
AS
PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
MSGID RAW(16);
OPTIONS DBMS_AQ.ENQUEUE_OPTIONS_T;
BEGIN
DBMS_AQ.ENQUEUE('AQ_MESSAGE_QUEUE',
    OPTIONS,
    PROPERTIES,
    PAYLOAD,
    MSGID);

RETURN MSGID;
END;

Step 6 - Create a function to dequeue
CREATE OR REPLACE FUNCTION DEQUEUE_AQ_MESSAGE_QUEUE(PAYLOAD OUT AQ_MESSAGE_TYPE) RETURN RAW
AS
PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
OPTIONS DBMS_AQ.DEQUEUE_OPTIONS_T;
MSGID RAW(16);
NOTHING_TO_DEQUEUE EXCEPTION;
PRAGMA
EXCEPTION_INIT (NOTHING_TO_DEQUEUE, -25228);
BEGIN
OPTIONS.WAIT := DBMS_AQ.NO_WAIT;
DBMS_AQ.DEQUEUE('AQ_MESSAGE_QUEUE',
    OPTIONS,
    PROPERTIES,
    PAYLOAD,
    MSGID);
RETURN MSGID;
EXCEPTION
WHEN NOTHING_TO_DEQUEUE
  THEN RETURN NULL;
END;

A word on dequeue and enqueue functions - these are simplistic in this example. You might want to modify them to take more inputs (for example - more message properties or enqueue/dequeue properties as input). The dequeue function returns immediately if a message is not available for dequeue and returns null in that case. You could tweak that too.

Step 7 - Call the functions using the adapter!
It is easy calling these from the adapter now. The reason why the DBMS_AQ.ENQUEUE and DBMS_AQ.DEQUEUE cannot be called directly from the adapter is that they use complex data types that are not supported by ODP.NET. One can now even call the dequeue function in a polling context, and get messages delivered to them as they are pushed into the queue! The links to portions of the Oracle Database adapter help that will help you do this are:

Developing BizTalk applications http://msdn.microsoft.com/en-us/library/dd788030.aspx
Support for Oracle User Defined Types http://msdn.microsoft.com/en-us/library/dd788045.aspx

Thanks to Mustansir for suggesting this blog idea!

WCF Siebel Adapter - Query fails because of misconfigured fields

SandeepP — Mon, 11 May 2009 14:24:00 GMT

Siebel adapter uses the COM interface to invoke calls on Siebel. For business component query, Siebel supports two ways of retrieving the field values for records. One can either use GetFieldValue to retrieve one field at a time or the other option is to use GetMultipleFieldValues to retrieve values for a bunch of fields in one go. The latter is better from a performance standpoint and is what the adapter uses. The problem with this approach though is that even if one of the fields, whose values are being retrieved, is mis-configured, the entire query call fails.

In development environments, it can happen that some of the customizations have issues, resulting in query failure as described above. The first step towards diagnosing the problem is identification of those problematic fields. The error message doesn’t always help identify the fields. When in such situation, one can use the attached Siebel COM sample code to narrow down the fields. You will need to add a reference to sstchca.dll (present in the Siebel install folder, in the bin sub-directory).

Once you have narrowed down the fields, you can either fix the issue with the field or if it’s not of interest, mark them as inactive on Siebel backend. You can then query using the Siebel adapter without any issues. Alternately, you can also use the QueryFields (optional) argument passed to the query operation to retrieve only the fields of interest.

BizTalk Adapter Pack 2.0 is now available

Vivek Krishna — Mon, 04 May 2009 08:40:00 GMT

The BizTalk Adapter Pack 2.0 was released to manufacturing and is now generally available .

For BizTalk 2009 users (except branch edition), the Adapter Pack 2.0 license comes free and can be downloaded from the volume licensing site.

Biztalk 2006 R2 users can get the Biztalk Adapter Pack if they have Software Assurance for Biztalk.

Others will have to buy it seperately.

The prequsite for this is the WCF LOB Adapter SDK SP2. A 120 day evaluation version of the Adapter Pack 2.0 is also now available . The below table summarizes all the links you need to get started with the Adapter Pack 2.0 .

Item	Link
Product
WCF LOB Adapter SDK SP2(pre-req for BAP 2.0)	http://go.microsoft.com/fwlink/?LinkId=147367
Adapter Pack 2.0 120 day Evaluation Version	http://www.microsoft.com/downloads/details.aspx?FamilyID=76736ba7-3c05-4436-9353-1c33f9005194&displaylang=en
Adapter Pack on volume licensing site (requires access)	http://www.microsoftvolumelicensing.com/userights/ProductPage.aspx?pid=281
MSDN Subscribers (requires access)	http://msdn.microsoft.com/subscriptions/downloads under BizTalk Server Accelerators and Adapters
SQL Adapter SKU Download(For BizTalk branch edition customers)	http://go.microsoft.com/fwlink/?LinkId=147379
Documentation and Samples
MSDN Location of Adapter Pack 2.0 docs	http://go.microsoft.com/fwlink/?LinkId=149364
Download location for individual CHMs in Adapter Pack 2.0	http://go.microsoft.com/fwlink/?LinkId=147355
Download location for Adapter Pack 2.0 Installation Guide	http://go.microsoft.com/fwlink/?LinkId=147364
Download location for SQL Adapter Installation Guide and CHM	http://go.microsoft.com/fwlink/?LinkId=147377
Download location for all the samples for Adapter Pack 2.0	http://go.microsoft.com/fwlink/?LinkID=145144

The Forum to ask any questions or look for answers is here . This version also includes the Customer Experience Improvement Program. Do turn it on so that we get some valuable feedback that can be used for future improvements.

Connection failure when using the WCF Siebel Adapter

SandeepP — Fri, 17 Apr 2009 07:07:00 GMT

I have seen quite a few instances where people run into issues while using the WCF Siebel adapter because of

a. Siebel Web Client is not installed correctly on the machine

b. Or the URI passed to the adapter, that eventually gets transformed to the connection string passed to Siebel, is incorrect

In order to eliminate the above 2 as possible causes, you can try out the following sample code that will use the Siebel COM interface to directly talk with Siebel (adapter is not involved). Please make sure you don’t see any error messages.

/// Application that will issue Login-Logoff request to Siebel

///

/// To run this program,

/// - Set ConnectString, Username and Password. ConnectString is of the format

/// siebel://host/EnterpriseServer/AppObjMgr/SiebelServer

/// (SiebelServer is required in case of Siebel 7.5)

/// For an example, please see the code below.

/// - Add a reference to sstchca.dll (found in siebel client install folder)

/// - Compile and run the program

/// - If the login fails, you will see an error message

using System;

using System.Runtime.InteropServices;

using SiebelBusObjectInterfaces;

namespace Sample

{

class Program

{

const string ConnectString = "host=\"siebel://adapsblsrvr77:2321/ent77/SSEObjMgr_enu";

const string Username = "abc";

const string Password = "def";

static void Main(string[] args)

{

SiebelDataControl sdc = null;

bool loggedIn = false;

try

{

/// Create a connection

sdc = new SiebelDataControl();

sdc.EnableExceptions(1);

sdc.Login(ConnectString, Username, Password);

loggedIn = true;

}

catch (COMException ex)

{

Console.WriteLine("ERROR: " + ex.ToString());

}

finally

{

if (sdc != null)

{

if (loggedIn)

{

sdc.Logoff();

}

Marshal.ReleaseComObject(sdc);

sdc = null;

}

Console.WriteLine("Press any key to terminate...");

Console.ReadLine();

}

Things to consider when writing WCF LOB Adapters for consumption through BizTalk

SandeepP — Thu, 12 Mar 2009 08:22:00 GMT

If you are writing a WCF LOB adapter that can be consumed through BizTalk, you need to be aware of certain issues that can manifest because of the way BizTalk interacts with WCF adapters. Some of these can have performance and scalability impact and hence you should consider them when designing/configuring the adapter.

Processing in IConnection/IConnectionFactory instance – When using SSO, for each message, BizTalk will end up creating a new IConnectionFactory and a new IConnection instance. If your adapter is doing a lot of processing in either of those instances, it can cause significant performance impact. So you will need to think of alternatives – possibly doing the processing upfront and caching.
Caching of LOB artifacts on IConnection instance – Typically you will have some LOB artifacts that will comprise the context associated with an IConnection instance. If your adapter is using WCF LOB Adapter SDK’s connection pooling, in the non SSO scenario, the IConnection instances will be closed only when the idle connection timeout expires. So you need to think about freeing up the LOB resources that comprise your connection context. If you make idle connection timeout too small, it would impact performance since LOB connection creation will incur an overhead and if you keep it at a large value then you will be holding on to LOB artifacts for a longer time than you really need to, possible scalability/performance impact.
Throughput stall – You can find more details about the issue on http://blogs.msdn.com/adapters/archive/2008/08/18/throughput-stalls-when-using-adapters-developed-against-the-wcf-lob-adapter-sdk-in-biztalk.aspx. Note that if you don’t limit the number of concurrent connections through your adapter, you won’t run into this problem. The problem happens the contention between Open and Send/Execute for thread pool threads.
Writing/promoting properties to BizTalk message context – When the adapter receives a WCF message from BizTalk, it will have all the properties comprising the BizTalk message context. For messages that originate from the adapter, if you want to have properties either written/promoted to BizTalk message context, you can look at http://technet.microsoft.com/en-us/library/bb246105.aspx on how to go about doing that.
Transaction overhead – BizTalk will by default start a transaction and send the message/process the response in that transaction scope. This can cause a significant overhead particularly if say the LOB doesn’t support transactions. In BizTalk 2009, there is an option on the Send port that lets you configure whether transactions should be enabled or not.

Connect to WCF LOB Adapter hosted as WCF service using ClientCredentials

SandeepP — Tue, 10 Mar 2009 13:00:00 GMT

On the website http://msdn.microsoft.com/en-us/library/dd451003.aspx, we have a step by step guide to publish the Siebel adapter as a WCF Service in IIS and then have MOSS consume the data using that service. In this approach, credentials are pass by MOSS to the adapter using custom HTTP headers.

If instead you want to consume this service from a .net application or any client that wants to pass the credentials using the ClientCredentials object, this is how you will go about doing it. Follow the steps on http://msdn.microsoft.com/en-us/library/dd450994.aspx that gives detailed instructions about publishing the WCF service. There are 2 differences though

1. In the “Configure service and endpoint behaviors” page, for “AuthenticationType” you will need to select “ClientCredentialUsernamePassword” (as opposed to “HTTPUsernamePassword” that the tutorial specifies)

2. In the “Configure the service endpoint binding and address” page, click on the BindingConfiguration (the … following BasicHttpBindingElement) to customize the binding. Set Security Mode to “TransportWithMessageCredential”

Follow the rest of the instructions for publishing the service. You can now connect to the service using a .net proxy application and pass the credentials using the ClientCredentials object.

Adapter Pack 2.0 Beta released

Vivek Krishna — Fri, 20 Feb 2009 07:40:00 GMT

Hi all,

We are pleased to announce the release of the Adapter Pack 2.0 Beta.As this is a public beta, customers can also download a 120 day evaluation version here .The features we added are listed below in brief . In addition, we are shipping a free to use migration tool which will help users migrate their projects which used older adapters to the Adapter Pack adapters (details below)

Oracle EBS

· 64 bit support

· Synonyms

· Added performance counters

· Notification support

OracleDB

· 64 bit support

· Synonyms

· UDTs

· Notifications

· Polling stored procedures

· Performance counters

Adapter SDK

· Display complex binding properties

· Display metadata wsdl in web control

SAP data provider

· Support for more operators in Sap Queries

· SAP SSRS support in VS2008

Samples

· New Samples for SQL and Oracle eBiz Adapters

The BizTalk Adapter Pack Migration Tool is a utility that helps developers migrate BizTalk projects using the BizTalk 2006 SQL or the BizTalk Adapter v2.0 for mySAP Business Suite to their corresponding WCF-based versions from BizTalk Adapter Pack v2!

With the help of this utility you will be able to compile and deploy your application within minutes. Some of the highlights for the Migration Tool are:

Works on the source project files
Generates equivalent schema definitions for the operations used in the existing project
Generates new maps to convert messages from older formats to the new format
Modifies any existing maps to work with the new schemas
A new project is created upon completion, which uses the SQL and/or SAP adapters from the BizTalk Adapter Pack v2

The tool works for most common scenarios of maps and orchestrations, however it is only meant to be a starting step towards migration to BAP and we are open to receiving feedback to add scenarios,.

Oracle EBS/DB Adapter Changes in Beta

Sai Shankar — Wed, 28 Jan 2009 08:48:00 GMT

As the Beta release for the Adapter pack approaches, here's a list of changes in the Oracle EBS/DB Adapters so that you can prepare for it:

· Now support Windows Vista and Windows Server 2008

· Based on Oracle ODP.Net 11.1.0.7

Both adapters now _require_ ODP.Net 11.1.0.7 installed on the client as compared to ODP.Net 11.1.0.6 which was the base for CTP 5 and earlier. Doing this enables us to deliver x64 support.

· Added Synonym support

Both adapters now support synonyms. Synonyms show up in the object tree wherever the underlying artifact (table/view etc.) of that type can occur. Synonyms are supported for the following types.

Tables
Views
Materialized views
Sequences
Procedures
Functions
Packages

Synonyms are not supported for:

Types
Synonyms

In addition, synonyms pointing to DB links are not supported.

· Added performance counters

We’ve added performance counters to both Adapters which are described in the online help.

· Added notification support, support for User Defined Types (UDTs) in the DB Adapter

Please note that this is not a complete list of changes. That list is available in the online documentation.

BizTalk Adapter Pack v2 CTP

Vivek Krishna — Mon, 15 Dec 2008 09:47:00 GMT

BizTalk Adapter Pack team has released a CTP. This is the 5^th CTP and the first one available publicly for everyone to try. The specific enhancements in this CTP are mostly to the OracleDB adapter(included in the BAPv1) to which we added features like support for polling stored procedures and composite operations. We also added 64 bit support for the Oracle EBS adapter.In addition we have addressed some feedback we got from the other adapters .

To re-iterate here is the complete list of features BAPv2 is adding . The downloads are available at the connect site for public download(requires sign up with live id)

BizTalk Adapter Pack V2 - Themes

New Adapters

· Oracle EBS Adapter

Ø Browse and search metadata for each Oracle application

Ø Support Interface tables, Interface views

Ø Support for PL/SQL APIs

Ø Concurrent Programs, Request Sets

Ø Support for polling tables, Stored Procedures

Ø Notification support

Ø App context initialization

Ø Supports REF cursors, User Defined Types, LOB types, RECORD types

Ø Support for PL/SQL Tables, boolean data types

Ø Support for MLS EBS installations

Ø Microsoft Office SharePoint Server Integration

Ø Versions supported – ODP.NET 11.1.0.6.21 (available only on 32bit), Oracle EBS 11.5.9, 11.5.10 and 12

· SQL Server Adapter

Ø Insert/Update/Delete/Select operations on tables and views

Ø Invoke (SQL and CLR) Stored procedures, scalar valued functions , table value functions

Ø Supports execution of generic T-SQL statements, via the ExecuteReader(), ExecuteNonQuery() and ExecuteScalar() operations

Ø Configurable polling – polling statement, polling interval, receive multiple result sets

Ø Support for x86 and x64 platforms

Ø Support for new data types in SQL2005 and SQL2008 (notably FILESTREAM, varbinary (max))

Ø Support for UDTs

Ø Ability to pass table value parameters

Ø Composite operations - invoke operations on multiple tables and call any number of stored procedures in the same transaction

Ø SQL query notifications

Ø Ability to directly call stored procedures which used the ‘for xml’ style supported by the BizTalk 2006 SQL adapter to ease backward compatibility

Ø Versions supported – SQL 2000, SQL 2005, SQL 2008

Enhancements to the existing V1 Adapter Pack

Include hot fixes built since the V1 Adapter Pack released
Support new Microsoft Platform releases – BTS 2009, VS 2008, Windows Server 2008
Oracle DB Adapter:

Ø Polling support for Stored procedures and Functions

Ø UDTs, PL/SQL tables

SAP Adapter:

Ø Support for Table Types

Ø Customization of adapter behavior when “special” values obtained for DATS/TIMS/NUMC

Ø SAP ADO Provider Enhancements:

o Support for SSRS

o Support for SAP Queries

o Ability to specify the result set to give to SSIS when executing BAPIs

Siebel Adapter:

Ø MVG operations (associate, dissociate, query) in design time enabled

Ø Bounded pick list fields supported per recommended approach

Ø Context specific custom messages generated for exceptions

Using sequences inside Composite Operations with Oracle EBS adapter

manasg — Thu, 04 Dec 2008 14:37:00 GMT

Consider a scenario where I want to insert data into two tables in a single transaction. The tables have a field that should be populated using the same sequence - one using SEQUENCE.NEXTVAL, and the other using SEQUENCE.CURRVAL, so that both tables get the same key value in these fields – a typical scenario when inserting into Interface Tables that have a dependency on each other.

The adapter performs all the operations in the Composite Operation in a single transaction and in order - however that does not mean that they are performed in a single session or connection to the Oracle server.

Sequences in Oracle are designed in a way such that their value does not reflect across sessions unless NEXTVAL is called on them. If you put 'SEQUENCE.NEXTVAL' in InlineValue for the first table and 'SEQUENCE.CURRVAL' in InlineValue for the second table in the Composite Operation, the result will not be what you’d normally expect. To elaborate, I defined a sequence named MYSEQUENCE, and opened two SQL-Plus sessions. I performed the following steps side by side as shown below:

Session 1	Session 2
SELECT MYSEQUENCE.CURRVAL FROM DUAL; ORA-08002: sequence MYSEQUENCE.CURRVAL is not yet defined in this session
	SELECT MYSEQUENCE.CURRVAL FROM DUAL; ORA-08002: sequence MYSEQUENCE.CURRVAL is not yet defined in this session
SELECT MYSEQUENCE.NEXTVAL FROM DUAL; >>21
SELECT MYSEQUENCE.CURRVAL FROM DUAL; >>21
	SELECT MYSEQUENCE.CURRVAL FROM DUAL; ORA-08002: sequence MYSEQUENCE.CURRVAL is not yet defined in this session
	SELECT MYSEQUENCE.NEXTVAL FROM DUAL; >>22
SELECT MYSEQUENCE.CURRVAL FROM DUAL; >>21
SELECT MYSEQUENCE.NEXTVAL FROM DUAL; >>23

As you see, the current value of the sequence does not get updated in all the sessions, and so if you need to use sequences in your inserts, straightforward logic does not work. We came up with two workarounds to make this work:

1. Fetch the value of the SEQUENCE.NEXTVAL beforehand (you can use the generic operation ExecuteScalar), and populate the final value in your message – works well if you’re using an orchestration.

2. Create two stored procedures on the Oracle backend – one for NEXTVAL, and another for CURRVAL that store the value of the sequence in a table (see attached sample script), so that the latest value is available across sessions.

In general, if a value that is session dependent needs to be used in various inserts in a composite operation, it should either be pre-fetched, or stored in a temporary table and accessed using functions called from InlineValue.

Adapter Pack v2 - CTP4 !!

Vivek Krishna — Thu, 30 Oct 2008 14:30:00 GMT

Adapter Pack CTP 4 is here and we have added a lot more features based on some feedback we have been getting from various channels. So here is the list of major enhancements

SQL adapter

Support for “for xml” style procedures used commonly with v1 SQL adapter

Oracle EBS Adapter - support for

Request Sets
Selecting Application Context using BizTalk Message Context Properties
Oracle EBS installations with Multi language systems
Windows integrated authentication of the DB user
Setting Print/Repeat/General options and submitting the request in a single operation for Request Sets and Concurrent Programs

As always, if you want to try this CTP you need to join the TAP(Technology Adoption Program). You can see the details for joining TAP here . Feedback is welcome and please write back to us on the forums or on the blogs to let us know what you think about the latest Adapter Pack releases.

Thanks

Vivek Krishna

Hotfix list for the Microsoft BizTalk Adapter Pack (and related scenarios)

Mustansir - MSFT — Thu, 30 Oct 2008 13:04:00 GMT

As and when we release newer hotfixes, I will edit this post.

Hotfix list for the Microsoft BizTalk Adapter Pack V1

Hotfix/KB Number	Component Affected	Description

950101	OracleDB	An error occurs if you try executing a Stored Procedure inside a Package, which belongs to a schema other than the default schema for the user.
950854	SAP	SAP Receive Locations in BizTalk stop working if an exception occurs in the Pipeline.
954539	SAP	By default, the adapter treats special DATS and TIMS values (like 00000000, 99999999, etc) as an error. With this hotfix, a new set of settings are exposed to the user, to allow him/her to configure the behavior when various special values are encountered. There is a blog post here for Adapter Pack V2, but this hotfix (for the Adapter Pack V1) has something very similar.
954233	SAP ADO	A “Reader positioned at invalid row” error is thrown, if you attempt to call GetFieldType() on the SAPDataReader object, if the reader is positioned before the first row or after the last row.
	SAP	The SAP adapter in the Adapter Pack V1 did not support “Table Types”. This hotfix adds support for it.
956168	SAP ADO	The option “no_conversion” does not work for NUMC datatypes. Also, incorrect values may be returned for some numeric fields.
959251	SAP	If an IDoc Segment has a special character in its name, the adapter throws an exception that the Segment information was not found in the metadata.

Hotfix list for Microsoft BizTalk Server 2006 R2

Hotfix/KB Number	Component Affected	Description

942612	WCF Adapter	If you use the Message Template feature in the WCF Adapter, the namespaces in the template are not passed to the Custom Binding (the adapters in the Adapter Pack will throw an error indicating that a node with a particular name and namespace was not expected).
960731	WCF Adapter	A Transaction object leaks (and ultimately times out) if an exception is thrown while reading a response message submitted by a transactional binding.

When to use InlineValue attribute in Insert in Oracle EBS adapter?

manasg — Mon, 20 Oct 2008 22:25:00 GMT

In typical Oracle EBS scenarios, it is a common requirement to insert computed values into a table. For example, one might want to populate the key column using a sequence, or insert something like SYSDATE into a date column. However, if the adapter makes it mandatory to provide constant values to be inserted, this becomes impossible to achieve in a single operation.

We came up with a simple solution. Every element in an InsertRecord takes an optional InlineValue attribute, which if populated, is used as-is in the insert statement. Let me illustrate with an example.

Consider the following insert operation XML snippet:

<ENAME>SCOTT</ENAME>

</InsertRecord>

This results in an insert statement that is equivalent to:

INSERT INTO SCOTT.EMP VALUES (:P0, :P1, :P2, …);

Where P0, P1, P2 etc are OracleParameter instances bound to the OracleCommand. Now consider the following XML snippet:

<ENAME>SCOTT</ENAME>

</InsertRecord>

This results in an insert statement that looks like this:

INSERT INTO SCOTT.EMP VALUES(SCOTT.EMP_SEQ.NEXTVAL, :P1, :P2, SYSDATE, SOME_API_TO_GET_SAL(), :P3, …);

Now, it is tempting to provide data values in the InlineValue attribute, but we recommend against that. <ENAME InlineValue="SCOTT"/> would result in an error as SCOTT is not a valid identifier. You’d have to use single quotes around the name as adapter merely puts this string in the insert statement without any sanity check on the value. Therefore, as a thumb rule, avoid using InlineValue attribute for constant values.

Update: If you want to use a select query in an inline value, you should enclose it in brackets. For example <ENAME InlineValue="(SELECT NAME FROM NEW_EMPLOYEES WHERE ID=123)"/> of course, you'll have to make sure that the select query returns only one field from a single row.

Precedence while setting Application Context in Oracle EBS Adapter

manasg — Fri, 17 Oct 2008 11:45:00 GMT

The messages sent to Oracle EBS Adapter can take three message context properties (starting CTP4):

1. Application Short Name
2. Responsibility Name
3. Organization Id

We introduced these properties so that one can invoke operations on a single port with different Application Contexts for each message being passed to the port, enabling the same port to be reused.

Here is the precedence order in which the adapter reads values for properies when it sets the application context before invoking an operation:

Also note that the precedence order for application short name we’re talking about is used only to set the application context – it does not change the application to which the interface table, interface view, concurrent program, or request set belongs to.

Why have these settings in Message Context as well as the Binding Properties? Well, we don’t want you to have to set message context properties on a message every time you have to set Application Context. You can put your typically used settings in the Binding Properties, and messages will be processed with Application Context as per those settings. When you want to process a message with a different Application Context, just set the Message Context Properties on it and you can send it to the same port – Message Context Properties override settings in the Binding!

Oracle EBS Adapter: Application Context

manasg — Tue, 14 Oct 2008 20:01:00 GMT

When the Oracle E-Business Suite (EBS) Adapter performs an operation on an Interface Table, Interface View, Concurrent Program, or a Request Set (Coming in the CTP4), it compulsorily sets the application context. To set application context, the adapter calls the FND_GLOBAL.APPS_INITIALIZE API. However, to call this API, the adapter requires three values:

1. User Id
2. Application Id
3. Responsibility Id corresponding to the User Id and Application Id.

The adapter gets the Responsibility Name from the binding, EBS User Name from the binding (or from Client Credentials starting CTP4), and retrieves corresponding id's from the backend to use when initializing application context. As I mentioned, it also needs the application id. For Interface Tables, Interface Views, Concurrent Programs, and Request Sets, the action contains the application short name. The adapter uses this application short name and ignores the value in the binding property ApplicationShortName to get the application id from the back end.

So when is the ApplicationShortName in the binding property used? It is used for setting application context for artifacts which do not have the application short name in their action. This includes PL/SQL APIs, Procedures, Functions, DB Tables, and DB Views. The adapter does not 'know' which application these artifacts belong to (if they belong to one). The adapter does not enforce setting application context for these artifacts, but you can make the adapter set the application context for them by proving the EBS User Name as well as the application short name.

When the adapter sees that the operation is to be performed on a PL/SQL API, Procedure, Function, DB Table, or DB View, if the ApplicationShortName in the binding and the EBS User Name are available, it goes ahead and tries to set the application context. Of course - it requires a valid Responsibility Name in order to be successful.

When (and only when) the application context is being set, the adapter looks at the binding property OracleEBSOrganizationId. If this has been populated, the adapter sets the Organization Id, else it does not.

With CTP4, which is due in some time, we support specifying ApplicationShortName, ResponsibilityName, and OrganizationId using Message Context Properties which provides greater flexibility in choosing responsibility and Org Id, and even override the application short name in the action. That will be the topic of my next post.

Till then, Happy Coding!

Multiple ways to Poll (Receive Location in BizTalk) in the SQL Adapter

Mustansir - MSFT — Mon, 06 Oct 2008 20:53:00 GMT

In this post, I outlined the ways in which stored procedures can be executed using the SQL Adapter. Similar to the methods described there, are very similar methods for polling on the inbound side. (I won’t mention the advantages/limitations/workarounds here, since there are very similar to those mentioned in the other post).

1. The first method for polling, manifested as the “Polling” operation in the Metadata Search/Browse tool. At runtime, the SQL statement specified in the “PollingStatement” binding property gets executed, and the result sets returned (we support multiple result sets being returned) are surfaced as System.Data.DataSet[] (an array of datasets).

NOTE – in order to let the adapter know that you want it to perform this operation at runtime, set the InboundOperationType binding property to Polling.

2. The next approach was to try and expose the result sets as “strongly typed”. For this purpose, we added the “TypedPolling” operation which you can see in the Metadata UI. Metadata is obtained (at design time + runtime) using ADO.NET’s CommandBehavior::SchemaOnly option (which translates to SET FMTONLY ON).

NOTE – in order to generate the metadata, setting the InboundId property in the Connection Uri is mandatory – since in a single BizTalk application, you can have multiple receive locations with different PollingStatement values, and in order to differentiate the XSDs, the InboundId value is used in the XSD namespace (thus, the InboundId should be different for different Receive Locations)

NOTE – in order to let the adapter know that you want it to perform this operation at runtime, set the InboundOperationType binding property to TypedPolling.

3. In CTP4 (releasing end of October 2008), we’ve added one more mechanism. This was mainly done for backward compatibility.

In this method, the PollingStatement you specify should return data in XML format (e.g., SELECT Col1 FROM Table1 FOR XML AUTO). At runtime, the XML data returned is enclosed within the root tag defined by the values in the binding properties XmlStoredProcedureRootNodeName and XmlStoredProcedureRootNodeNamespace.

NOTE – in order to let the adapter know that you want it to perform this operation at runtime, set the InboundOperationType binding property to XmlPolling.

SAP Adapter – No “BAPI” node in the Metadata Search/Browse hierarchy when creating an “Inbound” contract?

Mustansir - MSFT — Thu, 02 Oct 2008 04:50:08 GMT

A question which has popped up quite a few times over the last couple of weeks – “For an outbound contract, I see RFC, TRFC, IDOC and BAPI nodes in the UI. However, for an inbound contract, the BAPI node is missing. Why?”

Firstly, I’m going to explain what each of the four nodes you see really translate to within the adapter, and how they are “transported” to SAP.

The adapter communicates with SAP using the RFC protocol (it uses the RFC SDK for this purpose – which is written in C). RFC stands for “Remote Function Call” – a mechanism for calling Function Modules (functions) on SAP from external systems (you can also have functions defined on SAP which are not enabled for remote access). Using the RFC SDK, an external client can either invoke a RFC on SAP (outbound calls), or listen for incoming function calls (inbound).

The RFC and TRFC nodes you see in the metadata UI, are the only *pure* manifestations of this in the adapter.

Under the RFC node, we display all the function modules (which have been configured on SAP to allow them to be called from an external client). Invoking an operation under this node – the adapter just turns around and invokes that same operation on SAP.

The same functions also appear under the TRFC node. TRFC stands for “Transactional Remote Function Call”, though it is not really transactional. When you invoke an operation which appeared under the TRFC node (the Action is different for the same function under the RFC and TRFC nodes), the adapter when invoking the function on SAP, associates an identifier with the call. the SAP server makes a note of this identifier, and maps it to the execution status of that function. If, (suppose), the SAP server went down before completion of the function execution, the client can, at a later point, re-execute that call with the same identifier. SAP will realize that this call hasn’t been made before, and will attempt to re-execute the function. On the other hand, if SAP had successfully completed the call earlier, but if the client went down before it could process the response (or a network failure occurred before it could receive the response), the client can, at a later point, re-execute that call with the same identifier. SAP will know that an earlier call with the same identifier already completed successfully earlier, and will not execute it again, but will just send a response back to the client. The client, when it gets back a response (either on the first or later retries) should then clean up its own state with respect to the identifier, and also instruct SAP to do the same (via a RFC SDK API call, which the adapter exposes as an operation named “RfcConfirmTransID”). Once this cleanup/confirmation happens, this identifier is “forgotten”, and the next time this identifier is seen, it is assumed to be a brand new identifier / operation call. NOTE that in a TRFC call, SAP does not return any output values – hence the difference in the operation signatures when you compare them for the same RFC under the RFC node compared to the TRFC node.

What about the operations under the IDOC node? Sending IDOCs to SAP requires the client (the adapter) to execute special RFCs named IDOC_INBOUND_ASYNCHRONOUS / INBOUND_IDOC_PROCESS (depending on the version) on SAP. Receiving IDOCs from SAP requires the external application (now acting as the server) to be able to handle incoming calls to the same two functions. Operations under the IDOC node in the adapter are hence “dummy” operations which the adapter exposes – the adapter gets metadata for all available IDOCs on the SAP system, and exposes “Send” and “Receive” calls with different complex parameters based on the IDOC you want to work with. At runtime, for outbound calls, the adapter takes the individual pieces of data and converts them to the format which the above mentioned two RFCs require. For inbound calls, it splits/parses the data which it received from SAP as incoming parameter values to the above two RFCs, looks at the data to figure out which IDOC is being transmitted, gets metadata for that IDOC, and then re-formats the data to fit that metadata.

Note – for inbound IDOC calls, the adapter exposes a property named ReceiveIdocFormat (which is an enumeration of type IdocReceiveFormat). One of the values in this enumeration is “Rfc” – if you choose this, then, for incoming IDOCs, the adapter won’t peek at the data to figure out which IDOC it is and re-format it to fir the “Receive” operation – instead, it will just expose it as a RFC call to the servicehost – as a call to IDOC_INBOUND_ASYNCHRONOUS / INBOUND_IDOC_PROCESS. Well, actually, it will expose it as a TRFC call (i.e. the WCF message will have the TRFC action) since when SAP sends IDOCs to an external application, it usually does that using transactional semantics – at least SAP’s mechanism of transactions. (Note - the default value of the property/enumeration is “Typed”).

Lastly, BAPIs. SAP allows you to create Business Objects, and with each object, associate methods, events, attributes, etc – its version of OOP. In the current SAP architecture, methods on business objects are just RFCs. Hence, at design time, the adapter looks up the Business Object Repository (BOR), and determines the objects present on SAP. For each object, it figures out what methods were defined for it, and what the actual implementation is – that is, what is the actual RFC to which it maps. The adapter then shows the friendly names of the operations in the UI (since it has that information when it looked up the BOR), but in the Action , it actually uses the RFC name – since at runtime, what it really needs to do is just execute the corresponding RFC.

And now, coming to the question which this post was meant to answer – why is there no BAPI node in the UI for an inbound contract? When the adapter receives an incoming call from SAP, all it has is the function name – since after all, as you can see above, everything just involves execution of an RFC – for both outbound and inbound calls. If SAP sent along a “transactional” identifier with the function invocation, the adapter formats the incoming message (which it gives to the WCF service / BizTalk Receive Location) as a TRFC call. If the function which SAP invoked was named IDOC_INBOUND_ASYNCHRONOUS / INBOUND_IDOC_PROCESS, the adapter recognizes these functions as “special”, and peeks at the data to determine the IDOC being transmitted, and formats the WCF message as a call to “Receive” with the appropriate action (which contains the IDOC type, among other things). Else, the incoming WCF message is now just formatted as a call to an RFC (with the Action containing the RFC name).

The adapter can’t format the WCF message using an Action corresponding to a specific Business Object (i.e., corresponding to an operation that would live under the BAPI node), since strictly speaking, there is no mapping from the RFC name to the Business Object type which implements it. The BAPI node in the UI for outbound contracts was really more of a convenience mechanism – so that a user can navigate the business object hierarchy to find the function of interest. All functions under the BAPI node also appear under the RFC and TRFC nodes too. For inbound calls, if you’re interested in listening for an incoming function like BAPI_SALESORDER_CREATE (which is most probably a function with the friendly name “Create” defined on the “SalesOrder” business object), just search for the RFC named BAPI_SALESORDER_CREATE under the RFC node (or under the TRFC node if you know that SAP is going to execute this “transactionally”). NOTE that in the most common case, only the “special” IDOC RFCs are invoked transactionally by SAP on an external application, so for all other incoming RFC calls, you should just add the operation under the RFC node to your Service Contract.

Multiple ways to generate metadata / execute Stored Procedures in the SQL adapter

Mustansir - MSFT — Tue, 30 Sep 2008 14:05:00 GMT

If you’ve used the SQL Adapter in CTP3 of the BizTalk Adapter Pack V2, you’ll notice that there are two nodes in the Metadata Hierarchy for Stored Procedures – one is named “Procedures”, and the other is named “Strongly-Typed Procedures”. What’s the difference, and what are the scenarios which they are meant to solve? What are their limitations? I’ll try to explain all this, in this post.

1. The first method of executing Stored Procedures which we added in the adapter, manifested as the operations under the “Procedures” node in the Metadata hierarchy. The operations here have the action “Procedure/<database_schema_name>/<procedure_name>”. For these operations, the adapter reads the System Tables in SQL Server, finds out the parameters to the procedure, and exposes them as IN or INOUT parameters in the WSDL. However, we yet need a way to return the result sets which the Stored Procedure can return at execution time. The adapter exposes these result sets as a return parameter, of type System.Data.DataSet[]. Being an array, multiple result sets can be returned.

At runtime, the adapter executes the Stored Procedure using the ADO.NET function SqlCommand::ExecuteReader(). Each result set returned by executing the Stored Procedure is serialized into its own DataSet; hence all of them together appear as DataSet[]. The response XML message contains both, the schema for each result set / DataSet, as well as the actual data in that result set / DataSet. When used in a .NET application, the schema + data together are used when deserializing the SOAP message into a DataSet object.

Advantages: Works for all stored procedures.

Limitations: The result sets are loosely typed (being a DataSet[]), and in BizTalk, are not helpful if you want to use the Mapper.

Workarounds: What you could do is, execute the procedure once, dump the XML message to a file location, and open it in notepad. Select the <schema> node within the result set section in the return parameter, copy-paste it into a new file, and save it with the .xsd extension. You now have a schema which you can deploy in your BizTalk orchestration/project. Also, use the Message Template feature in the WCF-Custom/WCF-SQL port configuration, using a XPath query to only select out the data (at runtime) for that particular result set (matching the XSD which you deployed); ignoring the other result sets (if any) and out parameters. You now have a XML blob being submitted to BizTalk, which conforms to the XSD which you deployed.

2. The next approach we took, was to try and expose the result sets as “strongly typed”, instead of the loosely typed DataSet[] above. These operations manifest as the operations under the “Strongly-Typed Procedures” node in the metadata hierarchy. The action is of the form “TypedProcedure/<database_schema_name>/<procedure_name>”. For these operations, the adapter reads the System Tables in SQL Server, finds out the parameters to the procedure, and exposes them as IN or INOUT parameters in the WSDL. In order to expose the returned result sets in a “strongly typed” fashion, the adapter needs to know what the metadata for the returned result sets will look like. For this purpose, at design time, the adapter executes the Stored Procedure using the ADO.NET function SqlCommand::ExecuteReader(CommandBehavior::SchemaOnly). This translates to the SET FMTONLY ON option being used. However, in order to execute the Stored Procedure, the adapter needs to pass in values to the parameters. For this purpose, the adapter uses DBNull.Value for each parameter. Upon execution, the adapter gets back multiple (empty) result sets, and from these, the adapter can obtain the metadata for the result sets which can potentially be returned at runtime.

Note – I use the word potentially. This is because, at runtime (FYI – the adapter uses the ADO.NET function SqlCommand::ExecuteReader() at runtime), a Stored Procedure can return different result sets based on the input values. For example, if an input parameter has the value 1, the Procedure could return a result set by performing the operation “SELECT * FROM TABLE1”. If the input parameter has the value 2, it might actually execute “SELECT * FROM TABLE2”. At runtime, only one of the two result sets will be returned, depending on whether the input parameter is 1 or 2. However, at design time, when the SET FMTONLY ON statement is used, both the (empty) result sets are returned. The adapter exposes them both as complex out parameters (and names them in the metadata as TypedProcedureResultSet1, TypedProcedureResultSet2, etc). At runtime, one will be null, while the other will have the appropriate data filled in.

Note – The current design of the WCF LOB Adapter SDK (on which the SQL Adapter is based) is that metadata is also required at runtime. Hence, at runtime too, the adapter executes the Stored Procedure using SET FMTONLY ON (just once) to obtain the metadata. Then, for every message being passed to the adapter, the adapter executes the Stored Procedure, and based on the result sets returned, tries to determine whether it should be serialized as TypedProcedureResultSet1, or TypedProcedureResultSet2, etc (for example) (based on the metadata it obtained earlier by using SET FMTONLY ON).

Advantages: Strong typing of the result sets.

Limitations:

These operations cannot be used to execute “complicated” Stored Procedures – for example, a hypothetical procedure which returns multiple result sets (from potentially different tables) within a loop. This is because the adapter won’t be able to figure out which result set (at runtime) needs to be serialized as which complex type (TypedProcedureResultSet1, or TypedProcedureResultSet2, or something else).
These operations won’t work for Stored Procedures, when in the procedure code, a temporary table is created, and then, one of the returned result sets is obtained by doing a SELECT on that temporary table. The reason being, when the SET FMTONLY ON option is used, no temporary tables are created. However, when the SQL execution engine comes across the line SELECT * FROM #temptable, it throws an error, (something to the effect of it not finding an artifact named #temptable), since it never created this artifact in the first place (since SET FMTONLY ON is not supposed to make any changes on the server).
I mentioned above that during metadata retrieval time, the adapter needs to pass in values for all parameters to the Stored Procedure, and for this purpose it passes in DBNull. Now, if the Procedure internally calls a System Stored Procedure, and passes in one of the input parameters (which in our case is NULL), and if the System Stored Procedure returns an error if it sees the value as NULL, the adapter will get an exception at metadata retrieval time. NOTE – this won’t happen in your custom procedure if your code itself throws an error if it sees an input parameter having the value NULL, since for user procedures, when SET FMTONLY ON is true, the execution engine skips the evaluation of the if statements (this evaluation only happens in system stored procedures).

Workarounds:

Use one of the other Stored Procedure execution methods.
For working around the issue where temporary tables are used, you can modify the Stored Procedure code - http://www.sqlservercentral.com/articles/FMTONLY/64130/ has an example of what you can do.

3. In CTP4 (releasing end of October 2008), we’ve added one more mechanism to execute Stored Procedures. This was mainly done for backward compatibility.

The earlier SQL adapter required result sets returned from Stored Procedures to use the FOR XML syntax. This was because the old adapter used SQLXML; using FOR XML would instruct the SQL Server to return the result set as a single XML value, and the SQLXML code on the client would then parse the XML returned.

In methods 1 and 2 above, you’ve seen that the new adapter used the ADO.NET SqlCommand::ExecuteReader() function. If this function was used to execute a Stored Procedure which returned a result set using FOR XML, all the adapter would see is a single XML value (exposed as a string) – i.e., the adapter thinks that the returned result set only has one column. Even worse, if the XML value was large, it would get split into multiple rows. This is of course extremely cumbersome to work with.

For this purpose, a third mechanism was added, what I call “XML Procedures”. The action for such operations is “XmlProcedure/<database_schema_name>/<procedure_name>”.

There is no design time experience for such procedures. Generating metadata involves the following:

Generate metadata for the same procedure under the “Procedures” node – this generates the metadata for the request message as well as the response message. Here, we are only interested in the request message schema, since that’s the format in which the request message needs to be sent (though you need to use the “XmlProcedure” action).
In SQL Server Management Studio, edit your Stored Procedure, and add the XMLDATA keyword at the end of the FOR XML statement (similar to what you would have done if using the older SQL Adapter).
Execute your Stored Procedure from SQL Server Management Studio. Before the actual data, you should see a <schema> node which has the metadata for the result set. Copy-paste the schema into a .xsd file. Also, add a root node (with namespace) to encapsulate the nodes in the data/schema (you’ll see why later).
This .xsd file will serve as the schema for the response message in BizTalk.
Remember to remove the XMLDATA keyword from the Stored Procedure code after you're done with the above steps.

At runtime, you need to do this:

Specify values for the XmlStoredProcedureRootNodeName (mandatory) and XmlStoredProcedureRootNodeNamespace (optional) binding properties. The XML obtained by executing the “FOR XML” Stored Procedure will be wrapped within this root node, with this namespace.
Use the “XmlProcedure/<database_schema_name>/<procedure_name>” action during execution (instead of “Procedure/<database_schema_name>/<procedure_name>”).

When the adapter sees the XmlProcedure action, it uses the ADO.NET SqlCommand::ExecuteXmlReader() function.

Advantages: You can continue using FOR XML Stored Procedures side-by-side with the old SQL Adapter. Can work with all stored procedures which return XML.

Limitations: There is no way to generate the metadata using the adapter, since the WCF LOB Adapter SDK does not have a way to accept input parameter values during design time. The adapter cannot use NULL for the parameter values (unlike how it did in 2 above), since obtaining metadata involves actual execution of the Stored Procedure, and therefore all the Stored Procedure logic comes into play, which might throw an error if an unexpected value is seen for an input parameter.

Workarounds: Use one of the other Stored Procedure execution methods.

Support for SAP Queries in the SAP ADO.NET Provider

Mustansir - MSFT — Thu, 25 Sep 2008 11:22:40 GMT

In the BizTalk Adapter Pack V2, we are adding support for execution of SAP Queries.

A brief one liner on SAP Queries – you can use the SAP GUI designer to create something similar to a “SQL Query” (similar to how you would do it in Microsoft Access) by graphically selecting the tables you’re interested in, the columns, the input parameters, the sort order of the result set, etc.

You can execute the query from the SAP ADO Provider, using syntax like:

EXECQUERY QueryName @P1=Parameter1Value, @P2=Parameter2Value, @USERGROUP=UserGroup [, @WORKSPACE=’X’]

@Pn refers to the nth selection field specified during the creation of the Query.

@USERGROUP (mandatory) refers to the User Group associated with the Query. If the Query was defined in the global workspace, you would additionally specify @WORKSPACE=’X’ in the command.

You can refer to the documentation on additional information on the syntax.

Having the ADO Provider support SAP Queries means that you can also execute them from SSIS Tasks, as well as create SSRS Reports based on them.

In order to use SAP Queries, you’ll have to obtain the next CTP of the BAP 2.0 (scheduled end of October 2008). Note that this CTP is only available to TAP customers.

SAP ADO – Design Time helper tool

Mustansir - MSFT — Wed, 20 Aug 2008 16:44:00 GMT

Attached to this post is a standalone .NET Windows Form application which can be used to browse artifacts on SAP, and build queries which the SAP ADO.NET provider can understand. The main aim is to reduce the grunt work required when building the queries.

The provider supports EXECuting RFCs, SELECTing from a single table, and SELECTing from two tables (JOIN). The tool I’ve attached currently supports EXECuting RFCs and SELECTing from a single table; support for generating queries which use a JOIN will be added in a later version.

The current version of the tool is linked against the 32 bit version of CTP3 of the BizTalk Adapter Pack V2. I’ll update the attachment as and when newer versions of the Adapter Pack are released.

CTP3 of Adapter Pack v2

Vivek Krishna — Wed, 20 Aug 2008 07:41:00 GMT

CTP3 is here and we have added a lot more features based on some feedback we have been getting from various channels. So here is the list of major enhancements

SQL adapter:

1) Strongly typed polling
2) Strongly typed stored procedures

Oracle Adapter

1)      Inline Value support
2)      App context initialization
3)      Oracle Notifications
4)      ReadLOB/Update LOB operations
5)       PLSQL tables inside RecordTypes
6)      Performance improvement for metadata generation

Others

1) We now support upgrade from APv1 RTM to APv2 CTP3
2) More meaningful schema names for SQL adapter - again a common feedback from customers, we will be doing this for other adapters in next releases

3) Using the SAP ADO provider from SSRS

4) Making WCF LOB adapters more discoverable - one of the common requests was that users have to select WCF-Custom and then sqlbinding or sapbinding from yet another menu in the BTS admin console while configuring ports causing problems with usability and troubleshooting.This has now been fixed and you can choose WCF-SQL or WCF-SAP from the list of send ports.

So go ahead and try out the new CTP and do send in feedback. As always, if you want to try this CTP you need to join the TAP(Technology Adoption Program). You can see the details for joining TAP here .

Thanks

Vivek Krishna