none
WCF-SQL, Table Select: The columns are either duplicated or not in a sequence RRS feed

  • Question

  • Hello,

    I'm using the WCF-SQL adapter to select specific columns in a table.

    It works fine in my test environment, but when it runs in Production, here is the error message I got

    The columns aaaaa and bbbbb are either duplicated or not in a sequence. Each column can only be selected one time, and columns must be selected in sequence.

    It appears that the columns in the table in Production are not in the same order as in Test.

    Before asking my client to drop and and recreate this table (and eventually others), I was wondering if there is something I could do to have my query to work in both environments?

    Thank you

    Thursday, May 15, 2014 4:19 PM

Answers

  • Hi,

    Column order does matter if you use Select * in your polling statement as it generate the schema according to sequence. To avoid this issue, it is better to use Typed Polling and Selecting specific columns with aliases rather than using Select *.


    If this answers your question please mark as answer. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply.



    Thursday, May 15, 2014 4:33 PM

All replies

  • Hi,

    Column order does matter if you use Select * in your polling statement as it generate the schema according to sequence. To avoid this issue, it is better to use Typed Polling and Selecting specific columns with aliases rather than using Select *.


    If this answers your question please mark as answer. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply.



    Thursday, May 15, 2014 4:33 PM
  • I have a WCF-SQL Send (not receive/polling!) port throwing this error when attempting to perform a TableOp/Select/dbo/Employee operation.  The column order in the table designer and returned by select * from dbo.Employee both match my existing Table schema.  The existing schema works fine in other environments (including production).

    Using the Add generated items wizard I generated a new schema against the offending SQL Server and it did generate a schema with the columns in an order that differs.  With SQL profiler I observed the sys.columns query that is being executed to determine the schema's column order at generation time and there is no order by.  I dropped and recreated the table to no avail.  I even tried adding columns one by one.  The sys.columns query still returns them in a different order.

    I can't alter my schema else it will break when deployed to other environments (including production).  I need to make the offending SQL database return the columns in the same order as my other environments.  What are my options? 

    I am using BizTalk 2013 R2 and the offending SQL Server is version 12.0.2456.


    Jason Vercellone

    Thursday, June 4, 2015 2:44 AM
  • Hi Jason,

    I have a very similar issue. Did you finally found a workaround?

    Thanks.

    David.

    Monday, November 30, 2015 2:52 PM
  • No.  I made my schema match production and eventually rebuilt the offending environment for other reasons.  Let me know if you figure it out.

    Jason Vercellone

    Tuesday, December 1, 2015 12:32 AM
  • Hi,

    I found how to workaround but it is very strange.

    In my solution, I call a SQL view, not a table.

    In my DEV environment, even if I use a SELECT * it works. In my PROD environment, even if I set the correct columns in the SELECT clause and with the right order, it does not work.

    I asked the DBA to give me the order of the columns in the view in my PROD environment and thanks to the query below, I use this list in the right order in my SELECT clause. I also changed my XSD to use a "All" instead of a "Sequence". It works but I had to add a setting in the BizTalk solution to change the content of the SELECT clause for each environment... 

    The query to have the list of the columns in a view:

    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], 0 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(50),@ORIGINALSCHEMANAME nvarchar(3)',@ORIGINALOBJECTNAME=N'<<viewname>>',@ORIGINALSCHEMANAME=N'dbo'

    Thursday, December 3, 2015 9:00 AM