FIX: SQL Server 4.21a Service Pack 4 Fixlist

ID: Q132495

The information in this article applies to:

Microsoft SQL Server version 4.2x

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

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'

This failure may occur when remotely executing any stored procedures, such as sp_who.

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.

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

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.

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.

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.

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.

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.

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

Q109187 BUG# NT: 597 (4.2)

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.

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.

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.

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

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.

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.

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.

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

Worker threads become deadlocked when deallocating extents during sort cleanup.

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.

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.

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

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

For additional information, please see the following article in the Microsoft Knowledge Base:

Q120642 :TCP/IP & NBT Configuration Parameters for Windows NT 3.5

FIX: OPENDSNT.DLL Makes Thread AV Server-Wide

Q132423 BUG# NT: 9175 (4.21a)

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

Q132498 BUG# NT: 9658 (4.21a)

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

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

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

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

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

Q131662 BUG# NT: 9992 (4.21a)

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

Q122486 BUG# NT: 932 (4.2)

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

Q124949 BUG# NT: 966 (4.21)

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

A long-running query that does not return results.

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

Q125636 BUG# NT: 959 (4.2)

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

Q111680 BUG# NT: 681 (4.2)

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

Q116075 BUG# NT: 814 (4.2)

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

Q124238 BUG# NT: 935 (4.21a)

If possible, shorten the name of the view.

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

Q124059 BUG# NT: 929 (4.21.006)

Additional query words: 4.21a Windows NT

Keywords :
Version : 4.21a
Platform : WINDOWS
Issue type :