Home Page › Forums › BizTalk 2004 – BizTalk 2010 › SQL Adapter – Generating the Schema from Stored Proc. for XML
- This topic has 3 replies, 1 voice, and was last updated 9 years, 10 months ago by
community-content.
-
AuthorPosts
-
-
May 6, 2008 at 3:02 PM #19570
We are getting the following error when trying to generate the schema from the SQL Adapter
Failed to execute SQL Statement. Please ensure that the supplied syntax is correct. New transaction cannot enlist in the specified transaction coordinator.
Looked at the MS support page http://support.microsoft.com/kb/917847
The stored proc that is being provided to us reads databases and their tables to gather all the required data, then generates XML output. I think the part about generating XML output is the problem. The table in the SQL DB “NewPartNumbers” has 3 columns, “XMLBuiltFlag”; “ITNBR”; and “XMLBuiltDate”. Part of the problem is I do not understand what is going on. My comfort zone is tables with data in them — this structure is dynamic, reaching into existing tables to extract data, create an XML file and then send it out to the world (to BizTalk in this case).
Performing this remotely on BizTalk 2006 r2 running on WinServer 03. The remote SQL DB is 2005 also running on WinServer 03.
The following is the stored proc.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go— =============================================
— Description: <Creating XML document for New Part Numbers>
— =============================================
ALTER PROCEDURE [dbo].[SP_888XML_NEWPARTNUMBER]
— Add the parameters for the stored procedure here
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;DECLARE @Date Varchar(50)
DECLARE @iRow Int
DECLARE @iCount IntSet @Date = GetDate()
DECLARE @Head TABLE
(
MessageID VarChar(10),
Date SmallDateTime,
MessageType VarChar(10)
)
INSERT INTO @Head
Select ‘ItemMaster’,GetDate(),’ItemMaster’DECLARE @Sender Table
(
SystemID VarChar(5),
CompanyID VarChar(3),
ReplyToQ VarChar(4)
)
INSERT INTO @Sender
Select ‘AS400′,’ABC’,’SREQ’DECLARE @Recipient Table
(
SystemID VarChar(5),
CompanyID VarChar(3),
ReplyToQ VarChar(4)
)
INSERT INTO @Recipient
Select ‘WH1′,’EXE’,’RSEQ’DECLARE @Body Table
(
ItemMaster VarChar(11)
)
INSERT INTO @Body(ItemMaster)
Values(‘ItemMaster’)
DECLARE @ItemMasterHeader TABLE
(
Whse VarChar(5),
WhseCode VarChar(5),
PickDate smalldatetime
)INSERT INTO @ItemMasterHeader
Select ‘Whs01′,’001’,GetDate()DECLARE @Table Table
(
RowID Int Identity(1,1),
PartNumber Varchar(15)
)
Insert into @Table
Select Itnbr From NewPartNumbers Where XMLBuiltFlag = 0
Select @iCount=Max(RowID) From @Table
SET @iRow = 1DECLARE @ITNBR Varchar(15)
DECLARE @UPC_Code Varchar(50)
DECLARE @ProductID Varchar(15)
DECLARE @Weight Varchar(10)
DECLARE @Height Varchar(10)
DECLARE @Width Varchar(10)
DECLARE @Length Varchar(10)
DECLARE @Sku Varchar(15)
DECLARE @Description Varchar(50)
DECLARE @ItemType Varchar(5)
DECLARE @ClassCode Varchar(5)DECLARE @ItemDetail Table
(
UPC_Code Varchar(50),
ProductID Varchar(15),
Weight Varchar(10),
Height Varchar(10),
Width Varchar(10),
Length Varchar(10),
Sku Varchar(15),
Description Varchar(50),
ItemType Varchar(5),
ClassCode Varchar(5)
)WHILE @iRow <= @iCount
BEGIN
Select @ITNBR=PartNumber From @Table Where RowID = @iRow
–Getting UPC Code
Select @ProductID=LTRIM(RTRIM(a.ITNBR)),@Description=LTRIM(RTRIM(a.ITDSC)),@ItemType=LTRIM(RTRIM(a.ITTYP)),@UPC_Code=LTRIM(RTRIM(b.UPC12))
From stdweb_data.dbo.Itemast as a left join stdweb_data.dbo.Barcodf as b on a.ITNBR = b.ITEM
Where a.ITNBR = @ITNBR
Set @Weight = ”
Set @Height = ”
Set @Width = ”
Set @Length = ”
Set @Sku = ”
Set @ClassCode = ”Insert into @ItemDetail Values (@UPC_Code,@ProductID,@Weight,@Height,@Width,@Length,@Sku,@Description,@ItemType,@ClassCode)
Set @iRow = @iRow + 1
END
Select 1 as Tag,
Null as Parent,
Head.MessageID as [Head!1!MessageID!element],
Head.Date as [Head!1!Date!element],
Head.MessageType as [Head!1!MessageType!element],
NULL as [Sender!2!SystemID!element],
NULL as [Sender!2!CompanyID!element],
NULL as [Sender!2!ReplyToQ!element],
NULL as [Recipient!3!SystemID!element],
NULL as [Recipient!3!CompanyID!element],
NULL as [Recipient!3!ReplyToQ!element],
NULL as [Body!4],
NULL as [ItemMaster!5],
NULL as [ItemMasterHeader!6!Whse!element],
NULL as [ItemMasterHeader!6!WhseCode!element],
NULL as [ItemMasterHeader!6!PickDate!element],
NULL as [Items!7],
NULL as [ItemDetail!8!UPC_Code!element],
NULL as [ItemDetail!8!ProductID!element],
NULL as [ItemDetail!8!Weight!element],
NULL as [ItemDetail!8!Height!element],
NULL as [ItemDetail!8!Width!element],
NULL as [ItemDetail!8!Length!element],
NULL as [ItemDetail!8!Sku!element],
NULL as [ItemDetail!8!Description!element],
NULL as [ItemDetail!8!ItemType!element],
NULL as [ItemDetail!8!ClassCode!element]
From @Head as Head
UNION ALL
Select 2 as tag,
1 as Parent,
Null as [Head!1!MessageID!element],
Null as [Head!1!Date!element],
Null as [Head!1!MessageType!element],
Sender.SystemID as [Sender!2!SystemID!element],
Sender.CompanyID as [Sender!2!CompanyID!element],
Sender.ReplyToQ as [Sender!2!ReplyToQ!element],
NULL as [Recipient!3!SystemID!element],
NULL as [Recipient!3!CompanyID!element],
NULL as [Recipient!3!ReplyToQ!element],
NULL as [Body!4],
NULL as [ItemMaster!5],
NULL as [ItemMasterHeader!6!Whse!element],
NULL as [ItemMasterHeader!6!WhseCode!element],
NULL as [ItemMasterHeader!6!PickDate!element],
NULL as [Items!7],
NULL as [ItemDetail!8!UPC_Code!element],
NULL as [ItemDetail!8!ProductID!element],
NULL as [ItemDetail!8!Weight!element],
NULL as [ItemDetail!8!Height!element],
NULL as [ItemDetail!8!Width!element],
NULL as [ItemDetail!8!Length!element],
NULL as [ItemDetail!8!Sku!element],
NULL as [ItemDetail!8!Description!element],
NULL as [ItemDetail!8!ItemType!element],
NULL as [ItemDetail!8!ClassCode!element]
From @Sender as Sender
UNION ALL
Select 3 as tag,
1 as Parent,
Null as [Head!1!MessageID!element],
Null as [Head!1!Date!element],
Null as [Head!1!MessageType!element],
NULL as [Sender!2!SystemID!element],
NULL as [Sender!2!CompanyID!element],
NULL as [Sender!2!ReplyToQ!element],
Recipient.SystemID as [Recipient!3!SystemID!element],
Recipient.CompanyID as [Recipient!3!CompanyID!element],
Recipient.ReplyToQ as [Recipient!3!ReplyToQ!element],
NULL as [Body!4],
NULL as [ItemMaster!5],
NULL as [ItemMasterHeader!6!Whse!element],
NULL as [ItemMasterHeader!6!WhseCode!element],
NULL as [ItemMasterHeader!6!PickDate!element],
NULL as [Items!7],
NULL as [ItemDetail!8!UPC_Code!element],
NULL as [ItemDetail!8!ProductID!element],
NULL as [ItemDetail!8!Weight!element],
NULL as [ItemDetail!8!Height!element],
NULL as [ItemDetail!8!Width!element],
NULL as [ItemDetail!8!Length!element],
NULL as [ItemDetail!8!Sku!element],
NULL as [ItemDetail!8!Description!element],
NULL as [ItemDetail!8!ItemType!element],
NULL as [ItemDetail!8!ClassCode!element]
From @Recipient as Recipient
UNION ALL
Select 4 as Tag,
Null as Parent,
Null as [Head!1!MessageID!element],
Null as [Head!1!Date!element],
Null as [Head!1!MessageType!element],
NULL as [Sender!2!SystemID!element],
NULL as [Sender!2!CompanyID!element],
NULL as [Sender!2!ReplyToQ!element],
NULL as [Recipient!3!SystemID!element],
NULL as [Recipient!3!CompanyID!element],
NULL as [Recipient!3!ReplyToQ!element],
” as [Body!4],
NULL as [ItemMaster!5],
NULL as [ItemMasterHeader!6!Whse!element],
NULL as [ItemMasterHeader!6!WhseCode!element],
NULL as [ItemMasterHeader!6!PickDate!element],
NULL as [Items!7],
NULL as [ItemDetail!8!UPC_Code!element],
NULL as [ItemDetail!8!ProductID!element],
NULL as [ItemDetail!8!Weight!element],
NULL as [ItemDetail!8!Height!element],
NULL as [ItemDetail!8!Width!element],
NULL as [ItemDetail!8!Length!element],
NULL as [ItemDetail!8!Sku!element],
NULL as [ItemDetail!8!Description!element],
NULL as [ItemDetail!8!ItemType!element],
NULL as [ItemDetail!8!ClassCode!element]
UNION ALL
Select 5 as Tag,
4 as Parent,
Null as [Head!1!MessageID!element],
Null as [Head!1!Date!element],
Null as [Head!1!MessageType!element],
NULL as [Sender!2!SystemID!element],
NULL as [Sender!2!CompanyID!element],
NULL as [Sender!2!ReplyToQ!element],
NULL as [Recipient!3!SystemID!element],
NULL as [Recipient!3!CompanyID!element],
NULL as [Recipient!3!ReplyToQ!element],
Null as [Body!4],
” as [ItemMaster!5],
NULL as [ItemMasterHeader!6!Whse!element],
NULL as [ItemMasterHeader!6!WhseCode!element],
NULL as [ItemMasterHeader!6!PickDate!element],
NULL as [Items!7],
NULL as [ItemDetail!8!UPC_Code!element],
NULL as [ItemDetail!8!ProductID!element],
NULL as [ItemDetail!8!Weight!element],
NULL as [ItemDetail!8!Height!element],
NULL as [ItemDetail!8!Width!element],
NULL as [ItemDetail!8!Length!element],
NULL as [ItemDetail!8!Sku!element],
NULL as [ItemDetail!8!Description!element],
NULL as [ItemDetail!8!ItemType!element],
NULL as [ItemDetail!8!ClassCode!element]
UNION ALL
Select 6 as Tag,
5 as Parent,
Null as [Head!1!MessageID!element],
Null as [Head!1!Date!element],
Null as [Head!1!MessageType!element],
NULL as [Sender!2!SystemID!element],
NULL as [Sender!2!CompanyID!element],
NULL as [Sender!2!ReplyToQ!element],
NULL as [Recipient!3!SystemID!element],
NULL as [Recipient!3!CompanyID!element],
NULL as [Recipient!3!ReplyToQ!element],
Null as [Body!4],
NULL as [ItemMaster!5],
ItemMasterHeader.Whse as [ItemMasterHeader!6!Whse!element],
ItemMasterHeader.WhseCode as [ItemMasterHeader!6!WhseCode!element],
ItemMasterHeader.PickDate as [ItemMasterHeader!6!PickDate!element],
NULL as [Items!7],
NULL as [ItemDetail!8!UPC_Code!element],
NULL as [ItemDetail!8!ProductID!element],
NULL as [ItemDetail!8!Weight!element],
NULL as [ItemDetail!8!Height!element],
NULL as [ItemDetail!8!Width!element],
NULL as [ItemDetail!8!Length!element],
NULL as [ItemDetail!8!Sku!element],
NULL as [ItemDetail!8!Description!element],
NULL as [ItemDetail!8!ItemType!element],
NULL as [ItemDetail!8!ClassCode!element]
From @ItemMasterHeader as ItemMasterHeader
UNION ALL
Select 7 as Tag,
5 as Parent,
Null as [Head!1!MessageID!element],
Null as [Head!1!Date!element],
Null as [Head!1!MessageType!element],
NULL as [Sender!2!SystemID!element],
NULL as [Sender!2!CompanyID!element],
NULL as [Sender!2!ReplyToQ!element],
NULL as [Recipient!3!SystemID!element],
NULL as [Recipient!3!CompanyID!element],
NULL as [Recipient!3!ReplyToQ!element],
Null as [Body!4],
NULL as [ItemMaster!5],
NULL as [ItemMasterHeader!6!Whse!element],
NULL as [ItemMasterHeader!6!WhseCode!element],
NULL as [ItemMasterHeader!6!PickDate!element],
” as [Items!7],
NULL as [ItemDetail!8!UPC_Code!element],
NULL as [ItemDetail!8!ProductID!element],
NULL as [ItemDetail!8!Weight!element],
NULL as [ItemDetail!8!Height!element],
NULL as [ItemDetail!8!Width!element],
NULL as [ItemDetail!8!Length!element],
NULL as [ItemDetail!8!Sku!element],
NULL as [ItemDetail!8!Description!element],
NULL as [ItemDetail!8!ItemType!element],
NULL as [ItemDetail!8!ClassCode!element]
UNION ALL
Select 8 as Tag,
7 as Parent,
Null as [Head!1!MessageID!element],
Null as [Head!1!Date!element],
Null as [Head!1!MessageType!element],
NULL as [Sender!2!SystemID!element],
NULL as [Sender!2!CompanyID!element],
NULL as [Sender!2!ReplyToQ!element],
NULL as [Recipient!3!SystemID!element],
NULL as [Recipient!3!CompanyID!element],
NULL as [Recipient!3!ReplyToQ!element],
Null as [Body!4],
NULL as [ItemMaster!5],
NULL as [ItemMasterHeader!6!Whse!element],
NULL as [ItemMasterHeader!6!WhseCode!element],
NULL as [ItemMasterHeader!6!PickDate!element],
NULL as [Items!7],
ItemDetail.UPC_Code as [ItemDetail!8!UPC_Code!element],
ItemDetail.ProductID as [ItemDetail!8!ProductID!element],
ItemDetail.Weight as [ItemDetail!8!Weight!element],
ItemDetail.Height as [ItemDetail!8!Height!element],
ItemDetail.Width as [ItemDetail!8!Width!element],
ItemDetail.Length as [ItemDetail!8!Length!element],
ItemDetail.Sku as [ItemDetail!8!Sku!element],
ItemDetail.Description as [ItemDetail!8!Description!element],
ItemDetail.ItemType as [ItemDetail!8!ItemType!element],
ItemDetail.ClassCode as [ItemDetail!8!ClassCode!element]
FROM @ItemDetail as ItemDetail
FOR XML Explicit,ROOT(‘Message’)–Select @SetHeader
/* Select ItemMasterHeader.WHSE
For XML Auto,ELEMENTS
*/
–Select @SetHeader
/* Select OrderHeader.ExternOrderKey,’OrderDate’=dbo.UDF_FormatDate(OrderHeader.OrderDate),’DeliveryDate’=dbo.UDF_FormatDate(OrderHeader.DeliveryDate),OrderHeader.Priority,OrderHeader.C_Company,OrderHeader.C_Address1,OrderHeader.C_Address2,OrderHeader.C_Address3,OrderHeader.C_Address4,OrderHeader.C_City,OrderHeader.C_State,OrderHeader.C_Zip,OrderHeader.BuyerPO,OrderHeader.IntermodalVehicle,OrderHeader.Type,OrderHeader.Notes, OrderHeader.Ordergroup,OrderHeader.Door,
OrderDetail.ExternOrderKey , OrderDetail.ExternLineNo, OrderDetail.Sku, OrderDetail.OpenQty, OrderDetail.SUSR1, OrderDetail.SUSR2
From Order_Header OrderHeader Join Order_Detail OrderDetail On OrderHeader.ExternOrderKey = OrderDetail.ExternOrderKey
Where OrderHeader.ExternOrderKey = @OrderKey
and (OrderHeader.ACD = ‘C’ or OrderHeader.ACD = ‘A’)
Order By OrderHeader.ExternOrderKey,OrderDetail.ExternLineNo
For XML Auto,ELEMENTS
*/
END -
May 6, 2008 at 11:43 PM #19577
Hello JLingenfelder,
the most obvious problem is the missing XMLDATA-Part. So “For XML Auto, XMLDATA” should make it work. After adding the metadata, before running your orchestration alter the procedure and delete the “, XMLDATA”. Otherwise the schema wil be delivered with your data – at least that’s how I interpreted it when I had this problem.
To make the “ELEMENTS”-part work you seem to have to do a few things. I for my part just mapped the whole thing to a schema of my liking.
Regards, Michael
-
May 7, 2008 at 2:28 AM #19578
The SQL Add Generated Items requires the stored proc to return a schema. The XMLDATA instruction will return an XDR schema which the adapter converts inyo an XSD schema. You only need the XMLDATA instruction when using Add Generated Items you can remove it once the schema has been created
You may have a problem using FOR XML EXPLICIT, XMLDATA. From memory I don’t think this works that well
However you can create a schema without using the Add Generated Items.
Your messages will look something like this:
<ns0:SQLRequest xmlns:ns0=”urn:your:namespace”>
<SP_888XML_NEWPARTNUMBER>
</ns0:SQLRequest><ns0:SQLResponse xmlns:ns0=”urn:your:namespace”>
<Message>
<Head>
<MessageID/>
<Date/>
<MessageType/>
</Head>
<Sender>
<SystemID>
<CompanyID>
<ReplyToQ>
</Sender>
<Recipient>
<SystemID>
<CompanyID>
<ReplyToQ>
</Recipient>
<Body>
<ItemMaster>
<ItemMasterHeader>
<Whse>
<WhseCode>
<PickDate>
</ItemMasterHeader>
</ItemMaster>
<Items>
<ItemDetail>
<UPC_Code>
<ProductID>
<Weight>
<Height>
<Width>
<Length>
<Sku>
<Description>
<ItemType>
<ClassCode>
</ItemDetail>
</Items>
</Body>
<Message>
</ns0:SQLResponse>You could generate the schema manually, its not that complicated
An easier way to generate the schema is to run the stored proc and use the resultant Xml to generate the schema, then add the root nodes and namespace that the SQL adapter needs. The root node name and namespace can be anything you choose. On Send the SQL adapter ignores the root node and you specify the response root node and name space on the Send Port.
You may need to adjust the data types to match the SQL data types
-
-
May 11, 2008 at 1:08 PM #19610
Thank you for the replies. Turned out the problem was not with the SQL Stored Proc. Used “DTC Ping” to check the communication between the servers and found the the target server with SQL database did not resolve the server name on which BizTalk was running. We corrected this and Biztalk was able to generate the schema then.
However, the comments about the way the stored proc was structure was very helpful in tracking down a subsequent problem.
Long and short is that the replies did help me to understand that a stored proc that generated an XML file was valid for using with a SQL Adapter. BIG THANKS for helping me out there.
-
-
AuthorPosts
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.