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

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › SQL Adapter – Generating the Schema from Stored Proc. for XML

Viewing 2 reply threads
  • Author
    Posts
    • #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 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

       

    • #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

       

      • #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

    • #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.

Viewing 2 reply threads
  • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.