FIX: SQL Server 4.21a Service Pack 4 Fixlist

The information in this article applies to:

• Microsoft SQL Server, version 4.21a Service Pack 4

The following is a list of fixes and other various improvements that have been made in SQL Server Service Pack 4. SQL Server Service Pack 4 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 4

• FIX: RPC from Sybase SQL Server to Microsoft SQL Server Fails
• FIX: OPENDSNT.DLL Makes Thread AV Server-Wide
• FIX: Fixed Length Dates Cause Syntax Errors in srv_convert()
• FIX: Alpha: SELECT INTO with Kills Can Cause Process Hangs

FIX: RPC from Sybase SQL Server to Microsoft SQL Server Fails

ARTICLE-ID: Q123755 BUG# NT: 10665 (4.21a)

SYMPTOMS

Remote Stored Procedure calls (RPC) from Sybase SQL Server versions 4.8 and 4.92 on UNIX platforms to Microsoft SQL Server via TCP/IP may fail with the following error message:

   Message 7223: Can not read from site '%.*s'

CAUSE

The server-side network library for TCP/IP (SSMSOCN.DLL) incorrectly handles the RPC requests from certain versions of Sybase SQL Server on the UNIX platforms.

WORKAROUND

Replace the server-side network library SSMSSOCN.DLL with version 4.21 (dated 1/26/94). If you experience any out-of-band data problems (such as invalid buffer errors on the client)after switching to the older version, you may avoid them by adding the following entry to the NT 3.5 registry:

   System\CurrentControlSet\Services\Tcpip\Parameters\TcpUseRFC1122Urgent
   Pointer = 0x1

   ARTICLE-ID: Q120642
   TITLE     :TCP/IP & NBT Configuration Parameters for Windows NT 3.5

FIX: OPENDSNT.DLL Makes Thread AV Server-Wide

ARTICLE-ID: Q132423 BUG# NT: 9175 (4.21a)

SYMPTOMS

The retail x86 4.21a Service Pack 2 and Service Pack 3 OPENDSNT.DLL may cause access violations that were previously limited in scope to a single thread to instead be server-wide.

WORKAROUND

Use the 4.21a OPENDSNT.DLL with SQLSERVR.EXE Service Pack 2 or Service Pack 3, or use SQL Server Service Pack 4, which does not experience this problem.

FIX: Fixed Length Dates Cause Syntax Errors in srv_convert()

ARTICLE-ID: Q132498 BUG# NT: 9658 (4.21a)

SYMPTOMS

Under some conditions, the Open Data Services (ODS) function srv_convert() can generate a syntax error in source field message, when the data being passed to srv_convert() is the correct format.

NOTE: This only happens on fixed length date conversions. For example, "19000101" (dates without dashes or slashes) versus "1900-01-01".

CAUSE

Fixeddate(), a function internal to Open Data Services, experiences a problem with an uninitialized work string that is then converted with atoi(). The first half of this value is user provided; the other half in the failing condition is invalid data left in the string, which should be initialized to 0. This can cause the date value calculated to be invalid and create a syntax error.

WORKAROUND

The only workaround for this problem is to parse the date values and insert dashes prior to conversion.

FIX: Alpha: SELECT INTO with Kills Can Cause Process Hangs

ARTICLE-ID: Q132503 BUG# NT: 9871 (4.21a - NTAlpha)

SYMPTOMS

When you issue a kill on a spid, it will not kill the specified process and subsequent kills issued against the spid will not work.

When you perform a SELECT * from sysprocesses on the server, it will show spid 3 stuck in a command of DUMP TRAN in dbid 2 (tempdb), and one or more user processes stuck in a command of INSERT. These user processes will not respond to the kill command.

This problem is usually only encountered when you perform a stress test that includes SELECT INTO a temp table and repeated kills of all processes.

WORKAROUND

Once the problem occurs, the only solution is to shut down SQL Server with nowait. To avoid or minimize the problem, do not perform excessive KILL commands on processes doing SELECT INTO commands into tempdb.

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.

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

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

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.

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.

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.

   Emp
   empid    name
   1        Jane Doe

   Prod
   plantid   process  empid
   1         1        1
   1         2        1

   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.