BUG: Alias w/ Aggregates Can Cause Handled AV on 4.2x Upgrade (150389)
The information in this article applies to:
- Microsoft SQL Server 4.2x
This article was previously published under Q150389
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
| Modification Type: | Minor | Last Reviewed: | 3/2/2005 |
|---|
| Keywords: | kbbug kbProgramming KB150389 |
|---|
|