none
WCF-SQL Adapter Typed Procedure Response Schema RRS feed

  • Question

  • I'm calling stored procedure using WCF-SQL adapter. Stored procedure CreateOrder accepts XML parameter and returns single parameter OrderId. My question is why despite all efforts to define response message schema according to the documentation (see "Message Structure of Procedures and Functions" in Techincal Reference section of the WCF SQL Adapter documentation file):

    <[STRNG_SP_NAME]Response xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/[SCHEMA]">
      <[STRNG_SP_NAME]ResultSet> 
        <[PRM1_NAME]>value1<[PRM1_NAME]>
        <[PRM2_NAME]>value2</[PRM2_NAME]>
        …
      </[STRNG_SP_NAME]ResultSet>
      <ReturnValue>[Value]</ReturnValue>
    </[STRNG_SP_NAME]Response>
    the adapter returns message with those anonymous StoredProcedureResultSet0 elements similar to the one autogenerated by the Add Adapter Metadata wizard. Here's actual message received from adapter at runtime:

    <CreateOrderResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/Ordering">
      <StoredProcedureResultSet0>
        <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/Ordering/CreateOrder">
          <OrderId>779</OrderId>
        </StoredProcedureResultSet0>
      </StoredProcedureResultSet0>
      <ReturnValue>0</ReturnValue>
    </CreateOrderResponse>
    While I expect message like this according to defined schema (to create it I simply renamed nodes in the schema generated by the wizard to more meaningful ones):

    <CreateOrderResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/Ordering">
      <CreateOrderResultSet>
        <ArrayOfCreateOrderResult xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/Ordering/CreateOrder">
          <CreateOrderResult>
            <OrderId>779</OrderId>
          </CreateOrderResult>
        </ArrayOfCreateOrderResult>
      </CreateOrderResultSet>
      <ReturnValue>0</ReturnValue>
    </CreateOrderResponse>
    Messages are being received by an orchestration just fine with no errors but their schema does not correspond defined one. Is this expected behavior or I'm doing something wrong here? And, by the way, it's BizTalk 2006 R2 with WCF Adapter Pack 2.0 Beta.




    http://geekswithblogs.net/paulp/
    Tuesday, May 5, 2009 10:06 PM

Answers

  • Hi Paul,

    This is actually a problem with the documentation. The schema listed there is incorrect.  The correct schema is listed below and the response you are seeing actually confirms with that.

    <[STRNG_SP_NAME]Response xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/[SCHEMA]">

        <StoredProcedureResultSet0>

            <StoredProcedureResultSet0 xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/ProcedureResultSets/[SCHEMA]/[STRNG_SP_NAME]">

                  <[PRM1_NAME]>value1<[PRM1_NAME]>

                  <[PRM2_NAME]>value2</[PRM2_NAME]>

        …

            </StoredProcedureResultSet0>

       </StoredProcedureResultSet0>

       <ReturnValue>[Value]</ReturnValue>

    </[STRNG_SP_NAME]Response>

    We have taken a note of the issue and the fixed documentation will be up on msdn in the first week of June. Thanks for reporting it!

    Thanks,
    sandeep

    • Marked as answer by Paul Petrov Wednesday, May 6, 2009 3:23 PM
    Wednesday, May 6, 2009 8:02 AM

All replies

  • Hi Paul,

    This is actually a problem with the documentation. The schema listed there is incorrect.  The correct schema is listed below and the response you are seeing actually confirms with that.

    <[STRNG_SP_NAME]Response xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/[SCHEMA]">

        <StoredProcedureResultSet0>

            <StoredProcedureResultSet0 xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/ProcedureResultSets/[SCHEMA]/[STRNG_SP_NAME]">

                  <[PRM1_NAME]>value1<[PRM1_NAME]>

                  <[PRM2_NAME]>value2</[PRM2_NAME]>

        …

            </StoredProcedureResultSet0>

       </StoredProcedureResultSet0>

       <ReturnValue>[Value]</ReturnValue>

    </[STRNG_SP_NAME]Response>

    We have taken a note of the issue and the fixed documentation will be up on msdn in the first week of June. Thanks for reporting it!

    Thanks,
    sandeep

    • Marked as answer by Paul Petrov Wednesday, May 6, 2009 3:23 PM
    Wednesday, May 6, 2009 8:02 AM
  • Thank you, Sandeep. That clears one question.

    Can you tell why the runtime error is not raised then? Since there's no such schema deployed one would expect XML disassembler to throw exception when it can't find matching schema definition. That doesn't happen in this case.

    Thanks,
    Paul
    http://geekswithblogs.net/paulp/
    Wednesday, May 6, 2009 3:25 PM
  • Hi Paul,

    Are you configuring the XMLReceive pipeline and specifying the schema name (DocumentSpecNames) of this tweaked schema? If so, that would only validate the root node name and namespace and since they are the same between the actual and tweaked schema, there won't be any problems. If you additionally turn on ValidateDocument, it will do a full schema validation and then you should see failures.

    Please let me know if I am understanding your scenario right.

    Thanks,
    sandeep
    Monday, May 11, 2009 11:21 AM
  • I used XMLReceive pipeline with default configuration, i.e. no DocumentSpecNames specified.
    http://geekswithblogs.net/paulp/
    Monday, May 11, 2009 3:50 PM
  • Hi Paul,

    If you didn't explicitly specify the schema, BizTalk will try and find a deployed schema based off the root node (name & namespace) of the incoming message. Since in this case the deployed (tweaked) schema still has the correct root node, no error will be flagged at runtime. If you have any promoted properties and the XPATH(s) don't match in the tweaked schema, they won't get promoted in the incoming message. But still no errors will be flagged at runtime.

    Hope that clarifies...

    Thanks,
    sandeep
    Wednesday, May 13, 2009 5:41 AM
  • Sandeep, that's clear now. Thank you!
    http://geekswithblogs.net/paulp/
    Thursday, May 14, 2009 5:27 PM
  • Hi Sandeep/Paul,

     

    I notice that the response message contains the ReturnValue =0

    <ReturnValue>0</ReturnValue>

    Is it the behavior? I hope this is supposed to return the exact number of row inserted/updated/selected?
    Can you please confirm it.

    Regds
    Ashwini

    Wednesday, June 29, 2011 11:08 AM