In ADO.NET we can call a Stored Procedure that returns multiple results sets
.
Can we call this type fo stored procedure from Biztalk Sql Adapter (in an
Orchestration)?   We were trying to do this, because apparently "FOR XML
EXPLICIT" is not supported.  What we need is to return data from two (or
more) tables that don't necessarily have any relationship to each other.  We
would like to do it in one SQL.

Neal Walters
http://Biztalk-Training.com - FREE Biztalk Demos and Videos

[ Post a follow-up to this message ]

HI Neal,

I got this to work in a prototype, and it seams to work OK.

/Alan


"Neal Walters" wrote:

> In ADO.NET we can call a Stored Procedure that returns multiple results se
ts.
>  Can we call this type fo stored procedure from Biztalk Sql Adapter (in an
> Orchestration)?   We were trying to do this, because apparently "FOR XML
> EXPLICIT" is not supported.  What we need is to return data from two (or
> more) tables that don't necessarily have any relationship to each other.  
We
> would like to do it in one SQL.
>
> Neal Walters
> http://Biztalk-Training.com - FREE Biztalk Demos and Videos
>

[ Post a follow-up to this message ]

Hi Alan,

Your "The Bloggers Guide to BizTalk " is great.  You did a lot more than
I expected and I hope to contribute soon.

So, in your prototype to handle the two results sets - what did you do?
The SQL adapter seems to build the SQLService.xsd for only the first SQL
statement in the stored procedure.  I thought about building a second elemen
t
with fields for the second SQL statement, but I didn't want to waste 30
minutes if I knew it would not work.   Was this your approach?

So for example, suppose I want to retrieve all authors and all titles
from the "pubs" demo database.  The schema that the SQL Adapter built looks
like this (I'm giving just the structure without closing tags or without ful
l
xsd)

<Schema>
<SQLTestReq>
<TwoResults>  <!-- this is the stored proc name -->
<SQLTestResp>
<authors>
<au_id>
<au_lname>

Would I just change it to this:
<Schema>
<SQLTestReq>
<TwoResults>  <!-- this is the stored proc name -->
<SQLTestResp>
<authors>
<au_id>
<au_lname>
<titles>
<title_id>
<title>

Thanks again!

Neal Walters
http://Biztalk-Training.com - Free Biztalk 2004 Demos

[ Post a follow-up to this message ]

Hi,

More content for the guide would be great!


My SP looks like this:

ALTER PROCEDURE SelectTwoTables

AS

select * from Customer for xml auto, --xmldata

select * from Supplyer for xml auto, --xmldata



The response part of the schema looks like this:

- <xs:element name="TwoTableTestResponse">
- <xs:complexType>
- <xs:sequence>
<xs:element xmlns:q1="http://TwoTableTest" minOccurs="0"
maxOccurs="unbounded" name="Customer" type="CustomerType" />
<xs:element minOccurs="0" maxOccurs="unbounded" name="Supplyer"
type="SupplyerType" />
</xs:sequence>
</xs:complexType>
</xs:element>
- <xs:complexType name="CustomerType">
<xs:attribute name="CustomerID" type="xs:int" />
<xs:attribute name="SupplyerID" type="xs:int" />
<xs:attribute name="CustomerName" type="xs:string" />
</xs:complexType>
- <xs:complexType name="SupplyerType">
<xs:attribute name="SupplyerID" type="xs:int" />
<xs:attribute name="SupplyerName" type="xs:string" />
</xs:complexType>


I had to add the schema infor for second result set manually, but it's not
too hard to do.

This is a real basic prototype, so I'm not sure how good it works in
production.

Good luck with it,

/Alan


"Neal Walters" wrote:

> Hi Alan,
>
>    Your "The Bloggers Guide to BizTalk " is great.  You did a lot more tha
n
> I expected and I hope to contribute soon.
>
>    So, in your prototype to handle the two results sets - what did you do?
> The SQL adapter seems to build the SQLService.xsd for only the first SQL
> statement in the stored procedure.  I thought about building a second elem
ent
> with fields for the second SQL statement, but I didn't want to waste 30
> minutes if I knew it would not work.   Was this your approach?
>
>    So for example, suppose I want to retrieve all authors and all titles
> from the "pubs" demo database.  The schema that the SQL Adapter built look
s
> like this (I'm giving just the structure without closing tags or without f
ull
> xsd)
>
> <Schema>
>    <SQLTestReq>
>         <TwoResults>  <!-- this is the stored proc name -->
>   <SQLTestResp>
>         <authors>
>              <au_id>
>              <au_lname>
>
> Would I just change it to this:
> <Schema>
>    <SQLTestReq>
>         <TwoResults>  <!-- this is the stored proc name -->
>   <SQLTestResp>
>         <authors>
>              <au_id>
>              <au_lname>
>         <titles>
>               <title_id>
>               <title>
>
> Thanks again!
>
> Neal Walters
> http://Biztalk-Training.com - Free Biztalk 2004 Demos
>

[ Post a follow-up to this message ]

I got it to work.  Thanks!

Neal Walters
http://Biztalk-Training.com

[ Post a follow-up to this message ]