The new "WCF LOB Adapter SDK"-based SQL Adapter – Limitations


As mentioned in this post, we’re developing a new SQL Adapter, which will be available in the next release of the BizTalk Adapter Pack. The list of features which we’re supporting is mentioned in that blog post.

As for the features/items/etc which we’ve decided NOT TO SUPPORT, they are:



  • The ability to have multiple transactional blocks within the same XML message. All the operations within a message all take place within the same transaction; and this is actually a distributed transaction which spans these operations on your SQL Server, as well as the operations performed by BizTalk on the MessageBox database.

If the above point(s) are absolutely critical to your scenarios, please get in touch with us.

Comments (6)

  1. ThiagoAlmeida says:

    Hmmm, so will there be a way to perform multiple inserts in a parent-child situation within the same transaction? Say one parent to many children.

  2. CarloGM says:

    Sounds good, looking forward to these changes. This is critical to a number of scenarios we use, however we have been using SQLXML updategrams to achieve this (using a standard SQL [adapter] SendPort). Is this likely to be very different?

    Thanks

  3. CGM, are you saying that having updategrams is critical to your scenario? The post mentions that we will not be supporting it ..

  4. ThiagoAlmeida, if you’re using SQL 2008, then you can create a Stored Procedure having table parameters – e.g., one table containing the parent rows, and one table containing all the child rows. Your stored procedure logic can then insert into the appropriate tables.

    If you are using SQL 2005, then you have to take a slightly more laborious approach – you can create a stored procedure taking a single XML parameter, and you can invoke it with appropriately formatted XML values which can represent the parent-child hierarchy. Your stored procedure logic can then use the XML functions to extract various values.

  5. CarloGM says:

    Ah. Apologies, completely misread/misunderstood. It’s clear now.

    Thanks.

  6. ThiagoAlmeida says:

    Thanks for the reply. I guess the 2005 method you mentioned can already be done with the current SQL Adapter, but it’ll probably be easier with the WCF LOB one.