FIX: A Derived Table Using a Group By May Cause Error 8158

• Microsoft SQL Server version 6.5

SYMPTOMS

If you drop and then rebuild a table that a stored procedure relies on, the stored procedure may fail with error 8158 "%s has more columns then were specified in the column list". The stored procedure will also fail with this error if the database is dumped and then restored. For this problem to occur, the stored procedure must have a derived table that is based on the table that was dropped and re-created and uses a GROUP BY to retrieve its data.

The example below illustrates the problem if table "test" is dropped and re- created. On versions of SQL Server 6.5 before Service Pack 2, the error occurs when you attempt to create the procedure below, due to bug 15976. Once Service Pack 2 has been applied, you can create and run the procedure until the underlying table is dropped or the database is dumped and restored.

Example:

   create proc test_proc_1
   as
   select *
   from (select col1, col2
   from test
   group by col1, col2
   ) as x(col1, col2)
   return
   go 

WORKAROUND

To work around this problem, rewrite the query so that it does not use a GROUP BY clause.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.

Keywords          : kbusage SSrvGen SSrvStProc kbbug6.50 
Version           : 6.5
Platform          : WINDOWS 
Issue type        : kbbug 

Last Reviewed: April 9, 1999