none
WCF-Custom adapter - SQL Binding - Select on view returns Error RRS feed

  • Question

  • Ladies/Gentlemen,

    I have been implementing several solutions using the WCF-Custom adapter with regards to the SQL-Binding. So far I did not have any issues, however today a new requirement arose which involved selecting data from a View; well in theory this should be a piece of cake, but boy am I mistaken :-)

    The error I receive whenever I perform a select on a view is as follows;

    "Microsoft.ServiceModel.Channels.Common.XmlReaderGenerationException:  The columns COLUMN A and COLUMN B are either duplicated or not in a sequence. Each column can only be selected one time, and columns must be selected in sequence."

    My first hunch tells me it has to do with the way the View is constructed, but sadely enough I can't look into the source of the view. I hope I am mistaken and it has to do with a configuration setting, so if anyone can help me out; I would be much obliged.

    An alternative would be a storedprocedure, but hey that's using a workaround and not fixing the issue :-)

    So if anyone has a solution or suggestion, please don't hesitate and reply.

    PS, here's the select statement

    <?xml version="1.0" encoding="utf-8"?>
    <ns0:Select xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/Types/Views/dbo" xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/ViewOp/dbo/vwBizTalk" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <ns0:Columns>*</ns0:Columns>
    <ns0:Query/>
    </ns0:Select>

    Cheers

    Rene




    Wednesday, March 3, 2010 12:07 AM

Answers

  • The WCF-SQL adapter requires each column to have a unique name. SQL views do not require this so unfortunately you may need to have the view rewritten so each column is unique. This is the solution to your first post of the thread.

    For the second post, are you regenerating the schema or just adding columns to the binding file? You have to regenerate the schema or add columns manually to the schema. I would suggest regenerating it until you get a handle on what it generates before adding new columns by hand.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Wednesday, March 3, 2010 5:08 AM
    Moderator

All replies

  • Hmmm, single column selection returns results, so when I send in f.e the following query

    <?xml version="1.0" encoding="utf-8"?>
    <ns0:Select xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/Types/Views/dbo"  xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/ViewOp/dbo/vwBizTalk" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <ns0:Columns>COLUMNA</ns0:Columns>
    <ns0:Query></ns0:Query>
    </ns0:Select>

    it returns the results, however whenever I add more than one 'select column' it throws that Common.XmlReaderGenerationException


    <?xml version="1.0" encoding="utf-8"?>
    <ns0:Select xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/Types/Views/dbo"  xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/ViewOp/dbo/vwBizTalk" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <ns0:Columns> COLUMNA,COLUMNB,COLUMNC</ns0:Columns>
    <ns0:Query></ns0:Query>
    </ns0:Select>

    well seems to be trial and error for now, but I'll have to get some sleep as I have to get up early and head to the customer
    Wednesday, March 3, 2010 12:46 AM
  • Last post for tonight,

    I reckon my hunch is correct and the issue lies in the SQL statement used to construct the view. Well I'll grab a hold on the DBA first thing tomorrow. If that's the issue; I'll inform you all and post the solution, but hey it's still a hunch so if you have a solution, let me now

    Cheers

    Rene
    Wednesday, March 3, 2010 12:55 AM
  • The WCF-SQL adapter requires each column to have a unique name. SQL views do not require this so unfortunately you may need to have the view rewritten so each column is unique. This is the solution to your first post of the thread.

    For the second post, are you regenerating the schema or just adding columns to the binding file? You have to regenerate the schema or add columns manually to the schema. I would suggest regenerating it until you get a handle on what it generates before adding new columns by hand.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Wednesday, March 3, 2010 5:08 AM
    Moderator
  • The WCF-SQL adapter requires each column to have a unique name. SQL views do not require this so unfortunately you may need to have the view rewritten so each column is unique. This is the solution to your first post of the thread.

    For the second post, are you regenerating the schema or just adding columns to the binding file? You have to regenerate the schema or add columns manually to the schema. I would suggest regenerating it until you get a handle on what it generates before adding new columns by hand.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Ben,

    thanks for confirming my hunch, I'll have the view changed. With regards to the second post; just posted some 'test/debug' results, so it did not contain a question was simply thinking outloud :-)

    Cheers

    Rene
    Wednesday, March 3, 2010 7:10 AM
  • No problem. Usually I get the error about duplicate columns while generating the schema so I recognized it.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Wednesday, March 3, 2010 2:38 PM
    Moderator