SQL Adapter - Generating the Schema from Stored Proc. for XML

 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 Int

 Set @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 = 1

 DECLARE @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

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

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

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.