Microsoft KB Archive/105336

{|
 * width="100%"|

BUG: SELECT INTO Does Not Raise Error with No Column Name

 * }

Q105336

-

The information in this article applies to:


 * Microsoft SQL Server version 4.2x

-

BUG# NT: 489 (4.2)

SYMPTOMS
If a table is created using SELECT INTO, and no column name is given, sp_help returns error 515:

attempt to insert the value NULL into column , table tempdb.dbo.#sphelptab_.....column does not allow nulls.

Retrievals and updates of the data work fine on the table. Select * on the table shows that some of the columns have NULL column names.

CAUSE
The SELECT INTO statement puts a NULL for the column name when aggregate function is used and no column name is given. A table should not have NULL column names.

For example:

    select count(*), title_id into t    from titles

creates table t with two columns: NULL, and title_id. Sp_help t gives error 515.

WORKAROUND
Make sure the columns names are supplied when using SELECT INTO.

For example:

    select cnt=count(*), title_id into t    from titles

STATUS
Microsoft has confirmed this to be a problem in SQL Server version 4.2.

Additional query words: syscolumns sp_help function

Keywords : kbprogramming

Issue type :

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ420OS2