Microsoft KB Archive/104835

FIX: SQL Server Fix List 4.20aK10

PSS ID Number: Q104835 Article last modified on 10-04-1994

4.20a

OS/2

The following is a list of fixes and other various improvements that have been made in SQL Server version 4.20aK10. SQL Server version 4.20aK10 is now available from Microsoft Products Support Services, please contact your primary support provider for more information.

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: Correlated Subquery with Aggregate f’n Causes GP Fault

ARTICLE ID: Q98322

BUG# 1596

= SYMPTOMS =

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

SELECT C.col1 FROM tab1 C, tab1 P WHERE P.col1 = C.col1 and P.col2 = C.col5 and P.col3 = ( SELECT max(col3) FROM tab1 WHERE col2 = C.col5 )

The query causes a GP Fault only if there is a clustered index on table tab1 which includes col2 as one of the indexed columns.

= WORKAROUND =

Run update statistics on the table and run the query again. If that does not help, drop the clustered index or replace it with a clustered index that does not include col2. Alternatively, the index could be replaced by a non-clustered index if appropriate.

= 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.2ak10.

=
=======================================================

FIX: SELECT INTO Variable and Table May Trap Server

ARTICLE ID: Q101810

BUG# 1626

= SYMPTOMS =

Executing a SELECT … INTO statement that uses invalid syntax by assigning results to a local variable may trap SQL Server.

The following example traps the server:

declare @db_num smallint select @db_num = max(dbid) into #temp from sysdatabases

= WORKAROUND =

The above syntax is invalid. The same effect can be achieved by using either of the following two sets of statements:

declare @db_num smallint select @db_num = max(dbid) from sysdatabases

or

select max(dbid) into #temp from sysdatabases

If you want to assign to a variable and send data to a new table, use both statements.

= 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.2ak10.

=
==========================================================

FIX: Join Between Two Tables with 1=2 in WHERE Clause

ARTICLE ID: Q96856

BUG# 1639

= SYMPTOMS =

A join of two large tables with 1=2 in the WHERE clause causes SQL Server to general protection fault (GP fault). Each of the tables has 100 int columns. The minimum configuration that can cause the trap is 99 rows in the first table and 233 rows in the second table. Deleting one row or column from either of the tables will not cause a GP Fault. Also, changing the datatype of the columns does not affect the behavior.

The following query causes the trap:

select t1.c1 from t1,t2 where t1.c50=t2.c51 and 1=2

SQL Server does not handle large table joins with 1=2 in the WHERE clause correctly.

= WORKAROUND =

If possible, break up large table into smaller tables with fewer columns, or reduce the number of rows in the table.

= 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.2ak10.

=
===========================================================

FIX: Incorrect Error Message if Device Unavailable

ARTICLE ID: Q101728

BUG# 1641

= SYMPTOMS =

If a database device is not available to SQL Server during startup, SQL Server should issue error 840:

Device ‘%. s’ (with physical name ’%. s’, and virtual device number %d) has not been correctly activated at startup time. Please contact the System Administrator.

Instead SQL Server issues error 822:

Could not start I/O for request %S_BLKIOPTR.

This condition usually occurs if the device’s .DAT file is deleted while SQL Server is not running. If the .DAT file resides on a network file server, it may occur if the SQL Server server loses its connection to the file server.

= 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.2ak10.

=
===========================================================

FIX: Join and Ordering of Bit and Text Results Traps Server

ARTICLE ID: Q98165

BUG# 1656

= SYMPTOMS =

When executing a query that involves a join and includes bit and text values in the results set, SQL Server traps. The trap may appear or disappear as rows are added, deleted, or modified in the table.

For certain distributions of data involving NULL text columns, a query using a join and returning bit and text data types may cause SQL Server to trap. The trap typically occurs when the last text value returned in the results set is NULL.

= RESOLUTION =

If NULL text columns must be used, ensure that bit and text values are not returned as the results of a single query.

= 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.2ak10.

=
===========================================================

FIX: Store Procedure with UNION and ORDER BY Causes Errors

ARTICLE ID: Q98861

BUG# 1676

= SYMPTOMS =

A stored procedure that contains a UNION clause where the first SELECT clause has an INTO and the second SELECT clause has an ORDER BY, when executed, will return the following messages to the application:

Internal error – Unable to open table at query execution time. (Msg 202, Level 11, State 1).

A transaction begun in this stored procedure that did updates in tempdb is still active. This will cause a corruption of tempdb that will exist until the server is rebooted. All BEGIN TRANs must have matching COMMITs or ROLLBACKs. (Msg 277, Level 16, State 1).

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. Previous count = 0, Current count = 1. (Msg 266, Level 16, State 1).

Message 611 is also sent to the errorlog:

Attempt made to end a transaction that is idle or in the middle of an update.

After one of these messages is generated, the transaction count is increased by one and issuing a COMMIT TRAN terminates the front end while issuing a ROLLBACK causes the server to general protection fault (GP fault).

For example, the following stored procedure will exhibit the problem:

create procedure testproc as select * into #work from authors union select * from authors order by address go execute testproc

= RESOLUTION =

As a workaround, you can avoid the use of UNION clause by using the temporary table created by the first SELECT clause to insert the results from the second SELECT clause. Finally, you can retrieve results from the temporary table using SELECT and ORDER BY clauses, like:

create proc testproc as select * into #work from authors insert #work select * from authors select * from #work order by address go execute testproc

= 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.2ak10.

=
===========================================================

FIX: Uncommitted WRITETEXT May Cause 7902 Error

ARTICLE ID: Q99513

BUG# 1682 (1.11), 1681 (4.20)

= SYMPTOMS =

After inserting TEXT data into a table, DBCC CHECKDB and/or CHECKTABLE on that table reports the following error:

Data size mismatch occurred while checking TEXT/IMAGE values. The first page for this value is: . There were xxx bytes found, which is different from the expected data length of 0 bytes. (Msg 7902, Level 16, State 1)

For this error to occur, a text column must first be updated and the text value set to NULL. Then, a WRITETEXT command is issued within a transaction to insert data into the text column which was just previously set to NULL. If that transaction is then rolled back, SQL Server does not correctly initializes the text length back to 0 bytes, and a DBCC CHECKTABLE or CHECKDB will report the 7902 error.

= WORKAROUND =

If this error is encountered, any one of the following methods can be used to correct it:


 * 1) DELETE the offending row.
 * 2) UPDATE the row and set the text column to NULL.
 * 3) Issue an INSERT or WRITETEXT to insert new data into the text column.

= 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.2ak10.

=
===========================================================

FIX: Network Error During RPC May Hang New Connections

ARTICLE ID: Q100958

BUG# 1695

= SYMPTOMS =

If a serious network error occurs while a site handler is active, and a Remote Procedure Call (RPC) is attempted across that site handler, future connections to the originating SQL Server may stop responding or hang. Current connections are unaffected. On some versions of SQL Server, one additional connection can be made before the new connections stop responding, and on others the problem starts with the next connection.

This problem can be replicated by disconnecting the network between two machines that have active site handlers. Initiate another RPC (such as server2…sp_who) and wait for the error to be reported. Now log in to the SQL Server that originated the RPC. If you are able to log in, log out and try again. Existing connections should be OK, however, every attempt to log in to the SQL Server should not respond.

= WORKAROUND =

If you are experiencing this problem, try setting the timeout parameter to False. This causes the site handler to quit after one minute of inactivity. This minimizes the amount of time the site handler is active. Because the problem only happens when a network error occurs, this may minimize the occurrence.

= 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.2ak10.

Additional reference words: 4.20aK10 KBCategory: kbref kbfixlist KBSubCategory: SSrvGen

=
================================================================

Copyright Microsoft Corporation 1994.