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