BUG: Alias w/ Aggregates Can Cause Handled AV on 4.2x Upgrade

Last reviewed: May 2, 1997
Article ID: Q150389

The information in this article applies to:
  • Microsoft SQL Server, version 4.21a
BUG#: 14961 (4.21a)

SYMPTOMS

If a stored procedure is created that does an UPDATE statement and uses alias names and table names intermixed in a query accompanied by aggregate functions, the upgrade of a SQL Server version 4.21a server to version 6.0 may hang and a handled access violation (AV) will be seen in the error log.

The same stored procedure can be created and executed in both versions 4.21a and 6.0. The upgrade from 4.21a to version 6.5 completes successfully.

WORKAROUND

Verify that there are no stored procedures fitting the above criteria that would cause the upgrade to hang.

If the upgrade does hang, restore version 4.21a to the point where it is ready to upgrade again. Then delete the stored procedure and recreate it after the upgrade completes.

STATUS

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

MORE INFORMATION

An example of a stored procedure that would result in a handled access violation is:

create table tab1 (col1 int, col2 int, col3 char (1)) go create table tab2 (col1 int, col2 int, col3 char (1))

go insert into tab1 values (10, 20, 'A') go insert into tab1 values (10, 20, 'B') go insert into tab1 values (30, 40, 'C') go insert into tab1 values (30, 40, 'D') go insert into tab2 values (10, 20, 'A') go insert into tab2 values (10, 20, 'B') go insert into tab2 values (30, 40, 'C') go insert into tab2 values (30, 40, 'D') go

CREATE PROCEDURE testproc as begin update tab2 set tab2.col1 = (select (max(tab2.col2) - sum (tt.col2)) from tab2 t, tab1 tt where t.col3 = tt.col3)

If the aggregate statement is taken out, the upgrade completes successfully, but the error log will show that the stored procedure could not be upgraded. The stored procedure will need to be dropped and recreated. The following example shows this behavior:

CREATE PROCEDURE testproc as begin update tab2 set tab2.col1 = tab2.col2, tt.col1 = tt.col2 from tab21 t, tab1 tt where t.col3 = tt.col3 end

If all the table name prefixes are replaced with aliases, the upgrade completes successfully and the stored procedure does not need to be recreated.

A similar stored procedure that just does a SELECT statement will result in a successful upgrade, but the stored procedure will need to be recreated. The following example shows this behavior:

CREATE PROCEDURE testproc as begin select (max(tab2.col2) - sum (tt.col2)) from tab2 t, tab1 tt where t.col3 = tt.col3 end


Additional query words: exception sp sproc hung
Keywords : kbbug4.21a kbprg SSrvStProc
Version : 4.21a
Platform : WINDOWS
Issue type : kberrmsg


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.