Post reply

Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET

  • June 7, 2011 at 8:07 am

    #237520

    Hello,

    We have migrated from SQL 2000 TO 2008, we have an existing SP which parses data from XML using OPENQUERY. When parsing there are two columns with the same name within it, in 2000 it works well however when we migrate to 2008 and compiled the SP there it gives the "Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. " error.

    Please let me know what is wrong.

    thanks,

    aman

  • June 7, 2011 at 8:09 am

    #1335028

    In the resultset you use the same column name twice.

    Happens with sp_who2 if you want an exemple.

    The only workaround is to delete or rename the extra column.

  • June 7, 2011 at 8:12 am

    #1335033

    The same code works in SQL Server 2000. Does that mean to make this SP work in 2008, I need to change the code i.e. either rename the column or delete it?

  • June 7, 2011 at 8:15 am

    #1335036

    Are you using a system proc?

    If so you can go in master or msdb and see the source code in both system.

    You could also just run them on both servers and see it there's a difference.

  • June 7, 2011 at 8:17 am

    #1335037

    This isn't a system SP.

  • June 7, 2011 at 8:20 am

    #1335044

    Then go in your proc or sql query, find which column is duplicated and fix it.

    What's unclear about the error message??

  • June 7, 2011 at 8:23 am

    #1335045

    Changing the name of the duplicate column name will resolve the issue. However the same code works well in sql server 2000. As this is a production code and we are migrating to sql server 2008, the last thing I want to do is modify the code. I was looking for a work around.

    Why does the same code work in 2000 and not in 2008 any idea?

  • June 7, 2011 at 8:26 am

    #1335048

    Don't care actually. 2000 has been out of support for YEARS.

    You're moving to sql 2008 so make it work for 2008.

    Sorry if this is bad news but it's not like you have other options.

  • June 7, 2011 at 8:29 am

    #1335049

    Thanks for your help, the only option I see is to modify the code.

  • June 24, 2011 at 5:45 am

    #1343600

    Hi,

    I also faced the same issue when migrated from 2000 to 2005. The xml output format and validations have been changed in sql server 2005

    Sql server 2005 and above versions use better formatting for xml output.

    In 2005, xml output should not contain duplicate columns. But it is allowed in 2000. That's why it works in 2000.

    For same xml query, you will get two different outputs from sql server 2000 & 2005

    Please browse the below link. It may be useful to you to understand.

    Cheers,

    Kamal

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply