none
WCF-SQL table operation on tables with the same name. RRS feed

  • Question

  • Hello,

    My situation involves generating table operation schemas (Insert, Update, Delete, Select) for tables in two different SQL databases.  These tables happen to have the same name.  For the sake of discussion lets call the tables dbo.Customer.  However, these tables have different fields in them.  When generating the schemas to be used by BizTalk, they end up as the same message type like so:

    Database1: http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Customer#Update

    Database2: http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Customer#Update

    To my understanding, I can't simply change the namespaces or root nodes to be unique since this structure is expected by the SQL Adapter (shown here: https://msdn.microsoft.com/en-us/library/dd788023.aspx).  I had a couple thoughts on a solution: 

    1. Use schema versions to differentiate between the two different table schemas.
    2. Use a unique namespace and root node for the schemas.  Then, create a custom pipeline component to modify them to the standard as it is sent to SQL server.
    3. Modify the schema so support both tables with all columns in both tables.

    I don't really like any of these solutions, so I am hoping somebody in the community has run into this situation before and has something better.

    -Richard

    Friday, March 13, 2015 5:40 PM

Answers

  • You don't really have to do anything other than make sure the particular message gets routed always to the correct database.

    Having duplicate MessageType's is only a problem if you're relying on the automatic resolution of the XmlDisassembler and that would only be a concern on the Response side.

    To get around that, just create a custom Pipeline with the XmlDisassembler and set the Document Schemas list to only the one for that Port.

    • Marked as answer by Richard.Spice Monday, March 16, 2015 12:53 PM
    Friday, March 13, 2015 7:02 PM
  • Although you are correct that you should never have the same namespace/root name combination deployed more than once in the same BizTalk Group, you can do it in this case.

    On the send side, the Schemas are not really used, so you can easily deploy both of your Schemas and use them in Maps to map to the correct sql table with the corresponding correct columns.

    Morten la Cour

    • Marked as answer by Richard.Spice Monday, March 16, 2015 12:53 PM
    Friday, March 13, 2015 7:04 PM

All replies

  • You don't really have to do anything other than make sure the particular message gets routed always to the correct database.

    Having duplicate MessageType's is only a problem if you're relying on the automatic resolution of the XmlDisassembler and that would only be a concern on the Response side.

    To get around that, just create a custom Pipeline with the XmlDisassembler and set the Document Schemas list to only the one for that Port.

    • Marked as answer by Richard.Spice Monday, March 16, 2015 12:53 PM
    Friday, March 13, 2015 7:02 PM
  • Although you are correct that you should never have the same namespace/root name combination deployed more than once in the same BizTalk Group, you can do it in this case.

    On the send side, the Schemas are not really used, so you can easily deploy both of your Schemas and use them in Maps to map to the correct sql table with the corresponding correct columns.

    Morten la Cour

    • Marked as answer by Richard.Spice Monday, March 16, 2015 12:53 PM
    Friday, March 13, 2015 7:04 PM
  • Thank you very much for the feedback.  I will be moving forward with just deploying with the same namespace + root name.  
    Monday, March 16, 2015 12:56 PM