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

This week while migrating a demo, that I will present in BizTalk Summit 2015 – London event, from BizTalk Server 2013/Visual Studio 2012 to BizTalk Server 2013 R2/Visual Studio 2013 I found a strange behavior.

This is a simple demo where I have canonical schemas that will be transformed into different Schemas in order to perform 3 types of operations: Insert, Delete and Select in a custom SQL database using the WCF-SQL adapter and receive back the response.

However, when I tried the demo I got the following exception in the Select operation:

“Microsoft.ServiceModel.Channels.Common.XmlReaderGenerationException: The columns FullName and Address 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)”

WCF SQL columns are either duplicated or not in a sequence

Nevertheless, everything was working fine in the old environment (BizTalk Server 2013). The select statement transformation is what we normally use daily in our projects, by just putting a “*” in the Columns element of the SQL Table Operation Select Schema

WCF SQL Select Statement transformation

Cause

The error message is clear and we can easily identify the problem but the reason why this strange behavior happens is not, and I really don’t know why or how it happened, but when I checked and compare the SQL Table Operation SelectResponse Schemas in both projects I realize that for some reason during the project migration Visual Studio changed the order of elements inside SelectResult as you can see in the picture below:

WCF SQL SelectResponse Schemas Comparation

And, of course, the SELECT * statement is respecting the correct order of the columns in the database which is the order that we have on the left side in the picture above.

WCF SQL columns are either duplicated or not in a sequence SQL Table Sctructure

Solution

You have two options to solve this problem:

  • You can either rectify the order of the elements in your schema to respect the order that exists in the database
  • Or you need to change the value that you are passing to the Columns element inside your map to return the result in the right order of your schema.
    • In this case, you need to change the value “*” with the following string value:
      • Email, Address, CitizenCard, ZipCode, PhoneNumber, FullName
WCF SQL Select Statement transformation fixed
Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

5 thoughts on “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”

  1. Good catch: Note that solution nr2 should be the recommended solution. It shields your code from changes in the underlying table or view.

  2. Hi Sandro,
    Hope you are doing gud…

    Am also getting the same exception. But am constructing the Select request message and dynamically sending to the multiple databases. Each and every databases Select result order is changing as you mentioned. So in my case I cant predict the Order of the Select Result as am sending request to multiple databases like 120 DBs.
    So Column value as “*” and string value like field names, both are not working. Its throwing same exception.

    Can you please let me know, what exactly I have to do for this type of Table operation ?
    Please let me know the solution.

    Regards
    Kumar

Leave a Reply

Your email address will not be published. Required fields are marked *

turbo360

Back to Top