none
Biztalk orchestration RRS feed

  • Question

  • HI,

    I have a requirement of taking values from Sql and updating it to CRMOD DataBase.

    Here my challenge is i have to update two tables at a time in CRMOD.Inbetween I am also using web service for request.

    So how can i update two table at a time using single orchestration?

    Monday, April 15, 2013 10:43 AM

Answers

  • Hi,

    In Port1 for request and response i am using Multipart Message QuotePeriod_Update_Input and QuotePeriod_Update_Output.

    with this i also have to include Account_Update_Input and Account_Update_Output. These are through WSDLS.

    In sql i have used stored procedure to retrive values from two tables.

    I can't write stored procedure for Oracle. I just have to send Request request to CRM with input from sql. But not able to understand whether to use 2 request-response port .


     Does the code you use in Oracle complicate ? If you get the data from stored procedure, use something like update will update your tables in Oracle database. (I didn't use Oracle before, I don't know the code you use actually )

    Molly,
    Please Mark as the Answer, if this answers your question. Please vote as helpful, if this post is helpful.

    Wednesday, April 17, 2013 8:07 AM

All replies

  • Hi,

    You can write a stored procedure to update the two tables and invoke this stroed procedure from biztalk orchestration with the required input data (as an xml).

    The below link will help you achieve this.

    http://www.codeproject.com/Articles/37808/How-to-invoke-Stored-Procedures-with-the-FOR-XML-c

    Thanks,

    Sumit


    Sumit Verma - MCTS BizTalk 2006/2010 - Please indicate "Mark as Answer" or "Mark as Helpful" if this post has answered the question


    Monday, April 15, 2013 10:51 AM
  • HI,

    But my requirement is entirely different:

    This is my input:

    Market Status,

    Table

    Field Name

    Account

    RowId

    Account

    TransferStatus

    QuotePeriod

    StartDate

                   

    Account and quote Period are two different tables in sql. In stored procedure i have used select query to get values from two tables and i have to request for CRMOD which is cloud(oracle DB) which also has same tables.

    I am using WSDL'S as target in request-response message. But not able to understand how to request two tables account and quote period in request(talking about request-response port) with single message. How can i map fields which are going to two different tables.

    Transport is through  agreed secure and reliable protocol channel

    Monday, April 15, 2013 11:21 AM
  • As per Sumit you need stored procedure to get value from two table as per database good practice or you can use directly select query in  WCF-SQL adapter to get those value from SQL(refer this link http://soa-thoughts.blogspot.co.uk/2010/08/wcf-sql-adapter-table-operations.html ) and make request to ( CRMOD which is cloud(oracle DB) ,If I am able to understand your scenario ,you are integrating with CRM , please refer those links

    http://synthesisconsulting.net/blog/2012/5/10/oracle-and-biztalk-server-part-1.html

    http://blog.eldert.net/integrating-crm-2011-with-biztalk-2010-continued/

    http://seroter.wordpress.com/2011/02/10/the-good-bad-and-ugly-of-integrating-dynamics-crm-2011-and-biztalk-server-2010/

    • Proposed as answer by Chandra Kumar Monday, April 15, 2013 1:20 PM
    Monday, April 15, 2013 1:20 PM
  • Hi,

    In Port1 for request and response i am using Multipart Message QuotePeriod_Update_Input and QuotePeriod_Update_Output.

    with this i also have to include Account_Update_Input and Account_Update_Output. These are through WSDLS.

    In sql i have used stored procedure to retrive values from two tables.

    I can't write stored procedure for Oracle. I just have to send Request request to CRM with input from sql. But not able to understand whether to use 2 request-response port .


    Monday, April 15, 2013 2:29 PM
  • Hi,

    In Port1 for request and response i am using Multipart Message QuotePeriod_Update_Input and QuotePeriod_Update_Output.

    with this i also have to include Account_Update_Input and Account_Update_Output. These are through WSDLS.

    In sql i have used stored procedure to retrive values from two tables.

    I can't write stored procedure for Oracle. I just have to send Request request to CRM with input from sql. But not able to understand whether to use 2 request-response port .


     Does the code you use in Oracle complicate ? If you get the data from stored procedure, use something like update will update your tables in Oracle database. (I didn't use Oracle before, I don't know the code you use actually )

    Molly,
    Please Mark as the Answer, if this answers your question. Please vote as helpful, if this post is helpful.

    Wednesday, April 17, 2013 8:07 AM