How to fix Error 4142 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 4142 – “Aggregates Are Not Allowed in the RECEIVE List”

Why This Error Happens

This error occurs because Service Broker’s RECEIVE statement does not support aggregate functions (such as COUNT, SUM, MAX, MIN, or AVG) within its column list. SQL Server 2025 enforces strict parsing rules on the RECEIVE statement since it is designed to retrieve raw message rows from a queue, not to perform set-based aggregation. This typically happens when a developer attempts to summarize message data directly in the RECEIVE clause instead of processing the raw data in a subsequent step or temporary table.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Open SQL Server Management Studio (SSMS) connected to your Windows Server 2025 instance.
  • Locate the stored procedure or script that triggered Error 4142 (check the SQL Server Error Log via Management > SQL Server Logs).
  • Look specifically for RECEIVE statements containing COUNT(*), SUM(), or similar functions in the column selection list.

Step 2: Refactor the RECEIVE Statement

  • Modify the query to select raw columns only (e.g., message_type_name, conversation_handle, message_body) instead of aggregates.
  • Example of incorrect syntax: RECEIVE COUNT(*) FROM MyQueue;
  • Example of corrected syntax: RECEIVE TOP(1) conversation_handle, message_body FROM MyQueue;
  • Save the script to a local path, such as C:\Scripts\SQL\FixBrokerQueue.sql, for version control.

Step 3: Implement a Temporary Table for Aggregation

  • If you need to count or sum the data retrieved from the queue, first RECEIVE the data into a table variable or temporary table.
  • Use the following logic: RECEIVE * FROM MyQueue INTO @TempTable;
  • Perform the required aggregate function (e.g., SELECT COUNT(*) FROM @TempTable) as a separate, subsequent query.

Step 4: Validate Service Broker Configuration

  • Ensure the Service Broker is enabled on the database to rule out configuration conflicts alongside the syntax fix.
  • Run the following T-SQL command to check status: SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';
  • If the result is 0, enable it using: ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Alternative or Verification Steps

  • Verify the Fix: Execute the refactored stored procedure in a test environment. If the query compiles and returns raw row data without throwing Error 4142, the syntax correction was successful.
  • Alternative Approach: If you require real-time counting logic, use an OUTPUT clause in conjunction with the RECEIVE statement to capture data into a table structure before running COUNT or SUM operations in the application layer (C#/.NET or Python) rather than within the T-SQL batch itself.