none
sqlBinding - Procedure or function expects parameter which was not supplied. RRS feed

  • Question

  • Hi

    I am attempting my first sqlBinding LOB adapter port with Biztalk 2009.

    I have a stored procedure:

    CREATE PROCEDURE [dbo].[UpdatePOMPT]

    @POMPTUpdateId varchar(50),

    @PoNbr varchar(50),

    @PoLineRef varchar(50),

    @NewPromiseDate datetime

    AS

    BEGIN

    ...

    END

    I have generated my schemas by "add Generated Items" choosing the sqlBinding etc which creates the schema. I have created my map and imported the bindings. I have used both Procudure and Strongly Typed Procedures in the LOAB adapter wizard but I keep getting this error for both:

    System.Data.SqlClient.SqlException: Procedure or function 'UpdatePOMPT' expects parameter '@POMPTUpdateId', which was not supplied.

    I have stopped the port and the message looks correct to me:

    <ns0:UpdatePOMPT POMPTUpdateId="E152D6B3-D3C5-48BD-8052-29FDDACC8488" PoNbr="1" PoLineRef="12345" NewPromiseDate="2010-10-22" xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
    </ns0:UpdatePOMPT>

    Any ideas?

    Thanks


    Biztalk Developer
    datetime
    varchar(50),
    varchar(50),
    PROCEDURE [dbo].[UpdatePOMPT]
    Wednesday, April 14, 2010 6:10 AM

Answers

  • Another trick is to right-click on the schema and click generate instance. Then you can compare this generated file with the data you are testing with.

    I think the default child nodes are elements not attributes. Try using this for test data:

    <ns0:UpdatePOMPT xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
              <ns0:POMPTUpdateId>E152D6B3-D3C5-48BD-8052-29FDDACC8488</ns0:POMPTUpdateId>
              <ns0:PoNbr>1</ns0:PoNbr>
              <ns0:PoLineRef>12345</ns0:PoLineRef>
              <ns0:NewPromiseDate>2010-10-22</ns0:NewPromiseDate>
    </ns0:UpdatePOMPT>

    The generated instance should look more like the above format.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    • Marked as answer by Jody Petroni Thursday, April 15, 2010 4:56 AM
    Thursday, April 15, 2010 4:03 AM
    Moderator

All replies

  • Hi,

    Have validated the message against the schema? Can perhaps you supply the generated schema?

    Regards,

    Steef-Jan Wiggers
    MCTS BizTalk Server
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly


    BizTalk
    Wednesday, April 14, 2010 7:19 AM
    Moderator
  • Another trick is to right-click on the schema and click generate instance. Then you can compare this generated file with the data you are testing with.

    I think the default child nodes are elements not attributes. Try using this for test data:

    <ns0:UpdatePOMPT xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
              <ns0:POMPTUpdateId>E152D6B3-D3C5-48BD-8052-29FDDACC8488</ns0:POMPTUpdateId>
              <ns0:PoNbr>1</ns0:PoNbr>
              <ns0:PoLineRef>12345</ns0:PoLineRef>
              <ns0:NewPromiseDate>2010-10-22</ns0:NewPromiseDate>
    </ns0:UpdatePOMPT>

    The generated instance should look more like the above format.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    • Marked as answer by Jody Petroni Thursday, April 15, 2010 4:56 AM
    Thursday, April 15, 2010 4:03 AM
    Moderator
  • stupid me,

    I forgot to update my xslt for the map I am using...is produced attibutes rather than elements.

     

    Sorry for waisting your time.

     

    Cheers


    Biztalk Developer
    Thursday, April 15, 2010 4:58 AM
  • No problem! Glad to help.

    Cheers


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Thursday, April 15, 2010 5:12 AM
    Moderator