FIX: SQL Server Fixlist 4.20aK8

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 */

   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.

   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.

   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="AAA"+name
         FROM master..sysobjects
         WHERE name LIKE "syso%"
         ORDER BY name
      GO

   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.

   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.

   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.

   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.

   The workaround is to drop and recreate the view.

   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).

   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.

   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.