- This topic has 12 replies, 1 voice, and was last updated 9 years, 2 months ago by
.
-
Posts
-
-
Hi,
I am calling a stored procedure which returns a Set of records
using the FOR XML using the SQL Adapter -> request- reponse port
used!
It uses the generated request response schemas for the stored procedure.Before calling the request send port i transform the data to the generated request schema.
After calling the response recieve port i transform the response to another message.The problem is that for every customer in the customers request
only the first customer details are returned by the response message.The database calls are being made fine , i tested it by inspecting
the request message itself and aditionally chnaging the stored proc to
insert a record in a temp table for every call to the procedure to see
the
number of times it calls the procedure.
Say 3 customer ID’s were sent in the request file …i see
that are 3 inserts made into the temp table hence three calls were
made but the
response has only details for one message. I tried the calls with the indiviual 3 customer id’s and i get resultswhy am i getting only the response for the first customer id even though the procedure was called thrice ?
what is the problem here ?
-Jerry
-
Jerry,
Its not often we get Shakespearean language on here!!! (“even though the procedure was called thrice“)
I think you need to provide the SQL that you are executing for us to be able to help further as you appear to have the BizTalk bits working (i.e. you are sending a request message and are receiving a response, only the response is incorrect).
Also, you state that multiple customer Id’s are sent in the request file, but later say that you call the procedure three times – can you clarify how the request is being made and send a copy of the Xml request message so we can see how the Stored Proc is being called.
Cheers, Nick.
-
sorry it is kind of hard to explain it i try my best here
i created a send shape with message set to the sql request schema (generated) and then a receive shape with message set to the sql response schema.the send and receive are then connected to a request-response port. Hence there is only a physical SQL send port
As this is a request-response send port there is no physical receive port.i tested the messages before the send SQL port…it looks good
the message after the receive port has only one result.the stored procedure seems to work fine.
my i/p file had 4 customer items that were mapped to the SQL request schemathe SQL request schema created is as below …..where spGetCustomerDetails is the stored proc
<?xml version=”1.0″ encoding=”utf-8″ ?>
<ns0:customerDetailsRequest xmlns:ns0=”http://SQLTest”>
<ns0:spGetCustomerDetails customerID=”5″/>
<ns0:spGetCustomerDetails customerID=”21″/>
<ns0:spGetCustomerDetails customerID=”7″/>
<ns0:spGetCustomerDetails customerID=”3″/>
</ns0:customerDetailsRequest>for each stored proc call more than one records are returned as xml using the FOR XML AUTO,ELEMENTS
the response is like ………
<customerDetailsResponse xmlns=”http://SQLTest”>
<customer_data>
<customer_request_id>5</customer_request_id>
<tel_number>124567890</tel_number>
</customer_data>
<customer_data>
<customer_request_id>5</customer_request_id>
<tel_number>9876543210</tel_number>
</customer_data>
<customerDetailsResponse>the stored proc is like …………
ALTER PROCEDURE [dbo].[spGetCustomerDetails]
@customerID varchar(10)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @dynamicSQL varchar(MAX)
–INSERT INTO [CustomerDetails].[dbo].[$temp]
— ([Customer_id])
— VALUES
— (@customerID)Set @dynamicSQL = ‘SELECT TOP 10 customer_id as customer_request_id,tel_number FROM customer_data WHERE customer_id =’ + @customerID
Set @dynamicSQL = @dynamicSQL + ‘FOR XML AUTO’
EXEC(@dynamicSQL)
ENDuncommenting the commented lines in the stored proc will insert rows into a temp temp and hence i can monitor how many times the stored procedure gets called.
the response is only corresponding to the first stored proc call , i did find that the stored proc was callled 4 times but the response relates to only the first request call……. is it clearer now ?
-
This is what i found
I tested the Stored proc with the 4 CustomerID’s and i am getting the 4 desired xml results.
For the physical
Send SQL Port i set the Receive Pipeline ( this is a solicit-response
port ) to Passthrough instead of XMLReceiveThis errors out at the orchestration
On looking at the message details it looks like the message is what i expect it to be and have 4 message parts
I think the XMLReceive is disassembling the incoming message parts and sending only 1(first) message part.
What is a resolution to get all the 4 message parts ? i cannot use passthrough for the orch. receive-
Jerry,
I may be wrong here, but it sounds like you are just receiving the body part of the response message – there can only be one body part in a multipart message (this is normally the first part). A quick way to check will be to view the message detail in the Admin Console and see which part is marked as the body.
One way to resolve this would be to setup a loop in your orchestration and on each iteration you query the stored procedure with just one customer Id (returning just one result) and perform the necessary work on that. You could also look at an aggregation pattern – starting a new orchestration instance to query the database – that will acheive the same thing if you want this process to be asynchronous.
Obviously this approach isn’t half as efficient as your original approach, but it would appear you can’t call a stored procedure that returns a value multiple times in a single adapter call; this would be fine if you were simply performing a CUD, but no Retreival!
Nick.
-
I am facing some problem using same scenario, It is very fine with
orchestration to use LOOP shape and call stored procedure every time,
If my Request packet has three CustomerID it will returns me three
XML files in my FILE location on send port. It’s cool and no issue…
[:)]But when i am trying to put all three XML responses from Stored
Procedure into one XML and pass it to send port then I am encountered
with no of problems.please help me …. thanks in advance
Cheers 🙂
Abhishek Doyle
-
-
You are sending multiple stored procedure calls to the adapter at once.
The adapter returns one body part per query it has run.
BizTalk will only show you the first message body part. You will need to iterate through the parts in the message to access all results.
-
-
Hi Jerry,
Did you ever find an answer to your SQL adapter issue? I have a simlar problem where I want to use all the message parts returned by SQL Adapter but only first seems to be available even though looking at the message thru Biztalk Admin, it seems like all parts all returned successfully. Database inserts are fine as well.
I don’t want to use a loop in Orchestration. I would love to know how you solved this issue.
Thanks!
Adnan
-
If you want to do this without using loops in the orchestration, maybe because you want straight through messaging, you will need to process the message parts into a single message in a pipeline. You will need a 3 level xml structure rather than the default sql adapter 2 level structure, something like the xml below
<QuerySet><Query><QueryResult/></Query></QuerySet>
While the message parts are parts of a message, they are in fact seperate xml documents, and as such can not be accessed using standard message processing (maps, dot notation and the like).
I have done this with loops, and also found a useful blog, but I have lost the link that gave the example. I can try and post the example if it will be of use
-
Hi Dino,
Thanks for your response. Thats what I was seeing in the message when I debugged my orchestration and looked at the response message. I’m glad you confirmed that SQL adapter returns 3 XML docs so your suggestion of combining them in a pipeline makes sense.
This is only my second Biztalk Project so just would like to confirm couple of things regarding assembling the message in a custom pipeline:
Im using Solicit request-response SQL Adapter. Sould I replace Default XMLReceive pipeline with a Custom pipeline. Can you assemble the message parts in a single message in a receive pipeline? I thought that you can only disassemble files in a receive pipeline.
It will be very helpful to me to see an example.
Thanks Again!
Adnan
-
I did not go down the pipeline route, so I can not give you an exact example, I used a loop to process the message parts one by one within an orchestration (I know this was a bit of a cop out, but I needed quick results).
However were I to be given the time to do this….
I would write a custom pipeline component. This is quite involved, but basically you get access to the biztalk message, and once you have acces to this, you can do whatever you like. I would then build a pipeline that uses your component then passes the result to the xml decode component.
If written generically, with parameters for the enclosing xml and the enclosing namespace (just like the sql send\receive adpater) you would have a tasty generic component for handling multipart messages that could be posted for all and sundry!
Let me know if you need the loop project, and I will ty and package and send it to you, and I would be interested in seeing and maybe helping with the custom component if you do go down that route
D
-
-
-
-
-
-
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.