FIX: SQL Server 4.21a Service Pack 3 Fixlist

Article ID: Q131803
Creation Date: 20-JUN-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 3. SQL Server Service Pack 3 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.

NOTE: Service Pack 3 also includes the fixes that were supplied in the previous Service Packs.

LIST OF PROBLEMS CORRECTED IN SERVICE PACK 3

  • FIX: Floating-point Exception Generating Query Plan on Alpha
  • FIX: Thread Deadlock Causes Checkpoint to Hang in tempdb

FIX: Floating-point Exception Generating Query Plan on Alpha

ARTICLE ID: Q130981 BUG# NT: 1781 (4.21a - NTAlpha)

SYMPTOMS

Upon executing a query including a join, with or without NOEXEC set, the following dialog box appears: 

   SQLSERVR.EXE: The exception Floating-point invalid operation
   (0xc0000090) occurred in the application at location 0x00567938.
   Click on OK to terminate the application Click on CANCEL to debug
   the application.
If OK is chosen, the entire SQL Server process is terminated. This happens only on Alpha processors.

CAUSE

A floating-point underflow has occurred while the optimizer was evaluating possible join orders.

WORKAROUND

It may be possible to avoid this problem by changing the number or type of indexes available to the optimizer. The problem may disappear when there is a change in the distribution of data in the tables and UPDATE STATISTICS is run.

FIX: Thread Deadlock Causes Checkpoint to Hang in tempdb

ARTICLE ID: Q131662 BUG# NT: 9992 (4.21a)

SYMPTOMS

When you execute tempdb-intensive queries involving sorting, under very narrow conditions, a thread deadlock can occur during sort cleanup. This is often difficult to identify because the visible symptoms are ambiguous. However the observed characteristics include a general slowdown, increased blocking, and the checkpoint process hanging in tempdb. It is often possible to login to SQL Server with ISQL.EXE, do SELECTs from user databases, but not possible to run data modification statements in tempdb.

CAUSE

Worker threads become deadlocked when deallocating extents during sort cleanup.

WORKAROUND

The problem is exceedingly narrow in scope and very rare. Because of this and the ambiguous symptoms, this problem should not be suspected until close study has ruled out all other problems. This would include verifying dbcc newalloc and dbcc checkdb are error free on all databases, no errors exist in the SQL errorlog, and any observed blocking or slowdown is not caused by conventional concurrency issues. Slightly changing the application, queries, platform, or SQL configuration may avoid the problem.

LIST OF PROBLEMS CORRECTED IN SERVICE PACK 2

  • FIX: Broken Connection Does Not Terminate Blocked SPID
  • FIX: Dropped Net Session Not Detected During Long Query
  • FIX: Conversion Errors to VAX Floating Point

FIX: Broken Connection Does Not Terminate Blocked SPID

ARTICLE ID: Q122486 BUG# NT: 932 (4.2)

SYMPTOMS

An unexpectedly large number of client connections to SQL Server may be observed using the sp_who command or performance monitor. Many of the clients shown by sp_who have rebooted or otherwise terminated their client applications. sp_who will show these clients to be blocked on one or more other client processes.

CAUSE

If a client connection is blocked by a lock held by another process and the client's connection to SQL server is abnormally broken (for example, network problems, client GP fault, or client reboot), the spid used by that client will not be freed until the blocking process releases its locks.

WORKAROUND

Clients should be sure to terminate their connection to SQL Server. Applications should cancel long running queries and, if necessary, explicitly close connections to SQL Server. This will tend to discourage users from rebooting or terminating applications taking an extended period of time to process SQL commands.

FIX: Dropped Net Session Not Detected During Long Query

ARTICLE ID: Q124949 BUG# NT: 966 (4.21)

SYMPTOMS

A query can continue to run on SQL Server even after the client reboots and the network session has dropped. The query will acquire whatever type of locks are appropriate for the query type, which in some cases can block other users. Unless it becomes blocked on another connection's lock, the query will terminate when it has run to completion or when it needs to send results back to the nonexistent client. The query can usually be terminated with the Transact-SQL KILL command.

CAUSE

If a client is running a long query that does not return results for a while, then the net session is dropped because the client reboots, the query can continue to run. An example of this type of query would be: 

   SELECT COUNT(*) FROM LARGETABLE
If the query became blocked on another connection's lock, this could also prevent it from returning results. If in this state, the client running the query reboots, the query will continue to run even though its network session is terminated. This is caused by SQL Server not noticing the network session termination. Whenever the query begins to send results back to the nonexistent client, SQL Server will notice the network session is gone and terminate the query.

WORKAROUND

This problem only happens infrequently, as two fairly rare simultaneous events must occur to reproduce it.

  1. A long-running query that does not return results.

  2. The same client running the query must abruptly terminate in an uncontrolled fashion, rather than logging out or canceling the query with dbcancel().

In cases where this happens, the workaround is to use the Transact-SQL KILL command to terminate the query. A well-designed application should always allow users to cancel a query at any point during execution. These applications will generally not see this problem.

FIX: Conversion Errors to VAX Floating Point

ARTICLE ID: Q125636 BUG# NT: 959 (4.2)

SYMPTOMS

VAX clients may experience conversion errors when selecting very large float values from Microsoft SQL Server.

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: Q109187 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:

  1. Run UPDATE STATISTICS on all the tables involved.

  2. 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: Q111680 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: Q116075 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: Q124059 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: Q124238 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 kbfix4.21a.sp1 kbfix4.21a.sp2
Keywords : SSrvWinNT
Version : 4.21a
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.

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