BUG: Distinct Subquery in Stored Proc Causes Access Violation

  - Microsoft SQL Server, version 4.21 for Alpha

SYMPTOMS

On Microsoft SQL Server for Windows NT on DEC Alpha, a stored procedure may cause an access violation that terminates the client process if it selects based on IN or NOT IN criteria from a subquery returning a DISTINCT result column and with SQL Server's memory setting set to approximately 5120 pages or higher.

NOTE: This behavior does not occur in Microsoft SQL Server for Windows NT on Intel or Microsoft SQL Server for Windows NT on MIPS.

WORKAROUND

Run the SELECT outside of a stored procedure, or change the subquery to the following:

   SELECT pub_id FROM publishers GROUP BY pub_id

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.21 for Windows NT on DEC Alpha. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The follow query duplicates this problem when SQL Server's memory is more than 5120 pages:

   USE pubs
   GO
   CREATE PROCEDURE test AS
   SELECT * FROM publishers WHERE pub_id NOT IN (SELECT DISTINCT pub_id
   FROM publishers)
   RETURN
   GO
   EXEC test