Microsoft KB Archive/97436

= FIX: SQL Server Fixlist 4.20aK8 =

Article ID: 97436

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition

-



This article was previously published under Q97436



The following is a list of bugs that were discovered during internal testing of SQL Server and fixed in SQL Server version 4.2a server patch K8. For more information, please contact your primary support provider.

Please note that workarounds are provided for your information only. It is not necessary to implement these workarounds if you have the updated software.

TITLE FIX: Table Insert from View Might GP Fault Server
PROBLEM ID: BUG# 1426

SYMPTOMS
SQL Server GP faults when inserting into a table using a select statement from a view defined as a two-table join, whose columns contain the aggregate function AVG. A sample query follows:

create table t1(c int) create table t2(c int) insert t1 values(1) insert t2 values(1) go     create view v (c, av) as      select t1.c, avg(t1.c)      from t1,t2 where t1.c=t2.c     group by t1.c      go      create table t3(c1 int, c2 int)    /* Note c2 is int */ insert t3 select * from v         /* Insert avg result */ go                                /* into int column */

CAUSE
SQL Server incorrectly handles the automatic type conversions when using a view to insert into a table. Note that column c2 in table t3 is defined as int, but the result of avg should be float. The problem occurs when trying to insert the result of avg into the int column.

WORKAROUND
The workaround is to declare the column that receives the avg result as a float. In the sample query, if you declare column c2 in table t3 as a float, the problem goes away.

STATUS
Microsoft has confirmed this to be a problem in SQL Server version 4.2. This problem was corrected in SQL Server version 4.2ak8, which is available from Microsoft Support Services. For more information, please contact your primary support provider.

TITLE FIX: Insert with LIKE and ORDER BY from System Tables
PROBLEM ID: Bug # 1451 1452

SYMPTOMS
SQL Server may cause a general protection fault (GP fault) to occur when a SELECT statement concatenates text characters onto a column value from a certain system table (sysobjects, sysdatabases, or sysdevices) in the master database and assigns the result to a local variable. The GP fault occurs when that SELECT statement contains a LIKE operator and an ORDER BY clause.

For example, the following SELECT statement causes a GP fault:

DECLARE @t varchar(255) SELECT @t=&quot;AAA&quot;+name FROM master..sysobjects WHERE name LIKE &quot;syso%&quot; ORDER BY name GO

CAUSE
SQL Server handles the insertion incorrectly when it inserts a value coming from a column value into a local variable while it is using the LIKE operator and the ORDER BY clause to obtain the value from certain system tables in the master database.

WORKAROUND
Break the single SELECT statement into two parts. For example, if you want to insert the last row of the qualified data rows into the local variable, break the query into these two parts:


 * 1) Insert the qualified rows into a temporary table without using the ORDER BY clause.
 * 2) Insert the rows into the local variable from the temporary table using the ORDER BY clause.

STATUS
Microsoft has confirmed this to be a problem in SQL Server versions 1.11 and 4.2. This problem was corrected in SQL Server version 4.2ak8, which is available from Microsoft Support Services. For more information, please contact your primary support provider.

TITLE FIX: Select from a View with Recreated Tables May GP Fault
PROBLEM ID: Bug# 1529

SYMPTOMS
When selecting from a view that is defined as a three table join, and if one of these tables has been recreated since the view is defined, SQL Server may GP fault.

For example, assume the view is defined as:

create table A (id char(5)) create table B (id char(5)) create table C (id char(5)) go     create view V as      select A.id from A,B,C where A.id=B.id and A.id=C.id     go

If table C is dropped and recreated again after the view is defined (which is allowed), the query

select * from V

will cause SQL Server to GP fault.

CAUSE
SQL Server incorrectly handles the queries on views that are defined with a three or more object join, if some of the objects are recreated after the views are defined.

WORKAROUND
The workaround is to drop and recreate the view.

STATUS
Microsoft has confirmed this to be a problem in SQL Server version 4.2a. This problem was corrected in SQL Server version 4.2ak8, which is available from Microsoft Support Services. For more information, please contact your primary support provider.

TITLE FIX: Dividing by Negative Money Value Causes Timeslice Error
PROBLEM ID: BUG# 1574

SYMPTOMS
When dividing by certain negative money values, the operation may cause SQL Server to generate a timeslice error:

timeslice -1501, current process infected.

SQL Server will temporarily lock up until the timeslice is detected. At that time, the process is terminated and SQL Server resumes as normal. Note: Timeslice errors may cause SQL Server version 1.11 to GP fault (bug# 1574/1575).

CAUSE
SQL Server does not correctly handle division of certain negative money values. Values that are know to cause problems are -$6.53, -$6.54, and -$6.55.

STATUS
Microsoft has confirmed this to be a problem in SQL Server versions 1.11 and 4.2. This problem was corrected in SQL Server version 4.2ak8, which is available from Microsoft Product Support Services. For more information, please contact your primary support provider.

Keywords: kbfix KB97436

-

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

© Microsoft Corporation. All rights reserved.