Microsoft KB Archive/174854

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

Article ID: 174854

Article Last Modified on 10/3/2003

-

APPLIES TO


 * 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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.