BizTalk WCF-SQL适配器从视图中选择 [英] BizTalk WCF-SQL Adapter Selecting from a view

问题描述

我有最烦人的问题

我有一个业务流程针对视图进行查找. 它可以在本地和我们的开发服务器上使用，但不能在质量检查或UAT中使用. 相同的代码.相同的看法.只是不同的环境.

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.

要对此进行测试，并确保它不是编码问题，我将使用本地BizTalk，将端口配置为服务器A，触发一条消息，然后按预期运行. 然后，我更改发送到服务器B的发送端口的配置(这是我所做的全部更改)，并在其中发送相同的消息，但失败.

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

或

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.

我要的是<Columns>*</Columns>和<Query>WHERE FIELD='xyz'</Query>

这是实际消息；

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

然后我有了一个sqlbinding的TwoWay Wcf-Custom Send端口.这是配置

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>

然后在Action映射中进行此设置；

And this setup in the Action mapping;

ViewOp/Select/dbo/CRM_CUST-视图名称

推荐答案

我最近遇到了同样的问题.花了一天的时间后，我终于启动了SQL事件探查器，并找到了原因.由于某些原因，在某些数据库(包括Ax2009 DB)中，视图列出的列顺序与biztalk希望的列顺序有所不同.它执行以下代码，并希望各列的顺序正确('*'对您不起作用):

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'

只需将@ORIGINALOBJECTNAME值替换为您的视图名称，然后将列按准确的顺序放在选择的列中即可.

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

这篇关于BizTalk WCF-SQL适配器从视图中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！