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=“AAA” Name=“First Code” Type=“Blue” PartnerID=“1“></ns0:myTestSProc>
<ns0:myTestSProc Code=“AAB” Name=“Second Code” Type=“Blue” PartnerID=“2“></ns0:myTestSProc>
<ns0:myTestSProc Code=“AAC” Name=“Third Code” Type=“Blue” PartnerID=“3“></ns0:myTestSProc>
<ns0:myTestSProc Code=“AAD” Name=“Fourth Code” Type=“Green” PartnerID=“4“></ns0:myTestSProc>
<ns0:myTestSProc Code=“AAE” Name=“Fifth Code” Type=“Red” PartnerID=“5“></ns0:myTestSProc>
<ns0:myTestSProc Code=“AAF” Name=“Sixth Code” Type=“Green” PartnerID=“6“></ns0:myTestSProc>
<ns0:myTestSProc Code=“AAG” Name=“Seventh Code” Type=“Green” PartnerID=“7“></ns0:myTestSProc>
<ns0:myTestSProc Code=“AAH” Name=“Eight Code” Type=“Red” PartnerID=“8“></ns0:myTestSProc>
<ns0:myTestSProc Code=“AAI” Name=“Ninth Code” Type=“Yellow” PartnerID=“9“></ns0:myTestSProc>
<ns0:myTestSProc Code=“AAA” Name=“Tenth Code” Type=“Green” PartnerID=“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.
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.
Hi, please send me the sample of this.
Hi Renu,
You can download the BizTalk sample solution from here:
http://www.acsug.co.nz/files/Downloads/SQLMultipleSPROCCalls.aspx
Regards,
Thiago
For extra bonus points, let’s see your compensation routine for this! 🙂
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!!!
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.
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
Hi Rimps,
You can download the BizTalk sample solution from here:
http://www.acsug.co.nz/files/Downloads/SQLMultipleSPROCCalls.aspx
You probably forgot to change the stored procedure schema or the flat file schema node to unbounded, that’s why you only get one line inserted.
Regards,
Thiago
Nice trick, I tried it out on my BizTalk 2006 R2 VPC and worked like a charm.
Excellent tip.. it will certainly boost performance…
Hi all!
Nice site!
Bye
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
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.
this is excellent. may i have detail description of this problem? i will be very helpfull for me.
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..
You can try this approach for your second ‘multiple insert’ schema:
http://connectedthoughts.wordpress.com/2008/05/31/updating-repeating-nodes-in-an-xml-document-with-the-same-value-using-xpathmutatorstream/
This is an Excellent post
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.
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
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
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.
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
Hi Thiago,
Many thanks for your support and reply.
Yes I downloaded the latest code and was able to build it.
Regards
Tony
No problem, glad it is now working.
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.
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