Biztalk 2009 and SQL Server Adapter – Schema created incorrectly

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › Biztalk 2009 and SQL Server Adapter – Schema created incorrectly

Viewing 1 reply thread
  • Author
    Posts
    • #23522

      I am trying to call a stored procedure using the SQL Adapter. The problem that I am having is that the schema generated by the adapter creates a success element for the response. I can get the call to the stored procedure to work but I cannot do any mapping/assigning because the schema that is returned is completely different than what the SQL Adapter created. I have copied the XSD created by the SQL Adapter and the stored procedure that I used with the SQL Adapter

      <?xml version=”1.0″ encoding=”utf-16″?>
      <xs:schema xmlns:b=”http://schemas.microsoft.com/BizTalk/2003&#8243; xmlns=”spDealInsert_MsgRecByBiztalk” attributeFormDefault=”unqualified” elementFormDefault=”qualified” targetNamespace=”spDealInsert_MsgRecByBiztalk” version=”1.0″ xmlns:xs=”http://www.w3.org/2001/XMLSchema”&gt;
        <xs:annotation>
          <xs:appinfo>
            <msbtssql:sqlScript value=”exec [spDealInsert_MsgRecByBiztalk] @DealID=&quot;12345678&quot;” xmlns:msbtssql=”http://schemas.microsoft.com/BizTalk/2003&#8243; />
          </xs:appinfo>
        </xs:annotation>
        <xs:element name=”spDealInsert_MsgRecByBiztalkRequest”>
          <xs:annotation>
            <xs:appinfo>
              <b:properties>
                <b:property distinguished=”true” xpath=”/*[local-name()=’spDealInsert_MsgRecByBiztalkRequest’ and namespace-uri()=’spDealInsert_MsgRecByBiztalk’]/*[local-name()=’spDealInsert_MsgRecByBiztalk’ and namespace-uri()=’spDealInsert_MsgRecByBiztalk’]/@*[local-name()=’DealID’ and namespace-uri()=”]” />
              </b:properties>
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:element name=”spDealInsert_MsgRecByBiztalk”>
                <xs:complexType>
                  <xs:attribute name=”DealID” type=”xs:int” />
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name=”spDealInsert_MsgRecByBiztalkResponse”>
          <xs:complexType>
            <xs:sequence>
              <xs:element name=”Success”>
                <xs:complexType />
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:schema>

       

      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
       
       
      CREATE Procedure [dbo].[spDealInsertMsgRecByBiztalk]
      (
            @DealID     int
      )
      AS
      BEGIN
      SET NOCOUNT ON
      —
      declare @identityID int
      insert into Deal (DealID, MessageReceivedByBiztalk)
      select @DealID, getdate()
      set @identityID = scope_identity()
      —
      select @identityID as ProcessID for xml raw
      —
      —
      —
      END

       

    • #23523

      Hi,

       

         Before you generate the schema using the SQL Adapter wizard, modify your stored procedure to produce the following lines of code:

      …

      SET NOCOUNT OFF

      SELECT @identityID as ProcessID FOR XML AUTO, XMLDATA

       

         Then, after your schema is generated, comment out the XMLDATA portion of the SELECT statement above. That should do the trick.

       

         Best,

       

         Daniel.

       

      • #23525

        Unfortunately, I do not have the  project with me here at home so I will have to wait to try this Monday. However, I remember trying “for xml auto” in my stored proc. (a little different that you have suggested) and it would not compile. I hope this solution works. I will report back on Monday.

         

        Thanks,

        Blake

Viewing 1 reply thread
  • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.