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

ID: Q150389

The information in this article applies to:
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          : kbprg SSrvStProc kbbug4.21a 
Version           : 4.21a
Platform          : WINDOWS 
Issue type        :

Last Reviewed: March 25, 1999