Forum Replies Created
-
AuthorPosts
-
July 23, 2008 at 6:41 AM in reply to: SQL Adapter – returning only the first response….not returning all responses #20222
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.
Anon,
When the adapter wizard generates your sql adapter schema (that you posted above), the schema will be ‘saved’ to your project. You will need to create a new map and select this sql adapter schema as your source message and select another schema as the destination message – hook up the two schemas as per a normal map.
HTH, Nick.
Can you mark this question as answered if it, erm…. is?
You could try a Google search on how to get your reader talking to BizTalk:
- http://msdn.microsoft.com/en-us/library/aa479354.aspx
- http://www.microsoft.com/biztalk/en/us/rfid.aspx
- http://blogs.msdn.com/irvingd/pages/biztalk-rfid-device-provider-dspi-for-phidget-devices.aspx
- http://www.sirit.com/index.php?id=316&sub_id=296
Nick.
July 22, 2008 at 10:28 PM in reply to: A way to determine the failing node during routing error #20217Yes, that’s easier than my complicated way. I wanted to generate a unique name using the MessageID of a message from my main orchestration, but I guess I’ll go with your solution. Thanks again !
Regards, Michael
There are some articles floating around on exception management. See http://biztalkhotrod.com/default.aspx , the recent issue has a article about exception management and the enabling of tracking on receive / send pipelines and creating a fault message for orchestrations. There are different schemas for the receive and send pipeline error messages, and the orchestration fault message is custom created. Check out the article, it is a solid approach for exception management.
Hey Nick,
Thats a real Guru…that sounds logical and more easier than I thought of..thanks a lot for the help mate…I would try both the options and let you know once done..I would get back to you if I have any problems[;)]
Hey Mate,
Thanks alot for the reply,I had this thought in my mind too but after using the Mapper would I be able to pass the output as a string to another webservice?Please advice.
July 22, 2008 at 5:37 PM in reply to: SQL Adapter – returning only the first response….not returning all responses #20213This 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. receiveProblem solved. We installed SP2 🙂
July 22, 2008 at 8:31 AM in reply to: SQL Adapter – returning only the first response….not returning all responses #20211sorry 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 ?
Anon,
I’m not sure whether you’re after something a little more low level than this, but….
I would have a map from your SAP request message (the message that is being sent to your Orchestration exposed as a web-service) to the request message that BizTalk will be sending to the external web-service. In this map you will be adding the detail from the SAP request to your head/footer external message request.
You will then call the external web-service within your orchestration using a request/response port (sending the newly built external request message).
A second map in your orchestration will then map the external web-service response back to the SAP response, which will be send back via the request/response port that is used to expose your orchestration as a web-service.
Alternatively….
Expose the schema that the SAP web-service is consuming as a web-service (instead of your orchestration); subscribe to the MessageType of this schema with a Send Port that consumes your external web-service. On this Send Port use the first map (above) on the Send side and the second map on the receive side of the port and you have a messaging only solution – no need to host an orchestration!
For the messaging only solution, take a look at the following for more information:
- http://www.digitaldeposit.net/blog/2006/12/biztalk-2006-soap-adapter-web-service.html
- http://blogs.msdn.com/richardbpi/archive/2006/11/10/using-only-biztalk-messaging-with-request-response-on-both-ends.aspx
- http://msdn.microsoft.com/en-us/library/aa560545.aspx
You will also be consuming web-services without web-references:
HTH, Nick.
You could probably do this with the assembler and an envelope schema, but it would require using promoted properties to populate the envelope during the assembling process. Your case sounds pretty simple, so you might want to check if it isn’t actually simpler to just create the entire message using a map…
July 22, 2008 at 6:20 AM in reply to: A way to determine the failing node during routing error #20208For the synchronisation, could you not read the context and determine the original filename?
Dhaval,
Looks like I had a similar problem back in 2006 when working with BizTalk 2004…..
I had a customer that needed to receive messages that included the DTD DOCTYPE declaration so wrote a Send Pipeline component (see http://www.modhul.com/2006/12/28/add-doctype-declaration-component/ for source code) to add the DOCTYPE back into the message based on the MessageType (the message coming out of BizTalk did not include the DOCTYPE declaration).
You will need a similar Receive Pipeline decode component that removes the DOCTYPE as the message is received into BizTalk so it doesn’t break the Xml Disassembler – you could perform this with a simple RegEx replace for example.
HTH, Nick.
-
AuthorPosts