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:
- DELETE the offending row.
- UPDATE the row and set the text column to NULL.
- 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.
