BUG: St. Proc. Behavior Inconsistent When Grouping by a Bit Col.

• Microsoft SQL Server version 6.5

SYMPTOMS

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

1. After initial creation, the stored procedure runs fine.

2. After restarting SQL Server, 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