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

Question

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?

Answer 1

There is noting wrong with deploying Schemas with the same root+namespace and sometime, it's unavoidable as is this case.

1. Create two custom Pipelines, each with the XmlDisassembler.
2. In each XmlDisassembler, set the Document Specification to the one you expect to process on that port.
3. Deploy.
4. Set the Receive Pipeline to the custom Pipeline for that Location.

Answer 2

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)

Answer 3

For simple BizTalk scenario's, another approach would be to use the CompositeOperation-schema. This way you can avoid having to deploy the schema's from your stored procedures.

Within the "Composite"-schema, you can add all other supported SQL operations. The operation on your send port would be "CompositeOperation".

Your Composite-schema will look something like this: Composite Operation

For development purposes, you can still generate the schema for your stored procedure, as it wil help you to complete your XSLT, but you do no longer have to deploy it in BizTalk.

Also note that, this approach will not work when using the BizTalk Mapper instead of custom XSLT.