BizTalk WCF-SQL Adapter Selecting from a view

Question

i have the most annoying issue; i have an orchestration doing a lookup against a view. it works in locally and on our development server, but not in QA or UAT. same code. same views. just different environments.

to test this and be sure it's not a coding issue i take my local BizTalk, configure the port to Server A, fire a message and it works as expected. i then change the configuration of the send port to Server B (THAT'S ALL I CHANGE) and fire THE SAME message in, and it fails.

I get one of TWO errors for the same operation for the SAME view, so not even the error message is consistent.

Event Type: Warning
Event Source: BizTalk Server 2009
Event Category: (1)
Event ID: 5743
Date:  13/01/2010
Time:  16:53:07
User:  N/A
Computer: VM-RC-BTS2009
Description:
The adapter failed to transmit message going to send port "AX Lookup CRM_CUST" with URL "mssql://server//db?". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.XmlReaderGenerationException:  The columns BANKACCOUNTRECID and BLOCKED are either duplicated or not in a sequence. Each column can only be selected one time, and columns must be selected in sequence.
   at Microsoft.Adapters.Sql.SelectBodyWriter.OnWriteBodyContents(XmlDictionaryWriter writer)
   at System.ServiceModel.Channels.BodyWriter.WriteBodyContents(XmlDictionaryWriter writer)
   at System.ServiceModel.Channels.BodyWriterMessage.OnWriteBodyContents(XmlDictionaryWriter writer)
   at System.ServiceModel.Channels.Message.WriteBodyContents(XmlDictionaryWriter writer)
   at Microsoft.Adapters.AdapterUtilities.AdapterMessage.OnWriteBodyContents(XmlDictionaryWriter writer)
   at System.ServiceModel.Channels.Message.WriteBodyContents(XmlDictionaryWriter writer)
   at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfMarshaller.CreateBizTalkMessageStream(Message wcfMessage, IAdapterConfigInboundMessageMarshalling config)
   at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfMarshaller.CreateBizTalkMessage(IBaseMessageFactory messageFactory, IAdapterConfigInboundMessageMarshalling marshallingConfig, Message wcfMessage)
   at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)".

OR

Event Type: Warning
Event Source: BizTalk Server 2009
Event Category: (1)
Event ID: 5743
Date:  13/01/2010
Time:  16:45:49
User:  N/A
Computer: VM-RC-BTS2009.ad.integralgroup.co.nz
Description:
The adapter failed to transmit message going to send port "AX Lookup CRM_CUST" with URL "mssql://vm-lesmillsnzqa.aplplus.local//LMNZ_AX_Improve?". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.XmlReaderGenerationException:  The columns ACCOUNTNUM and BANKACCOUNTRECID are either duplicated or not in a sequence. Each column can only be selected one time, and columns must be selected in sequence.
   at Microsoft.Adapters.Sql.SelectBodyWriter.OnWriteBodyContents(XmlDictionaryWriter writer)
   at System.ServiceModel.Channels.BodyWriter.WriteBodyContents(XmlDictionaryWriter writer)
   at System.ServiceModel.Channels.BodyWriterMessage.OnWriteBodyContents(XmlDictionaryWriter writer)
   at System.ServiceModel.Channels.Message.WriteBodyContents(XmlDictionaryWriter writer)
   at Microsoft.Adapters.AdapterUtilities.AdapterMessage.OnWriteBodyContents(XmlDictionaryWriter writer)
   at System.ServiceModel.Channels.Message.WriteBodyContents(XmlDictionaryWriter writer)
   at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfMarshaller.CreateBizTalkMessageStream(Message wcfMessage, IAdapterConfigInboundMessageMarshalling config)
   at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfMarshaller.CreateBizTalkMessage(IBaseMessageFactory messageFactory, IAdapterConfigInboundMessageMarshalling marshallingConfig, Message wcfMessage)
   at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)".
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

i am asking for <Columns>*</Columns> and <Query>WHERE FIELD='xyz'</Query>

here's the actual message;

<ns0:Select xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/Types/Views/dbo" xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/ViewOp/dbo/CRM_CUST"&gt;
<ns0:Columns>*</ns0:Columns>
<ns0:Query>WHERE ACCOUNTNUM='id_0'</ns0:Query>
</ns0:Select> 

I then have a TwoWay Wcf-Custom Send port with sqlbinding. here's the config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <system.serviceModel>
    <client>
      <endpoint address="mssql://devserver//DbName?" behaviorConfiguration="EndpointBehavior" binding="sqlBinding" bindingConfiguration="sqlBinding" contract="BizTalk" name="CUST Lookup" />
    </client>
    <behaviors>
      <endpointBehaviors>
        <behavior name="EndpointBehavior" />
      </endpointBehaviors>
    </behaviors>
    <bindings>
      <sqlBinding>
        <binding name="sqlBinding" useAmbientTransaction="false" />
      </sqlBinding>
    </bindings>
  </system.serviceModel>
</configuration>

And this setup in the Action mapping;

ViewOp/Select/dbo/CRM_CUST -- name of the view

Answer 1

So; having given up on the Select from View operation which should be simple and suspecting there is a problem with the code for this particular part of the adapter i decided to try a "trick".

I created a stored procedure that simply does SELECT * FROM VIEW WHERE ID = @Param (same view that was causing issues earlier) where Param is the AccountNum i was passing in to the ViewOp criteria

Then used the adapter wizard to generate the schemas for TypeStoredProcedure operation instead of the ViewOp Changed the map to produce this new message Redploy And presto ... i can now happily switch between two environments without any errors!

this clearly says to me that there is a problem with the ViewOp portion of the SQL adapter!?!? Anybody got any other ideas / explanations as to why this would be happening other than a problem with the adapter?

I know most people say don't use Views and go to Stored Procedures instead, but there is a very strong reason why we are using Views. We're selecting against a Microsoft Dynamics AX database. AX publishes these Views for external systems to use. Creating a stored procedure against the AX schema is not supported by Microsoft as it changes their database. The same goes if we were using Views on CRM, we can't go creating stored procedures at will.

So the solution i have now might work, but it's not supported. It'll have to stay for now until we get this resolved though