Home Page › Forums › BizTalk 2004 – BizTalk 2010 › Biztalk 2009 and SQL Server Adapter – Schema created incorrectly
- This topic has 2 replies, 1 voice, and was last updated 9 years, 10 months ago by
community-content.
-
AuthorPosts
-
-
October 23, 2009 at 7:44 PM #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″ xmlns=”spDealInsert_MsgRecByBiztalk” attributeFormDefault=”unqualified” elementFormDefault=”qualified” targetNamespace=”spDealInsert_MsgRecByBiztalk” version=”1.0″ xmlns:xs=”http://www.w3.org/2001/XMLSchema”>
<xs:annotation>
<xs:appinfo>
<msbtssql:sqlScript value=”exec [spDealInsert_MsgRecByBiztalk] @DealID="12345678"” xmlns:msbtssql=”http://schemas.microsoft.com/BizTalk/2003″ />
</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 -
October 23, 2009 at 8:26 PM #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.
-
October 24, 2009 at 9:18 AM #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
-
-
-
AuthorPosts
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.