locked
Typed polling sql server biztalk RRS feed

  • Question

  • Hi All,

    I am working on the application in which i am trying to call the SQL server (stored Procedures).

    I am having few queries as follows :

    Q1 ) When calling SQL server database(Stored Procedures) it's add Two schemas(Procedure.db.xsd and DataSetSchema.xsd) and one binding files - I do remember that early it was genrating Orchestation with Port and port type in it but in my case not created ? why ? i am using Biztalk 2013 R2 and SQL 2014.

    Q2 ) i Got error while calling the Stored Procedure. i have created the Transform using schema.

    This stored procedure will take 4 input's and will return one Field element as CDMXML.

    so the SQL Request schema have 4 fields as Input and the for output the SQL Generate schema looks like as SchemaResponse -> SchemaResult -> ns3:DataSet -><Any>

    i want to handle the return output of the Database so i have created one response schema and now i am trying to map this with DatabaseResponse schema in Map how to do that cause database response will be of type OutputXML = "<TestRootXML>

    <Name>TEst</Name><Address>Test</Address><City>Test</City></TestRootXML>"

    i am not able to handle this in Orchestration or not sure how to assign it in map as it's only one result coming back from database.

    also want's to check in expression that the Response from DB is not or not and on this logic will execute other part of code.


    Thanks and Regards, Nitin.

    Thursday, August 13, 2015 9:39 AM

Answers

  • Hi Nitin,

    I am trying to answer your queries .Feel free to post if any further information is required.

    Q1 ) When calling SQL server database(Stored Procedures) it's add Two schemas(Procedure.db.xsd and DataSetSchema.xsd) and one binding files - I do remember that early it was genrating Orchestation with Port and port type in it but in my case not created ? why ? i am using Biztalk 2013 R2 and SQL 2014.

    Ans : The behavior of WCF SQL adapter is same .You will get one  schema for Procedure.db.xsd and other for DataSetSchema.xsd which is being internally used inside your Procedure.db.xsd .You need to construct SQL request and the response from Procedure.db.xsd (It will contain two records one for the request to SQL and other for response from the SQL).

    When you do add Generate Item It will always create you schema (procedure and dataset ) along with the binding file which will be used for SQL server communication and creating ports inside your BizTalk admin console .It wont generate you any default Orchestration (default orchestration is created while consuming web service in BizTalk) .

    2) i Got error while calling the Stored Procedure. i have created the Transform using schema.This stored procedure will take 4 input's and will return one Field element as CDMXML.

    For response you need to construct the message from Procedure.db.xsd and not from the data set . Once you get any valid out pout from SQL ,then you can map the response as per your requirement .

    Hope this will help you

    Thanks
    Abhishek

    • Proposed as answer by Angie Xu Thursday, August 20, 2015 2:04 AM
    • Marked as answer by Angie Xu Friday, August 21, 2015 1:50 AM
    Thursday, August 13, 2015 9:59 AM
  • 1. Why can't you use Strongly-Typed Procedures?  The difference in output, whether you get a .odx or not, depends on which Wizard you use, Consume Adapter Service vs Add Adapter Metadata.  You should be using Consume Adapter Service.

    2. If you're not using Strongly-Typed Procedures, you have to either hand-craft the response schema from the generated .xds's (replacing <Any> with the DataSet or debatch the DataSet.

    Why can't you use an Orchestration?

    • Proposed as answer by Angie Xu Thursday, August 20, 2015 2:04 AM
    • Marked as answer by Angie Xu Friday, August 21, 2015 1:50 AM
    Thursday, August 13, 2015 11:57 AM
    Moderator

All replies

  • Hi Nitin,

    I am trying to answer your queries .Feel free to post if any further information is required.

    Q1 ) When calling SQL server database(Stored Procedures) it's add Two schemas(Procedure.db.xsd and DataSetSchema.xsd) and one binding files - I do remember that early it was genrating Orchestation with Port and port type in it but in my case not created ? why ? i am using Biztalk 2013 R2 and SQL 2014.

    Ans : The behavior of WCF SQL adapter is same .You will get one  schema for Procedure.db.xsd and other for DataSetSchema.xsd which is being internally used inside your Procedure.db.xsd .You need to construct SQL request and the response from Procedure.db.xsd (It will contain two records one for the request to SQL and other for response from the SQL).

    When you do add Generate Item It will always create you schema (procedure and dataset ) along with the binding file which will be used for SQL server communication and creating ports inside your BizTalk admin console .It wont generate you any default Orchestration (default orchestration is created while consuming web service in BizTalk) .

    2) i Got error while calling the Stored Procedure. i have created the Transform using schema.This stored procedure will take 4 input's and will return one Field element as CDMXML.

    For response you need to construct the message from Procedure.db.xsd and not from the data set . Once you get any valid out pout from SQL ,then you can map the response as per your requirement .

    Hope this will help you

    Thanks
    Abhishek

    • Proposed as answer by Angie Xu Thursday, August 20, 2015 2:04 AM
    • Marked as answer by Angie Xu Friday, August 21, 2015 1:50 AM
    Thursday, August 13, 2015 9:59 AM
  • Hi Nitin,

    There are basically 2 ways of generating the artifacts for BizTalk for WCF SQL:

    1. Add Generated Items -> Add Adapter Metadata

    2. Add Generated Items -> Consume Adapter Service

    Both of these options generates slightly different artifacts in BizTalk Solution:

    "Add Adapter Metadata" generates the following artifacts:

    • BizTalk Orchestration.odx
    • Procedure.db.xsd
    • DataSetSchema.xsd

    "Consume Adapter Service" generates the following artifacts in BizTalk Solution:

    • DataSetSchema.xsd
    • Procedure.dbo.xsd
    • WcfSendPort_SqlAdapterBinding_Custom.bindinginfo.xml

    Thanks

    Rahul

    • Proposed as answer by RahulS_88 Thursday, August 13, 2015 10:40 AM
    • Unproposed as answer by RahulS_88 Thursday, August 13, 2015 10:40 AM
    Thursday, August 13, 2015 10:39 AM
  • 1. Why can't you use Strongly-Typed Procedures?  The difference in output, whether you get a .odx or not, depends on which Wizard you use, Consume Adapter Service vs Add Adapter Metadata.  You should be using Consume Adapter Service.

    2. If you're not using Strongly-Typed Procedures, you have to either hand-craft the response schema from the generated .xds's (replacing <Any> with the DataSet or debatch the DataSet.

    Why can't you use an Orchestration?

    • Proposed as answer by Angie Xu Thursday, August 20, 2015 2:04 AM
    • Marked as answer by Angie Xu Friday, August 21, 2015 1:50 AM
    Thursday, August 13, 2015 11:57 AM
    Moderator