|
Home > Archive > BizTalk Server Applications Integration > April 2005 > SQL Server Stored Procedure call
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL Server Stored Procedure call
|
|
| Stuart Grover 2005-04-17, 2:47 am |
| I'm having all kinds of trouble trying to return the
results of a stored procedure to an orchestration. I've
tried using all the samples as guides and read a large
amount of info but to no avail.
Here's the error:
Microsoft.XLANGs.Core.UnexpectedMessageTypeException:
Received unexpected message type '' does not match
expected type 'http://MyFirstOrchestration#ResponseRoots'.
at
Microsoft.BizTalk.XLANGs.BTXEngine.BTXPortBase.VerifyTrans
port(Envelope env, Int32 operationId, Context ctx)
at Microsoft.XLANGs.Core.Subscription.Receive(Segment
s, Context ctx, Envelope& env, Boolean topOnly)
at Microsoft.XLANGs.Core.PortBase.GetMessageId
(Subscription subscription, Segment currentSegment,
Context cxt, Envelope& env, CachedObject location)
at MyFirstOrchestration.EmployeeOrchestration.segment1
(StopConditions stopOn)
at Microsoft.XLANGs.Core.SegmentScheduler.RunASegment
(Segment s, StopConditions stopCond, Exception& exp)
Microsoft.XLANGs.Core.UnexpectedMessageTypeException
Here's the schema:
<?xml version="1.0" encoding="utf-16" ?>
- <xs:schema
xmlns:b="http://schemas.microsoft.com/BizTalk/2003"
attributeFormDefault="unqualified"
elementFormDefault="qualified"
targetNamespace="http://MyFirstOrchestration"
version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema">
- <xs:annotation>
- <xs:appinfo>
<msbtssql:sqlScript value="exec [ValidateToken]
@GymID=NULL, @Token=NULL"
xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003"
/>
</xs:appinfo>
</xs:annotation>
- <xs:element name="RequestRoot">
- <xs:complexType>
- <xs:sequence>
- <xs:element name="ValidateToken">
- <xs:complexType>
<xs:attribute name="GymID" type="xs:int" />
<xs:attribute name="Token" type="xs:int" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
- <xs:element name="ResponseRoots">
- <xs:complexType>
- <xs:sequence>
<xs:element xmlns:q1="http://MyFirstOrchestration"
minOccurs="0" maxOccurs="unbounded" name="products"
type="q1:LoansType" />
</xs:sequence>
</xs:complexType>
</xs:element>
- <xs:complexType name="LoansType">
<xs:attribute name="productID" type="xs:string" />
</xs:complexType>
</xs:schema>
Here's the stored Procedure:
CREATE PROCEDURE [dbo].[ValidateToken] @GymID int, @Token
int AS
Select top 1 productID from products FOR XML AUTO, XMLDATA
GO
------------------------------------------------
As you can see i've dumbed it right down but still no
joy... Any pointers would be much appreciated
Thanks Stu
P.S. I would have thought that intetgrating with SQL
would be far easier than it is. I hope BTS2006 has some
major improvements!
| |
| Matt Barbour 2005-04-21, 5:49 pm |
| Stuart, I am going to guess you are using the bind later approach when you
set up your port.
Please check the following...
When you created the adapter ( via the add generated items ), On the 3ed
screen there is a field called "Target namespace", in it you need to put
something like http://VaildateToken.
Choose Send Port as you are getting a Response back from it...
Then you need to assign a Request Root like : VaildateTokenReq
Then you need to assign a Response Root like : ValidateTokenResp
***Note these names and namespace down .. you will need them later. ***
The names are just names I am using to help keep things straight, you can
actually use whatever you want...
Then go though and completed the adapter generation.
In the biztalk explorer, drill down to SendPorts and add a send port.
Choose Static Solicit-Response Port.
Transport Type : SQL
Address -- : Open that up and plug in your connection string.
The Document Target Namespace will then be : http://VaildateToken
**This is the same value you gave to the Add Adapter wizard. **
The Response Root Element Name will be : ValidateTokenResp
**This is the same value you gave to the Add Adapter wizard. **
Complete though the setup as normal and give it a go.
That should solve the Microsoft.XLANGs.Core.UnexpectedMessageTypeException:
Received unexpected message type '' does not match
expected type 'http://MyFirstOrchestration#ResponseRoots' Error.
Hope that helps out..
Matt.
"Stuart Grover" wrote:
> I'm having all kinds of trouble trying to return the
> results of a stored procedure to an orchestration. I've
> tried using all the samples as guides and read a large
> amount of info but to no avail.
>
> Here's the error:
> Microsoft.XLANGs.Core.UnexpectedMessageTypeException:
> Received unexpected message type '' does not match
> expected type 'http://MyFirstOrchestration#ResponseRoots'.
> at
> Microsoft.BizTalk.XLANGs.BTXEngine.BTXPortBase.VerifyTrans
> port(Envelope env, Int32 operationId, Context ctx)
> at Microsoft.XLANGs.Core.Subscription.Receive(Segment
> s, Context ctx, Envelope& env, Boolean topOnly)
> at Microsoft.XLANGs.Core.PortBase.GetMessageId
> (Subscription subscription, Segment currentSegment,
> Context cxt, Envelope& env, CachedObject location)
> at MyFirstOrchestration.EmployeeOrchestration.segment1
> (StopConditions stopOn)
> at Microsoft.XLANGs.Core.SegmentScheduler.RunASegment
> (Segment s, StopConditions stopCond, Exception& exp)
> Microsoft.XLANGs.Core.UnexpectedMessageTypeException
>
> Here's the schema:
> <?xml version="1.0" encoding="utf-16" ?>
> - <xs:schema
> xmlns:b="http://schemas.microsoft.com/BizTalk/2003"
> attributeFormDefault="unqualified"
> elementFormDefault="qualified"
> targetNamespace="http://MyFirstOrchestration"
> version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema">
> - <xs:annotation>
> - <xs:appinfo>
> <msbtssql:sqlScript value="exec [ValidateToken]
> @GymID=NULL, @Token=NULL"
> xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003"
> />
> </xs:appinfo>
> </xs:annotation>
> - <xs:element name="RequestRoot">
> - <xs:complexType>
> - <xs:sequence>
> - <xs:element name="ValidateToken">
> - <xs:complexType>
> <xs:attribute name="GymID" type="xs:int" />
> <xs:attribute name="Token" type="xs:int" />
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> - <xs:element name="ResponseRoots">
> - <xs:complexType>
> - <xs:sequence>
> <xs:element xmlns:q1="http://MyFirstOrchestration"
> minOccurs="0" maxOccurs="unbounded" name="products"
> type="q1:LoansType" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> - <xs:complexType name="LoansType">
> <xs:attribute name="productID" type="xs:string" />
> </xs:complexType>
> </xs:schema>
>
>
> Here's the stored Procedure:
> CREATE PROCEDURE [dbo].[ValidateToken] @GymID int, @Token
> int AS
> Select top 1 productID from products FOR XML AUTO, XMLDATA
> GO
> ------------------------------------------------
> As you can see i've dumbed it right down but still no
> joy... Any pointers would be much appreciated
>
> Thanks Stu
>
> P.S. I would have thought that intetgrating with SQL
> would be far easier than it is. I hope BTS2006 has some
> major improvements!
>
|
|
|
|
|