Knowledge Base

BUG: Stored Procedure Behavior Inconsistent When Grouping by a Bit Column

Article ID: 174854

Article Last Modified on 10/3/2003

Microsoft SQL Server 6.5 Standard Edition

This article was previously published under Q174854

SYMPTOMS

BUG #: 17248 (6.50)

When you create a stored procedure that involves a temporary table and a WHERE clause on a bit column, the following happens:

After initial creation, the stored procedure runs fine.
After restarting SQL Server or loading the database from a dump, executing the stored procedure generates the following error message:
Msg 252, Level 16, State 1 Can't group by a bit field.

WORKAROUND

To work around this problem, do either of the following:

Use a tinyint instead of a bit column (this is the preferred solution). -or-
Replace the temporary table with a permanent table.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Run the script below to re-create the table and stored procedure. Note that the "a.bitcol1 = b.bitcol1" line in the script creates an implied grouping on bit columns.

CREATE TABLE dbo.table1 (
intcol1 int NOT NULL ,
bitcol1 bit NOT NULL
)
GO

CREATE PROCEDURE sp_test1 AS
BEGIN
CREATE TABLE #table2
(intcol1     INTEGER  NOT NULL)

INSERT INTO #table2 (intcol1)
SELECT
intcol1
FROM
table1 a
WHERE
intcol1 =
(SELECT
MAX(intcol1)
FROM table1 b                WHERE
a.bitcol1  = b.bitcol1)
END

GO

Additional query words: col colum

Keywords: kbbug kbusage KB174854