Microsoft KB Archive/121236

= BUG: Distinct Subquery in Stored Proc Causes Access Violation =

PSS ID Number: 121236

Article Last Modified on 10/21/2000

-

The information in this article applies to:


 * Microsoft SQL Server 4.2x, when used with:
 * the hardware: DEC Alpha

-



This article was previously published under Q121236



BUG# NT: 911 (4.21 - NTAlpha)



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

Keywords: kbprogramming KB121236

Technology: kbAudDeveloper kbHWDecAlpha kbSQLServ420 kbSQLServSearch

-

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

© 2003 Microsoft Corporation. All rights reserved.