Article ID: 165511
Article Last Modified on 10/3/2003
APPLIES TO
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q165511
BUG #: 16571
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: kbbug kbfix kbusage KB165511
