Difference between revisions of "Microsoft KB Archive/105336"

From BetaArchive Wiki
m (Text replacement - "<" to "<")
m (Text replacement - ">" to ">")
 
Line 23: Line 23:
 
== SYMPTOMS ==
 
== SYMPTOMS ==
  
If a table is created using SELECT INTO, and no column name is given, sp_help <tablename&gt; returns error 515:
+
If a table is created using SELECT INTO, and no column name is given, sp_help <tablename> returns error 515:
  
 
<blockquote><div class="ERRORMESSAGE">
 
<blockquote><div class="ERRORMESSAGE">
  
attempt to insert the value NULL into column <col_name&gt;, table tempdb.dbo.#sphelptab_.....column does not allow nulls.
+
attempt to insert the value NULL into column <col_name>, table tempdb.dbo.#sphelptab_.....column does not allow nulls.
  
 
</div></blockquote>
 
</div></blockquote>

Latest revision as of 16:44, 20 July 2020

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 <tablename> returns error 515:

attempt to insert the value NULL into column <col_name>, 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


Last Reviewed: November 11, 1999
© 2001 Microsoft Corporation. All rights reserved. Terms of Use.