Adding SQL Stored Procedure Parameters to an existing Schema and SendPort

Question

This biztalk Orch was working perfectly before I touched it. I did two things:

1. Modified the schema below adding 3 key, type and c_date

2. Modified my message transform so that those 3 additional parameters were mapped to the destination schema.

   <?xml version="1.0" encoding="utf-16"?>
   <schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns:ns3="http://schemas.datacontract.org/2004/07/System.Data" elementFormDefault="qualified" targetNamespace="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo" version="1.0" xmlns="http://www.w3.org/2001/XMLSchema">
     <import schemaLocation=".\sqlBinding_System_x2eData.xsd" namespace="http://schemas.datacontract.org/2004/07/System.Data" />
     <annotation>
       <appinfo>
         <fileNameHint xmlns="http://schemas.microsoft.com/servicemodel/adapters/metadata/xsd">Procedure.dbo</fileNameHint>
         <references xmlns="http://schemas.microsoft.com/BizTalk/2003">
           <reference targetNamespace="http://schemas.datacontract.org/2004/07/System.Data" />
         </references>
       </appinfo>
     </annotation>
     <element name="Vendor_Receive_IPN_Message_sp">
       <annotation>
         <documentation>
           <doc:action xmlns:doc="http://schemas.microsoft.com/servicemodel/adapters/metadata/documentation">Procedure/dbo/Vendor_Receive_IPN_Message_sp</doc:action>
         </documentation>
       </annotation>
       <complexType>
         <sequence>
           <element minOccurs="0" maxOccurs="1" name="data" nillable="true" type="string" />
           <element minOccurs="0" maxOccurs="1" name="key" nillable="true" type="string" />
           <element minOccurs="0" maxOccurs="1" name="type" nillable="true" type="string" />
           <element minOccurs="0" maxOccurs="1" name="c_date" nillable="true" type="dateTime" />
         </sequence>
       </complexType>
     </element>
     <element name="Vendor_Receive_IPN_Message_spResponse">
       <annotation>
         <documentation>
           <doc:action xmlns:doc="http://schemas.microsoft.com/servicemodel/adapters/metadata/documentation">Procedure/dbo/Vendor_Receive_IPN_Message_sp/response</doc:action>
         </documentation>
       </annotation>
       <complexType>
         <sequence>
           <element minOccurs="0" maxOccurs="1" name="Vendor_Receive_IPN_Message_spResult" nillable="true" type="ns3:ArrayOfDataSet" />
           <element minOccurs="1" maxOccurs="1" name="ReturnValue" type="int" />
         </sequence>
       </complexType>
     </element>
   </schema>
   

I'm getting this error:

The adapter failed to transmit message going to send port "SendPort IPN Message to SQL" with
URL "mssql://myserver:1433//mydb?". It will be retransmitted after 
the retry interval specified for this Send Port. 
Details:"Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: 
The start element with name "key" and namespace 
"http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo" was unexpected. 
Please ensure that your input XML conforms to the schema for the operation.

What step am I missing?

Answer 1

When you add nodes to a SP schema, they have to be

1. In the same order as in the procedure (is @key the second param, or did you put it before @data?).
2. Use the same spelling and casing as the procedure (is it called @key or @Key in the procedure?).
3. Use an XSD data type that corresponds to the datatype the procedure expects (is @key a (N)VARCHAR or (N)CHAR?).

The error you're getting indicates either 1 or 2 isn't correct. Show us your procedure definition to be sure. You could also regenerate the schema for the procedure in a separate (throw away) project and compare it with your changes.