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,  

CONSTRAINT [PK_tbMyTestTable] PRIMARY KEY CLUSTERED 
([id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

 And I have this stored procedure on the same database:

CREATE
PROCEDURE [dbo].[myTestSProc]

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

AS BEGIN

    SET NOCOUNT ON;

    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

    ELSE

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

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

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

END

 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:SQLInsertCodeReq
xmlns:ns0=http://MyBizTalkProject/SQLInsert>

    <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>

</ns0:SQLInsertCodeReq>

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.

DOWNLOAD THE SAMPLE CODE

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

  • Good Trick Thiago!

  • damn … that’s most definitely useful info. will make sure to make use of this in future.

  • For extra bonus points, let’s see your compensation routine for this! 🙂

  • Ben

    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!!!

  • Ben

    Oh, and thanks for posting this!

  • 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.

    • Rimps

      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.

      Regards.
      Rimps

  • Nice trick, I tried it out on my BizTalk 2006 R2 VPC and worked like a charm.

  • Excellent tip.. it will certainly boost performance…

  • Pingback: Tricks with Schemas Part 1: De-batching from a SQL Receive Port « Connected Pawns()

  • oblilaitatips

    Hi all!
    Nice site!

    Bye

  • Daniel Santana

    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

  • Daniel Santana

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

    [?xml version=”1.0″?]
    [xsd:schema xmlns:PKG_ATRIBUFUN_IN=”http://schemas.microsoft.com/[OracleDb://OracleDB_SADBR_FPW/SADBR_FPW/Procedures/PKG_ATRIBUFUN_INSERT]” elementFormDefault=”qualified” targetNamespace=”http://schemas.microsoft.com/[OracleDb://OracleDB_SADBR_FPW/SADBR_FPW/Procedures/PKG_ATRIBUFUN_INSERT]” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”]
    [xsd:element name=”SP_ATRIBUFUN_INSERT”]
    [xsd:complexType]

  • 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.

  • Pingback: Using the BizTalk WCF-SQL Adapter to load a flat file into a SQL Server 2008 table « Connected Thoughts – Thiago Almeida()

  • nazmul

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

  • Reddy

    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)
    LNAME LNAME
    FNAME FAME
    ID (Variable inside Orchestration)
    How do i achieve this suggestions please.

    Thanks in advance..

  • RaviKumar Bhuvanagir

    This is an Excellent post

  • Peter

    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.

    • 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.

      • Peter

        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

  • 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.

    • 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: http://support.microsoft.com/kb/960731/en-us
      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.

      Regards,
      Thiago

  • Nengsih Fitria

    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…

    • 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?

      Regards,
      Thiago

  • Tony

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

    Thanks

    • 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.

      • Tony

        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.

        Regards
        Tony

        • 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?

          Regards,
          Thiago

  • Tony

    Hi Thiago,

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

    Regards
    Tony

  • Jernej

    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.

    • Jernej

      for some reason i can’t post my code here.

      I hope you can see it from here: http://www.sourcepod.com/hubqht65-4518

      • 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.

        Regards,
        Thiago

  • Hello,

    Really handy technique this one. Just in case it helps anyone else out, I came across Thiago’s article first and had some issues getting it to work in BizTalk 2010. There are a few subtle differences outlined in the following article which did the trick for me:

    http://geekswithblogs.net/StuartBrierley/archive/2011/10/19/biztalk-server-2010—using-the-wcf-sql-adapter-to-make.aspx

    Cheers,

    Keith

  • Pingback: Götter Erzengel()

  • Pingback: Multiple Stored Procedure Calls using the BizTalk SQL Adapter without a loop shape | Jesse's Blog()

One Platform Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

One Platform - Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

One Platform - Operations, Monitoring and Analytics Software
ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

Back to Top