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)”
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
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:
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.
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
- In this case, you need to change the value “*” with the following string value:
Great … really helpful. Thanks
Good catch: Note that solution nr2 should be the recommended solution. It shields your code from changes in the underlying table or view.
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
@Kumar Is the offending environment using BizTalk 2013 R2 and SQL Server 2014? I think I am having the same problem. Reference: https://social.msdn.microsoft.com/Forums/en-US/065e2017-969d-41e3-bf25-a38b1797da6f/wcfsql-table-select-the-columns-are-either-duplicated-or-not-in-a-sequence?forum=biztalkgeneral&prof=required
Vercellone