title: BizTalk 2010 WCF SQL adapter compatibility with SQL Server 2014
body: <p>I am getting the error below whilst updating a port which uses a WCF SQL adapter that has always worked fine with SQL 2008 to a SQL 2014 DB with exactly the same (very simple) table schema.</p>
  
  <blockquote>
    <p>The adapter failed to transmit message going to send port "WcfSendPort_SqlAdapterBinding_TableOp_dbo_XXXXXXXX_Custom" with URL "mssql://XXXXX//XXXXXX?". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.XmlReaderGenerationException:  The columns CUST_ID and BUSINESS_UNIT_AR are either duplicated or not in a sequence. Each column can only be selected one time, and columns must be selected in sequence.</p>
  </blockquote>
  
  <p>I've tried the following with no luck</p>
  
  <ol>
  <li>Update the SELECT request object from * to the specific column names
  in order</li>
  <li>Regenerated the XSD schemas via the WCF wizard</li>
  <li>Installed the latest BT 2010 cumulative update packs for both the
  core installation and adapter packs</li>
  </ol>
  
  <p>I am coming to the conclusion that maybe the BizTalk 2010 WCF SQL adapter is not compatible with SQL Server 2014. Can anyone confirm this and/or suggest any workarounds? Currently I'm thinking I will need a custom c# component, and also plan a BizTalk upgrade at some point in the near future.</p>
  
  <p>Here is the table</p>
  
  <pre><code>CREATE TABLE [dbo].[PS_PAC_CUSCLI_XREF](
      [SETID] [nvarchar](5) NOT NULL,
      [CUST_ID] [nvarchar](15) NOT NULL,
      [BUSINESS_UNIT_AR] [nvarchar](5) NOT NULL,
      [PAC_M_CLIENTCD] [int] NOT NULL,
      [ADDRESS_SEQ_NUM] [int] NOT NULL,
      [PAC_M_ACCOUNT] [nvarchar](4) NOT NULL,
      [PAC_M_SUNCODE] [nvarchar](10) NOT NULL,
      [PAC_M_TAXCNTRY] [nvarchar](5) NOT NULL
  ) ON [PRIMARY]
  </code></pre>
  
  <p>I'm trying to execute a very simple select, here is the message</p>
  
  <pre><code>&lt;ns0:Select xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/PS_PAC_CUSCLI_XREF" xmlns:array="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo"&gt;
      &lt;ns0:Columns&gt;SETID, CUST_ID, BUSINESS_UNIT_AR, PAC_M_CLIENTCD, ADDRESS_SEQ_NUM, PAC_M_ACCOUNT, PAC_M_SUNCODE, PAC_M_TAXCNTRY&lt;/ns0:Columns&gt;
      &lt;ns0:Query&gt;WHERE [CUST_ID] = '0000005341' and [ADDRESS_SEQ_NUM] =     1&lt;/ns0:Query&gt;
  &lt;/ns0:Select&gt;
  </code></pre>
  
  <p>I can see it's using 2008 in the namespace, but that is how it is auto generated using the WCF import.</p>
  
  <p>The send port binding is pretty much the default as generated by the schema generation tool, but can add it if you really need it (it's quite big!).</p>
  
  <p>After running a SQL trace I confirmed that the select statement has the correct column order. However, this SQL was also run which seems to return the table schema in a different order.</p>
  
  <pre><code>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 &lt;&gt; -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], clmns.is_filestream 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(18),@ORIGINALSCHEMANAME nvarchar(3)',@ORIGINALOBJECTNAME=N'PS_PAC_CUSCLI_XREF',@ORIGINALSCHEMANAME=N'dbo'
  </code></pre>
  
  <p>Here is the order of columns returned from this query</p>
  
  <ul>
  <li>BUSINESS_UNIT_AR</li>
  <li>PAC_M_ACCOUNT</li>
  <li>PAC_M_SUNCODE</li>
  <li>SETID</li>
  <li>PAC_M_CLIENTCD</li>
  <li>ADDRESS_SEQ_NUM</li>
  <li>PAC_M_TAXCNTRY</li>
  <li>CUST_ID</li>
  </ul>
  
  <p>I also tried moving the data to temp table, dropping and recreating the table and then re-populating but the same error occurs. The order of columns returned in the statement above is different but still not the order I would expect. The same query on the SQL 2008 DB does return the data in the correct order so perhaps it is significant. Unfortunately it will be embedded into the source code of the WCF adapter so not sure there is anything I can do about it.</p>