BizTalk WCF-SQL Adapter Selecting from a view

问题:

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">
<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

回答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

回答2:

I met the same problem recently. After spending a day I finally started an SQL Profiler and found a cause. For some reason in some DBs, including Ax2009 DB, there is a difference in column order listed in view and column order that biztalk wants. It executes the following code and wants the columns to be in exact order ('*' won't work for you):

exec sp_executesql N'SELECT sp.type AS [ObjectType], modify_date AS [LastModified] FROM sys.all_objects AS sp WHERE (sp.name=@ORIGINALOBJECTNAME and SCHEMA_NAME(sp.schema_id)=@ORIGINALSCHEMANAME);SELECT clmns.name AS [Name], usrt.name AS [DataType], SCHEMA_NAME(usrt.schema_id) AS DataTypeSchemaName, usrt.is_assembly_type AS [IsAssemblyType], clmns.is_identity AS [IsIdentity], ISNULL(baset.name, N'''') AS [SystemType], CAST(CASE WHEN baset.name IN (N''nchar'', N''nvarchar'') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length], CAST(clmns.[precision] AS int) AS [NumericPrecision], CAST(clmns.[scale] AS int) AS [NumericScale], clmns.is_nullable as [IsNullable], clmns.is_computed as [IsComputed], 0 as [IsFileStream], AT.assembly_qualified_name AS AssemblyQualifiedName, defCst.definition AS [DefaultValue] FROM sys.columns as clmns LEFT OUTER JOIN sys.default_constraints defCst on defCst.parent_object_id = clmns.object_id and defCst.parent_column_id = clmns.column_id LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id LEFT JOIN sys.assembly_types AT ON AT.[name] = usrt.name AND AT.schema_id = usrt.schema_id WHERE clmns.object_id = (SELECT object_id FROM sys.objects o WHERE o.name=@ORIGINALOBJECTNAME and SCHEMA_NAME(o.schema_id)=@ORIGINALSCHEMANAME)',N'@ORIGINALOBJECTNAME nvarchar(13),@ORIGINALSCHEMANAME nvarchar(3)',@ORIGINALOBJECTNAME=N'CRM_CFU',@ORIGINALSCHEMANAME=N'dbo'

Just replace @ORIGINALOBJECTNAME value with your view name, and put columns in your select in the exact order.