Search code examples
c#.netbiztalkbiztalk-2013

How to solve BizTalk message type conflict when 2 procedures from 2 different databases share names?

I have two databases

  1. DB1
  2. DB2

I have a procedure dbo.Procedure in both of those databases.

I'm using "Consume Adapter Service" in order to execute those procedures from BizTalk.

On execution I'm getting error:

A response message sent to adapter "WCF-Custom" on Receive Location: xxx... Reason: Cannot locate document specification because multiple schemas matched the message type http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo#Procedure".

How can I solve this issue without renaming procedure in DB1 or DB2?

Solution

  • I would avoid having any schemas deployed with the same targetNamespace and root node name - but you can't do that if you must use the TypedProcedure type with the WCF-SQL/Custom adapter(s).

    Work arounds:

    • Don't use TypedProcedure on both if you're not actually getting typed resultsets back. Keep in mind that you can just use Procedure or XmlProcedure as appropriate, especially if you're not dealing with resultsets getting returned (e.g. if there's just a return code or if the proc is actually returning XML e.g. SELECT ... FOR XML ...). In fact, if you're returning XML from the proc(s), then you may be best off using XmlProcedure with a strongly typed schema for that result. If they're both just returning regular return values, I'd prefer doing...
    • If that doesn't work, create some wrapper procedure(s) for one or both. E.g. create a new proc on DB1 called bts.Db1Procedure (I like putting BizTalk specific procedures in a schema called bts so DBAs realize that's what it's for, but this could work with dbo just as well). Have this procedure take the same parameters and just pass them through to dbo.Procedure. Regenerate/modify your schema(s) to point to this procedure instead of the conflicting one.

    The thing to keep in mind is that the adapter uses the targetNamespace and the root node name to figure out

    1. the schema the proc is in
    2. the name of the procedure
    3. the shape of the resultset (generic, typed, or xml)