FIX: SQL Server Fix List 4.20aK10
  
PSS ID Number: Q104835
Article last modified on 10-04-1994
 
4.20a
 
OS/2
 

----------------------------------------------------------------------
The information in this article applies to:
 
  - Microsoft SQL Server version 4.2aK10 for 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: <page #>. 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.
