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

Posted: April 9, 2015 in BizTalk
Tags: , , , ,

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

WCF-SQL-SelectResponse-Schemas-Comparation

And, of course, the SELECT * statement is respecting the correct order of the columns in the database witch is the order that we have in 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 exist 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

Comments
  1. Great … really helpful. Thanks

  2. Charles says:

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

  3. Kumar says:

    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

  4. […] after googling was to define the columns in the request xml so the order is explicit as stated in Sandro’s blog. This resulted in the same error. Connecting the staging environment to the production server gave […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s