Multiple Stored Procedure Calls using the BizTalk SQL Adapter without a loop shape

Posted: September 9, 2008  |  Categories: BizTalk Uncategorized

Alright, now that the New Zealand Tech Ed is done and dusted people around here are back at blogging, including me.

I thought I’d start with one that’s been on my list for a while: how to make multiple calls to the same stored procedure using the SQL Adapter without having to use a loop shape.

No matter how much you use BizTalk – you’re always learning a new trick. This one comes courtesy of some fellow BizTalk developers here at work – Mark Brimble, Capree Lim and Colin Dijkgraaf.

Say I have this table inside my SQL Server MyTestDB database: 

CREATE TABLE [dbo].[tbMyTestTable]  

([id] [int] IDENTITY(1,1) NOT NULL,

[Code] [nchar] (5) NOT NULL,
[Name] [nchar] (30) NOT NULL,
 [Type] [nchar] (10) NOT NULL,
[PartnerID] [int] NOT NULL,  


 And I have this stored procedure on the same database:

PROCEDURE [dbo].[myTestSProc]

    @Code nchar(5) ,@Name nchar(30), @Type nchar(10), @PartnerID int



    IF EXISTS(Select Code from tbMyTestTable tb where Code= @Code)

        UPDATE [MyTestDB].[dbo].[tbMyTestTable]

         SET [Code] = @Code, [Name] = @Name, [Type] = @Type, [PartnerID] = @PartnerID

         WHERE [Code] = @Code


        INSERT INTO [MyTestDB].[dbo].[tbMyTestTable]

            ([Code], [Name], [Type], [PartnerID])

        VALUES (@Code, @Name, @Type, @PartnerID)


 As you can see, the stored procedure will insert a new row in case the code doesn’t already exist, otherwise it will update the row with that code. From this I used the trusty “Add Generated Items” -> “Add Adapter Metadata” to create the schema for that stored procedure. It looks like this:

Now, here is the trick: on the properties of the myTestSProc node (the node with the name of your stored procedure) I changed the Max Occurs setting to unbounded. This will make the schema allow us to repeat that row of XML as many times as we want.

I then created a flat file schema with those same elements:

And, of course, a straight map between the two:


And for simplicity I created an orchestration to manage it all (although it could and should all have been done via messaging with no need for an orchestration):


Notice how there is no looping shape in the orchestration – it is doing a straight map. So if my (weird) test flat file looks like:

AAA,First Code,Blue,1

AAB,Second Code,Blue,2

AAC,Third Code,Blue,3

AAD,Fourth Code,Green,4

AAE,Fifth Code,Red,5

AAF,Sixth Code,Green,6

AAG,Seventh Code,Green,7

AAH,Eight Code,Red,8

AAI,Ninth Code,Yellow,9

AAA,Tenth Code,Green,10


It gets mapped to the following XML which is then sent to the SQL Adapter:


    <ns0:myTestSProc Code=AAAName=First CodeType=BluePartnerID=1></ns0:myTestSProc>

    <ns0:myTestSProc Code=AABName=Second CodeType=BluePartnerID=2></ns0:myTestSProc>

    <ns0:myTestSProc Code=AACName=Third CodeType=BluePartnerID=3></ns0:myTestSProc>

    <ns0:myTestSProc Code=AADName=Fourth CodeType=GreenPartnerID=4></ns0:myTestSProc>

    <ns0:myTestSProc Code=AAEName=Fifth CodeType=RedPartnerID=5></ns0:myTestSProc>

    <ns0:myTestSProc Code=AAFName=Sixth CodeType=GreenPartnerID=6></ns0:myTestSProc>

    <ns0:myTestSProc Code=AAGName=Seventh CodeType=GreenPartnerID=7></ns0:myTestSProc>

    <ns0:myTestSProc Code=AAHName=Eight CodeType=RedPartnerID=8></ns0:myTestSProc>

    <ns0:myTestSProc Code=AAIName=Ninth CodeType=YellowPartnerID=9></ns0:myTestSProc>

    <ns0:myTestSProc Code=AAAName=Tenth CodeType=GreenPartnerID=10></ns0:myTestSProc>


So after configuring all the ports and binding everything as per normal SQL Adapter, we end up with nine rows in the table. Nine rows? Hah! Notice that the code on the last line is ‘AAA’, so the stored procedure just updates the row that was inserted first.

This is pretty powerful: all the calls to the stored procedure get wrapped around the same DTC transaction. If one of them fails (which I tested by changing a PartnerID to a string) the whole thing gets rolled back. We have something similar in production uploading a file with 30 thousand records in less than a minute as well.


Hope it’s useful to other BizTalkers.
–Thiago Almeida

41 thoughts on “Multiple Stored Procedure Calls using the BizTalk SQL Adapter without a loop shape”

  1. This is so damn easy it makes me cry that I did not figure this out sooner! This is a huge performance boost for some of my solutions!!!

  2. Thanks all! One thing I forgot to mention is that this will not work if your stored procedure is returning XML back to the orchestration. The new “WCF LOB Adapter SDK”-based SQL Adapter will allow that.

    1. Hi Thiago,

      Please send me the sample running for this application. As I am pretty new in Biztalk so not able to understand this. Although I tried to make the application like this but its just fetching the first row only and passing the same to database. But its not transferring the other rows.

      Please help me and send me the sample. It is too urgent to save my job.


  3. Hi Thiago Almeida! Do you know something about this in Oracle Adapter? Can be done? Because we have something like this in the Metadata generated Schema:


    So… as the first element cannot be multiple, I guess there is no other option than the loop.

    Best regards and congrats for the BizTalk/SQL tip 😉

    Daniel Santana

  4. Hummmm…. problem with the posted schema… Lets try again…

    [?xml version=”1.0″?]
    [xsd:schema xmlns:PKG_ATRIBUFUN_IN=”[OracleDb://OracleDB_SADBR_FPW/SADBR_FPW/Procedures/PKG_ATRIBUFUN_INSERT]” elementFormDefault=”qualified” targetNamespace=”[OracleDb://OracleDB_SADBR_FPW/SADBR_FPW/Procedures/PKG_ATRIBUFUN_INSERT]” xmlns:xsd=””]
    [xsd:element name=”SP_ATRIBUFUN_INSERT”]

  5. Hi Daniel,

    Sorry, I haven’t done this with the oracle adapter. With the SQL adapter the adapter understands the multiple lines in the schema. But like you said – the root element can’t repeat so doesn’t look like it’ll work like with the SQL adapter.

  6. this is excellent. may i have detail description of this problem? i will be very helpfull for me.

  7. I have a similar scenario :

    2 Schemas — SCHEMA-A and SCHEMA-B —> generated by SQL Adapter
    I have a Orchestration variable “ID”
    SCHEMA-A(Response)+ Variable needs to be Mapped to SCHEMA-B(Request)

    ROOT(SCHEMA-A-Response)(Multiple) >>>>>>>>>> ROOT (SCHEMA-B-REQUEST)(Multiple Insert)
    ID (Variable inside Orchestration)
    How do i achieve this suggestions please.

    Thanks in advance..

  8. I implemented the idea of the solution in my project but I have 2 Questions:

    1. I tried it with unbounded and with leaving the Max Occurs property field emtpy and both times all lines reseach the database?!?

    2. How to configure the request-response port: I left both on PassThruTransmit and got

    Inner exception: Received unexpected message type ” does not match expected type ‘http://test_MasterData#rsp_test_MasterData’.

    But Target Namespace and Response Document Root Element are correct.

    1. Hi,

      Leaving Max Occurs empty (wich means 1, the default value) or changing it to unbounded will both times create the correct XML after the map and almost all the time work. This is because BizTalk doesn’t by default validate the output of the map against the schema. The reason I changed it to unbounded is just to be throrough and make sure it would work even if there are any component in the processing of the output of the map that validates the message agaisnt the schema.
      Try using the XMLTransmit and XMLReceive pipelines on your request-response message.

      1. Thanks for your quick response
        Ok I understand then I will also use unbounded
        XML Receive works fine, XML Transmit seems not to be essential necessary

  9. Thanks for the nice post. I have implemented the same by first inserting multiple records using one stored proc and then calling other stored proc to do some operation on those records. However, I am facing SQl server lock issues. Not sure whether SQL adapter calls the proc under a transaction and whether this is closed. The second procedure call operating on insert records is facing the deadlock issue. I have implemented calls to both procs using SQL adapater within the same orchestration. Any guidance would be appreciated. thanks.

    1. Hi there,
      Have you tried changing the isolation level on the WCF-Custom adapter properties under the Messages tab to ‘Read Commited’. The next thing after that is looking at the code of your two stored procedures to see if they can be changed to avoid deadlocks.
      If you’re using BizTalk 2006R2 there is this hotfix that could help as it applies to WCF-Custom and also the adapters in the BizTalk Adapter Pack 2.0:
      I’m not sure if there will be the same hotfix for BizTalk 2009 or if it was already included, you would have to contact Microsoft support for it. It is included in the BT 2006 R2 Service Pack 1.


  10. Thanks Thiago,

    I has download the sample, but when i rebuild it, there is an error appear:

    “a schedule ‘Orchestration_1(servicelink implements Provider)’ with parameters cannot have an activatable receive ”

    I have change the server with my own server…

    Great thanks your reply Thiago…

    1. Thanks Nengsih, glad you got it working. Where did you have to change the server to your own server? Was it in the BizTalk project properties or on the Test project?


  11. Hi,
    Is the same possible if your inbound document is xml which contains repeating nodes or does this approach work only for flat files?


    1. Hi Tony,

      Yes it should be fine if the source is an XML file. As long as you map it to the correct destination schema for the SQL adapter it will be fine.

      1. Hi Thiago,

        Thanks for your reply.
        Unfortunately as mentioned in one of the above posts, am getting the error – “a schedule ‘Orchestration_1(servicelink implements Provider)’ with parameters cannot have an activatable receive ”, not sure how to fix that.


        1. Hi Tony, sorry – the sample had role links in the orchestration, that shouldn’t be there. I’ve updated the sample with normal ports without role links. Can you please redownload and try again?


  12. Hi Thiago,

    Many thanks for your support and reply.
    Yes I downloaded the latest code and was able to build it.


  13. Hello, i have followed your instructions to create an orchestration that sends multiple requests to the SQL adapter.

    But i also need to receive replies from the adapter. The XML file returned from the adapter only contains one result instead of as many results as i wanted. I saw in your example that your response only contains a single “Success” element which is different from what i need.

    My SQL service schema looks like this:

    So both my request and response structures are set to be unbounded, so this is probably different from the problem Rimps had.

      1. Hi,

        Sorry I don’t think you can get the results when using multiple requests like this. You might have to do it the old way inside a loop in an orchestration and make each call separately.


  14. Pingback: Götter Erzengel

Comments are closed.

BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More

Manage and monitor serverless
components effortlessly

Learn More
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top