Microsoft KB Archive/126010

FIX: SQL Server 4.21a Service Pack 1 Fixlist

Article ID: Q126010
Creation Date: 12-FEB-1995
Revision Date: 30-APR-1997 '
The information in this article applies to:

Microsoft SQL Server, version 4.21a

The following is a list of fixes and other various improvements that have been made in SQL Server Service Pack 1. SQL Server Service Pack 1 is now available from your primary support provider. For more information, 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.

LIST OF PROBLEMS CORRECTED IN SERVICE PACK 1

FIX: Complex Query May Cause 1521 Error
FIX: Runtime Error in Trigger from RPC Causes Server to Hang
FIX: SELECT MAX from INT Column May Cause Access Violation
FIX: False Error 512 on Subqueries with Version 4.21.006
FIX: Access Violation if Fully Qualified View Name Is Large

FIX: Complex Query May Cause 1521 Error

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 597 (4.2)

SYMPTOMS

Under certain conditions SQL Server may return error 1521:

   Sort failed because a table in tempdb used for the processing
   of the query had a bad data page count.

This generally occurs when processing very complex queries that may involve any of the following: multiple tables, the use of dynamic indexes, internal sorting, or heavy use of tempdb. WORKAROUND

In some cases this problem can be resolved by executing one or more of the following:

Run UPDATE STATISTICS on all the tables involved.
Modify the query so the optimizer can produce a different query plan. - Make sure your indexes have been designed correctly and efficiently. - Purge or archive any unnecessary data from any tables involved. - Change BETWEEN clauses to the corresponding <= and >= expressions.

NOTE: You may review your query plan by running SET SHOWPLAN ON before executing the query. For more information on interpreting SHOWPLAN output refer to "Appendix B: Understanding SHOWPLAN Output" in the "Microsoft SQL Server for NT Troubleshooting Guide." FIX: Runtime Error in Trigger from RPC Causes Server to Hang

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 681 (4.2)

SYMPTOMS

When issuing a RPC to a remote SQL Server for Windows NT, the client application hangs. The remote server will refuse to accept further connections and any currently connected clients that attempt to issue queries will hang. The remote server cannot be shut down with the service control manager. The SQLSERVR process must either be killed or the NT server cycled.

CAUSE

If execution of RPC causes a trigger to be fired which in turn encounters a non-fatal runtime error, such as a unique key or rule violation, and if that trigger contains any statements after the point where the error occurred, the symptoms noted above may occur.

Execution of the stored procedure locally will work properly, even if the runtime error is encountered.

WORKAROUND

Care must be taken to ensure that triggers will not cause non-fatal runtime errors. Logic should either be added to the trigger to guard against this, or any statements that might generate these errors should be placed in the stored procedure instead of the trigger.

FIX: SELECT MAX from INT Column May Cause Access Violation

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 814 (4.2)

SYMPTOMS

Selecting the MAX value from a column that is defined as INT NOT NULL and is the fourth column in a table may result in an access violation on SQL Server.

MORE INFORMATION

The table may be wider than 4 columns, but MAX should be selected from the fourth column and columns 1-3 should be 4 bytes wide.

Here is a brief script to demonstrate the problem:

   use pubs
   go
   drop table t1
   go
   create table t1(c1 int, c2 int, c3 int, c4 int)
   go
   declare @counter int
   select @counter = 0
   while @counter < 400
   begin
     insert t1 values(@counter,@counter,@counter,@counter)
     select @counter = @counter + 1
   end
   go
   select max(c4) from t1
   go

FIX: False Error 512 on Subqueries with Version 4.21.006

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 929 (4.21.006)

SYMPTOMS

Some subqueries in a query select list that are correctly returning one row may falsely return a 512 error in server versions 4.21.006 or later.

A subquery in a select list must operate as an expression, which means it is not legal for such queries to return more than one row. Prior to version 4.21.006, SQL Server would not detect this condition as an error and would instead return the last row of the subquery result set.

This was filed as bug 550, and a bugfix was supplied in 4.21.006. However, starting with this version some queries that are correctly returning one row are incorrectly generating the 512 error:

   Subquery returned more than 1 value. This is illegal when
   the subquery follows =, !=, <, <=, >, >=, or when the subquery is
   used as an expression.

For example, if there are two tables:

   Emp
   empid    name
   1        Jane Doe

   Prod
   plantid   process  empid
   1         1        1
   1         2        1

The following query generates a false 512 error:

   select (select name
           from emp
           where emp.empid = prod.empid)
   from prod

WORKAROUND

If possible, recode the query as a join:

   select name
   from emp, prod
   where emp.empid = prod.empid

FIX: Access Violation if Fully Qualified View Name Is Large

ARTICLE ID: \[\[..\/\|Q\]\] BUG# NT: 935 (4.21a)

SYMPTOMS

SQL Server version 4.21a will suffer an access violation if the fully qualified name of a view becomes very large. This occurs if the fully qualified name exceeds 30 bytes on the Alpha versions of SQL Server, but may occur with larger names on the MIPS or Intel versions. The problem is specific to view names. It does no occur for other objects, such as tables or stored procedures.

WORKAROUND

If possible, shorten the name of the view.

Additional query words: Windows NT
Keywords : SSrvWinNT
Version : 4.21a 4.21a.sp1
Platform : WINDOWS
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.