Microsoft KB Archive/68370

PRSQL9012032: ORDER BY Produces NULL Column ID Number: Q68370

1.10 OS/2 buglist1.10 fixlist1.11

Summary:

PROBLEM ID: PRSQL9012032

SYMPTOMS After running the following query

SELECT Col1 INTO NewTable FROM OldTable ORDER BY convert(int, Col1), Col2 the following error message is displayed:

Column names in each table must be unique. Column name ' ' in table 'newtable' is specified more than once. CAUSE When a SELECT..INTO..FROM..ORDER BY query is run and there is a column in the ORDER BY clause that is not in the SELECT list, the new table will have a column with a null column name. Because both columns in the ORDER BY clause in the above query are not in the SELECT list, SQL Server tries to make two columns with a column name of “NULL”.

WORKAROUND Perform the SELECT INTO query to select at least one of the columns in the ORDER BY clause. This will produce only one “NULL” column name. To remove the unwanted columns, perform a second SELECT INTO query into a third table.

STATUS Microsoft has confirmed this to be a problem in SQL Server version 1.1. This problem has been corrected in SQL Server version 1.11, which is available from Microsoft Product Support Services. For more information, contact your primary support provider.