2

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.

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.

I've tried the following with no luck

  1. Update the SELECT request object from * to the specific column names in order
  2. Regenerated the XSD schemas via the WCF wizard
  3. Installed the latest BT 2010 cumulative update packs for both the core installation and adapter packs

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.

Here is the table

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]

I'm trying to execute a very simple select, here is the message

<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">
    <ns0:Columns>SETID, CUST_ID, BUSINESS_UNIT_AR, PAC_M_CLIENTCD, ADDRESS_SEQ_NUM, PAC_M_ACCOUNT, PAC_M_SUNCODE, PAC_M_TAXCNTRY</ns0:Columns>
    <ns0:Query>WHERE [CUST_ID] = '0000005341' and [ADDRESS_SEQ_NUM] =     1</ns0:Query>
</ns0:Select>

I can see it's using 2008 in the namespace, but that is how it is auto generated using the WCF import.

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

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.

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], 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'

Here is the order of columns returned from this query

  • BUSINESS_UNIT_AR
  • PAC_M_ACCOUNT
  • PAC_M_SUNCODE
  • SETID
  • PAC_M_CLIENTCD
  • ADDRESS_SEQ_NUM
  • PAC_M_TAXCNTRY
  • CUST_ID

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.

Stuart Charles
  • 21
  • 2
  • 1
    What table op are you running? Have you tried running SQL Profiler to capture the actual SQL statement being generated? Can you post the actual request message if there is one? Can you post the table definition (or one that would reproduce this)? – Dan Field Mar 22 '16 at 14:06
  • Also try to include your send port binding – zurebe-pieter Mar 22 '16 at 14:17
  • Thanks for the reply. Good idea about SQL profiler, will see if that shows anything. The table schema and request XML are now included. The send port binding is pretty big so haven't included it, but it's pretty much a bog standard WCF-Custom sql send port. – Stuart Charles Mar 22 '16 at 18:32
  • 1
    See this blog https://sandroaspbiztalkblog.wordpress.com/2015/04/09/biztalk-wcf-sql-adapter-the-columns-are-either-duplicated-or-not-in-a-sequence-each-column-can-only-be-selected-one-time-and-columns-must-be-selected-in-sequence/ – Dijkgraaf Mar 22 '16 at 19:35
  • Thanks, saw that article and tried it but same result. – Stuart Charles Mar 22 '16 at 19:55
  • So which columns is it complaining about? As you commented those out in the error message. – Dijkgraaf Mar 22 '16 at 19:59
  • I would set up a trace using SQL Profiler to see what the actual query being run is. That should give you a pretty good idea. I'd also make sure your SelectResult schema had the correct order, as in the blog @Dijkgraaf linked above - if the order or spelling differs at all it will throw this error. – Dan Field Mar 22 '16 at 21:47
  • Just ran the trace and the column order matches what I would expect and is the same order as the .xsd table schema "SELECT SETID, CUST_ID, BUSINESS_UNIT_AR, PAC_M_CLIENTCD, ADDRESS_SEQ_NUM, PAC_M_ACCOUNT, PAC_M_SUNCODE, PAC_M_TAXCNTRY FROM [dbo].[PS_PAC_CUSCLI_XREF] WHERE [CUST_ID] = '0000005341' and [ADDRESS_SEQ_NUM] = 1". Have also updated the specific columns reported in the error. – Stuart Charles Mar 23 '16 at 08:38
  • 2
    The error is thrown by the adapter, not SQL Server. There is no reason to assume any incompatibility with SQL Server 2014 (there aren't any). There *are* duplicate questions for this specific error that point to bugs in the adapter itself, eg [this one](http://stackoverflow.com/questions/2054308/biztalk-wcf-sql-adapter-selecting-from-a-view) – Panagiotis Kanavos Mar 23 '16 at 08:43

1 Answers1

0

So I think I managed to prove that although BizTalk 2010 itself is compatible with SQL Server 2014, the delivered SQL adapter in the official adapter pack is not, at least not for SELECT operations.

Here are some alternative solutions

  • Use a stored procedure instead
  • Use a different mechanism to extract data from the application, for example a web service. This is what I did as it is probably best practice anyway.
  • Upgrade to BizTalk 2013 or above

Note that the SQL adapter did work fine for an UPDATE operation.

Stuart Charles
  • 21
  • 2