Microsoft KB Archive/101694

From BetaArchive Wiki

FIX: SQL Server Fixlist 4.20aK9

Article ID: Q101694
Creation Date: 20-JUL-1993
Revision Date: 28-APR-1997

                  Microsoft SQL Server version 4.20aK9

The following is a list of problems, and various other improvements that were discovered during internal testing, that have been fixed in SQL Server version 4.20ak9. For more information, please contact your primary support provider. Please note that workarounds have been provided for your information only. It is not necessary to implement these workarounds if you have the updated software.

Fix: Error 511 when INSERT into Large Table

ARTICLE-ID: \[\[..\/\|Q\]\]

PROBLEM ID: BUG#1542

SYMPTOMS

   When a row is inserted into a table created with a large number of large
   varchar columns, you may encounter the following error:

      Updated or inserted row is bigger than the maximum size
      allowed for this table.
      (Msg 511, Level 16, State 2)

   For example, if you create a table with 127 varchar(255) columns, the
   table will be created without any problems, but you will get the above
   error when you attempt to insert the row.

   A look at the row in sysindexes for the table will show a negative
   number for maxlen field.

WORKAROUND

   Try creating the table with smaller size of the varchar columns or
   reduce the size of the table.

STATUS

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

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Fix: STR() Function with Length of 255 Causes GP Fault

ARTICLE ID: \[\[..\/\|Q\]\]

PROBLEM ID: BUG#1573

SYMPTOMS

   Issuing a query that uses the STR() function to convert a floating
   point number to a string may cause SQL Server to general protection
   fault (GP fault).

CAUSE

   SQL Server incorrectly handles the STR() function when a length of
   255 is used. For example, the following query will cause a GP
   fault:

      select STR(0.1, 255, 5)
      go

   If a length of less than 255 is used, the query works properly.

STATUS

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

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Fix: Negative DPAGES May Cause an INSERT/SELECT to GP Fault

ARTICLE ID: \[\[..\/\|Q\]\]

PROBLEM ID: BUG#1588

SYMPTOMS

   The following query causes a general protection fault (GP fault)in SQL
   Server 4.2:

     INSERT tableA
          SELECT colA1
          FROM       tableA A,
                 tableB B
          WHERE  A.colA1 = B.colB1

More Information: The above query causes a GP fault only if the 'DPAGES' column in SYSINDEXES for tableA is negative.

WORKAROUND

   Run DBCC CHECKTABLE on the table. This corrects the dpages count.
   Note that if DBCC CHECKDB is run regularly, the chances of running
   into this problem diminish.

STATUS

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

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Fix: Client Not Notified when Locks Are Exceeded

ARTICLE ID:\[\[..\/\|Q\]\]

PROBLEM ID: BUG#1597

SYMPTOMS

   When SQL Server runs out of locks, the client application is not
   notified of the error. However, the following error is logged in
   the SQL Server ERRORLOG:

      SQL Server has run out of LOCKS. Re-run your command when there
      are fewer active users, or ask your System Administrator to
      reconfigure SQL Server with more LOCKS.
      (Msg 1204, Level 19, State 1)

CAUSE

   SQL Server incorrectly handles the out of locks condition, and
   rather than sending the 1204 error to both the client and the SQL
   Server ERRORLOG, it only sends it to the ERRORLOG.

STATUS

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

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Fix: UNION of Two SELECT's with Subqueries May Cause GP Fault

ARTICLE ID:\[\[..\/\|Q\]\]

PROBLEM ID: BUG#1603

SYMPTOMS


The following statement may cause a general protection fault (GP fault) in SQL Server 4.2:

     SELECT DISTINCT A.col1
          FROM tab   A
          WHERE 0 <> (SELECT SUM(col2)+SUM(col3)
                         FROM tab B
                         WHERE A.col1=B.col1)
     UNION
     SELECT DISTINCT A.col1
          FROM tab A
          WHERE 0 <> (SELECT  SUM(col2)+SUM(col3)
                         FROM tab B
                         WHERE A.col1=B.col1)

More Information: The problem occurs when the subquery returns more than one row. In that case, SQL Server should raise error message 512 ("Subquery returned more than 1 value. This is illegal when the subquery follows =, !=, <, <=, >, >=, or when the subquery is used as an expression") and terminate the query instead of attempting to evaluate the second SELECT involved in the UNION.

STATUS

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

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Fix: Uninitialized Variables in Empty Trigger Causes Trap

ARTICLE ID:\[\[..\/\|Q\]\]

PROBLEM ID: BUG#1604

SYMPTOMS

   When one or more local variables are declared in a trigger (for
   update, insert, or delete) and no value is assigned to any one of
   these local variables and there are no other statements defined
   within the trigger, SQL Server will TRAP D upon execution of the
   trigger. The TRAP D occurs after the trigger is created and then
   SQL Server is shutdown and restarted once.

   After the trap occurs on the server, the client connections will be
   lost. PSTAT indicates that SQLSERVR.EXE is still running but all
   other attempts to log into SQL Server will fail until the process
   executing SQLSERVR.EXE is stopped and then SQL Server is restarted.

WORKAROUND

   Add an additional valid statement to the trigger, or ensure that
   all variables are initialized to a valid value.

STATUS

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

>>>>>>>>>>>>>>>>>>> Fix: CONVERT() Does Not Handle Styles 13 and 113 Correctly

ARTICLE ID:\[\[..\/\|Q\]\]

PROBLEM ID: BUG#1616

SYMPTOMS

   When converting a DATETIME data type to character with the Transact-
   SQL CONVERT() function, styles 13 and 113 do not get converted
   as stated in the documentation.

CAUSE

   In the "Language Reference" guide under CONVERT, it states that
   styles 13 and 113 should be in the form of:

      DD MMM YYYY hh:mi:ss:mmmm(24h)

   However, style 13 comes out with the month as a number and
   the year as only the last two digits:

      DD MM YY hh:mi:ss:mmmm(24h)

   and style 113 comes out with the month as a number:

      DD MM YYYY hh:mi:ss:mmmm(24h)

STATUS

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

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Fix: 511 on SELECT INTO with over 127 varchar(255)

ATICLE ID:\[\[..\/\|Q\]\]

PROBLEM ID: BUG#1637

Summary:

A SELECT INTO that references a table with more than 127 varchar(255) columns will fail with the following error:

   Error 511:
   Updated or inserted row is bigger than maximum size (-nnnnn)
   allowed for this table.

(Where -nnnnn is a large negative number.) SYMPTOMS

   Assuming a table named "bigtab" has been defined with more than 127
   varchar(255) columns, the following SELECT will get the 511 error:

      select * into newtab from bigtab

   The table newtab will have been created and is fully functional,
   but no data rows will have been inserted.

WORKAROUND

   If a 511 error is detected on a SELECT INTO from a table with over
   127 varchar(255) columns, a SELECT based INSERT can be used to copy
   over the data rows to the new table. For example:

      insert newtab select * from bigtab

RESOLUTION

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



Additional query words:
Keywords : SSrvGen kbfixlist
Version : 4.2a
Platform : OS/2
Issue type : kbref

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.

©1997 Microsoft Corporation. All rights reserved. Legal Notices.