|
Home > Archive > BizTalk Server General > July 2004 > sql adapter not returning success message
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 adapter not returning success message
|
|
| Rebecca 2004-06-30, 5:55 pm |
| I created a project that uses a flat file disassembler in the receive pipeline and then updates a sql db. The sp does update the db, but it doesn't successfully populate the 'response' message that is supposed to come back.
Just let me know what other info you need to point me in the right direction.
Here's my schema for the sql part:
<?xml version="1.0" encoding="utf-16" ?>
- <xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://InvoiceToSQL" version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema">
- <xs:annotation>
- <xs:appinfo>
<msbtssql:sqlScript value="exec [usp_InsertInvoice] @pCustomerId="123", @pInvDesc="InvDesc", @pInvoiceId="456"" xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003" />
</xs:appinfo>
</xs:annotation>
- <xs:element name="InvRequestRoot">
- <xs:complexType>
- <xs:sequence>
- <xs:element name="usp_InsertInvoice">
- <xs:complexType>
<xs:attribute name="pCustomerId" type="xs:string" />
<xs:attribute name="pInvDesc" type="xs:string" />
<xs:attribute name="pInvoiceId" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
- <xs:element name="InvResponseRoot">
- <xs:complexType>
- <xs:sequence>
<xs:element xmlns:q1="http://InvoiceToSQL" minOccurs="0" maxOccurs="unbounded" name="tblInvoice" type="q1:tblInvoiceType" />
</xs:sequence>
</xs:complexType>
</xs:element>
- <xs:complexType name="tblInvoiceType">
<xs:attribute name="InvoiceID" type="xs:string" />
<xs:attribute name="CustomerID" type="xs:string" />
<xs:attribute name="InvDesc" type="xs:string" />
</xs:complexType>
</xs:schema>
Here's my sp:
CREATE PROCEDURE usp_InsertInvoice
@pInvoiceId as nvarchar(10),
@pCustomerId as nvarchar(10),
@pInvDesc as nvarchar( 50 )
AS
INSERT INTO tblInvoice
( InvoiceID, CustomerID, InvDesc)
VALUES( @pInvoiceId, @pCustomerId, @pInvDesc )
/* Finally we return the Loans row we created
remember xmldata only needed when deriving schema for the adapter */
select * from tblInvoice for xml auto
GO
Here's my error from HAT:
There was a failure executing the receive pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLReceive" Source: "XML disassembler" Receive Location: "SQL://localhost/EmailInvoices/" Reason: The disassembler cannot retrieve the document specification by using
this type: "http://Microsoft.BTS2004.Sample.SQL_Adapter#InvResponseRoot". Either the schema is not deployed correctly, or more than one schema is deployed for the same message type.
--
Thanks,
Rebecca
| |
| larry franks 2004-07-01, 7:47 am |
| Hi Rebecca, from the error you are receiving the port that you are using to
submit data to SQL is most likely configured incorrectly.
When you configure the two-way send port to SQL, in the SQL Transport
properties dialog you have to specify a Document Target Namespace and a
Response Root Element. These need to exactly match the target namespace of
your schema and the root element for the response document. BizTalk uses
the target namespace and root element of a document to select a schema from
the deployed assemblies, in this case it's not finding one that matches the
namespace#root combination of the inbound document. Since we are pulling
the info from SQL, the SQL send port specifies which namespace and root
element to apply to the data before it's submitted to BizTalk.
Based on the schema you posted the namespace in the send port should be set
to http://InvoiceToSQL and the namespace to InvResponseRoot.
From the error you are receiving it looks like the target namespace is
specified incorrectly in the port. Can you change this and see if this
resolves the problem you are seeing?
Larry Franks
This posting is provided "AS IS" with no warranties,and confers no rights.
Subscribe at
http://support.microsoft.com/defaul...msdn/nospam.asp
&SD=msdn
--------------------
| Thread-Topic: sql adapter not returning success message
| thread-index: AcRe34pdOMX9puQGRDSLl0QjYK+u7g==
| X-WBNR-Posting-Host: 65.248.165.108
| From: "=?Utf-8?B?UmViZWNjYQ==?=" <rclarke@community.nospam>
| Subject: sql adapter not returning success message
| Date: Wed, 30 Jun 2004 13:19:26 -0700
| Lines: 58
| Message-ID: <A87E970D-EDE5-413F-95D8-828BF53744B8@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.biztalk.general
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 127.0.0.1
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGXA0
1.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.biztalk.general:16647
| X-Tomcat-NG: microsoft.public.biztalk.general
|
| I created a project that uses a flat file disassembler in the receive
pipeline and then updates a sql db. The sp does update the db, but it
doesn't successfully populate the 'response' message that is supposed to
come back.
|
| Just let me know what other info you need to point me in the right
direction.
| Here's my schema for the sql part:
| <?xml version="1.0" encoding="utf-16" ?>
| - <xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003"
attributeFormDefault="unqualified" elementFormDefault="qualified"
targetNamespace="http://InvoiceToSQL" version="1.0"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
| - <xs:annotation>
| - <xs:appinfo>
| <msbtssql:sqlScript value="exec [usp_InsertInvoice] @pCustomerId="123",
@pInvDesc="InvDesc", @pInvoiceId="456""
xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003" />
| </xs:appinfo>
| </xs:annotation>
| - <xs:element name="InvRequestRoot">
| - <xs:complexType>
| - <xs:sequence>
| - <xs:element name="usp_InsertInvoice">
| - <xs:complexType>
| <xs:attribute name="pCustomerId" type="xs:string" />
| <xs:attribute name="pInvDesc" type="xs:string" />
| <xs:attribute name="pInvoiceId" type="xs:string" />
| </xs:complexType>
| </xs:element>
| </xs:sequence>
| </xs:complexType>
| </xs:element>
| - <xs:element name="InvResponseRoot">
| - <xs:complexType>
| - <xs:sequence>
| <xs:element xmlns:q1="http://InvoiceToSQL" minOccurs="0"
maxOccurs="unbounded" name="tblInvoice" type="q1:tblInvoiceType" />
| </xs:sequence>
| </xs:complexType>
| </xs:element>
| - <xs:complexType name="tblInvoiceType">
| <xs:attribute name="InvoiceID" type="xs:string" />
| <xs:attribute name="CustomerID" type="xs:string" />
| <xs:attribute name="InvDesc" type="xs:string" />
| </xs:complexType>
| </xs:schema>
| Here's my sp:
| CREATE PROCEDURE usp_InsertInvoice
| @pInvoiceId as nvarchar(10),
| @pCustomerId as nvarchar(10),
| @pInvDesc as nvarchar( 50 )
| AS
|
| INSERT INTO tblInvoice
| ( InvoiceID, CustomerID, InvDesc)
| VALUES( @pInvoiceId, @pCustomerId, @pInvDesc )
|
| /* Finally we return the Loans row we created
| remember xmldata only needed when deriving schema for the adapter */
| select * from tblInvoice for xml auto
| GO
|
| Here's my error from HAT:
| There was a failure executing the receive pipeline:
"Microsoft.BizTalk.DefaultPipelines.XMLReceive" Source: "XML disassembler"
Receive Location: "SQL://localhost/EmailInvoices/" Reason: The disassembler
cannot retrieve the document specification by using this type:
"http://Microsoft.BTS2004.Sample.SQL_Adapter#InvResponseRoot". Either the
schema is not deployed correctly, or more than one schema is deployed for
the same message type.
| --
| Thanks,
| Rebecca
|
|
|
|
|
|