Microsoft KB Archive/106503

FIX: SQL Server FixList for Version 4.20aK11

Article ID: Q106503
Creation Date: 09-NOV-1993
Revision Date: 15-APR-1997 '
The information in this article applies to:

  - Microsoft SQL Server version 4.2aK11 for OS/2

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

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

ARTICLE ID: \[\[..\/\|Q\]\]

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

CAUSE

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 version 4.2a. This problem was corrected in SQL Server version 4.2aK11.

FIX: Multiple Procedures in Transaction Causes GP Fault

ARTICLE ID: \[\[..\/\|Q\]\]

BUG# 1669

SYMPTOMS

Executing a user-defined transaction that calls multiple stored procedures that do inserts, updates, or deletes to tables may cause a general protection fault (GP fault) in SQL Server. The SQL Server errorlog may contain the following warning just prior to the GP fault:

   Warning: OPEN OBJECTS parameter may be too low; attempt was
   made to free up descriptors in localdes(). Run sp_configure to
   increase parameter value.

CAUSE

SQL Server incorrectly handles the execution of stored procedures that do updates within a user-defined transaction when the value of open objects is low.

WORKAROUND

To workaround this problem, reduce the number of stored procedures that are being executed within the user-defined transaction. If that option is not feasible, use the sp_configure system procedure to increase the value for open objects. This may resolve the GP faults, or may lessen the frequency of them.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2. This problem was corrected in SQL Server version 4.2aK11.

FIX: 803 Error on Select Based Insert

ARTICLE ID: \[\[..\/\|Q\]\]

BUG# 1692

SYMPTOMS

Under certain rare circumstances, a select based insert can receive an 803 error:

   Unable to place buffer 0x%ld holding logical page %ld in
   sdes for object '%s' - either there is no room in sdes or
   buffer already in requested slot.

Under rare circumstances, an insert of the form: insert table803

  select * from source_table

on a table with two or more non-clustered indexes can result in an 803 error. This error is very dependent on the data in the indexes at the time the operation is attempted. WORKAROUND

This condition may sometimes be addressed by dropping any non-clustered indexes on the table, then retrying the operation. If it succeeds, the indexes can be recreated and normal operations continued.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2. This problem was corrected in SQL Server version 4.2aK11.

FIX: Select Local Variable with for Browse Drops Connection

ARTICLE ID: \[\[..\/\|Q\]\]

BUG# 1705

SYMPTOMS

A SELECT statement with an assignment to a local variable in the SELECT clause and a FOR BROWSE clause may disconnect the user's connection with an error:

   Error 10008 Bad Token from SQL Server

or may trap the server. This only happens when a unique index exists on the table being selected from. For example:

     DECLARE @ordnum int
     SELECT @ordnum = OrderNumber FROM SaleOrder
        WHERE OrderNumber = 1
        FOR BROWSE

WORKAROUND

Do not use the FOR BROWSE clause in a Transact SQL statement when values are being selected into local variables. The FOR BROWSE clause is designed to be used with DB-Library applications, and is not designed to be used in situations like this.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2. This problem was corrected in SQL Server version 4.2aK11.

FIX: Clients Hang Issuing Concurrent WRITETEXTs

ARTICLE ID: \[\[..\/\|Q\]\]

BUG# 1711 (4.2) and 1712 (1.11)

SYMPTOMS

When multiple clients are inserting or updating text and/or image data in a table via WRITETEXT, as well as selecting from that table, one or more of the clients may hang. Issuing an sp_who shows the hung client(s) having a state of Awaiting Command, and sp_lock shows that no locks are preventing the client(s) from continuing. New connections can be established to SQL Server, indicating SQL Server itself is not hung.

CAUSE

Under certain conditions, depending on where in the table the concurrent clients are updating and selecting data, SQL Server may cause the client application to hang indefinitely. This can happen with both the logged and non-logged syntax of WRITETEXT.

STATUS

Microsoft has confirmed this to be a problem in SQL Server versions 1.11 and 4.2. This problem was corrected in SQL Server version 4.2aK11.

FIX: SELECT INTO with Outer Join can Trap Server

ARTICLE ID: \[\[..\/\|Q\]\]

BUG# 1713

SYMPTOMS

Using the SELECT INTO command with an outer join between at least two tables and a GROUP BY that includes columns from both tables including a money type from the second table can cause SQL Server to trap with the following SYS1943 error message:

   A protection violation has occurred.

The minimum conditions required for replication are the following:

The first table has at least three columns, at least two of which are VARCHAR or CHAR.
The first table has at least 580 rows of data.
The second table has a least one CHAR and a MONEY column, zero or more rows of data, and zero or more indexes.
The table selected into can be either a temp table or permanent.
The GROUP BY clause must contain at a minimum the two VARCHAR or CHAR columns from the first table and the money column from the second table.
There must be an outer join (t1.c1 *= t2.c1) in the WHERE clause, but it does not appear to matter which columns it is on.

Following is a minimal example of the table structures and query required to trap the server. You will need to have at least 580 rows of data in t1 to replicate the error.

   /*****************************************************/
   /*  Set up the two tables                            */
   /*****************************************************/

   use pubs
   go

   drop table t1
   go

   create table t1
   (c1        char(2) null,
    c2        varchar(10) null,
    c3        varchar(10) null)
   go

   drop table t2
   go

   create table t2 (c4 char(2),
                    c5 money)
   go

   /**************************************************/
   /*  The query                                     */
   /**************************************************/
   /*  Need to insert data or query will not crash.  */

   select c2, c3, c5
   into #temp_snapshot
   from
       t1, t2
   where
       t1.c1 *= t2.c4
   group by
       c2, c3, c5
   go

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2a. This problem was corrected in SQL Server version 4.2aK11.

FIX: Update Trigger Fails After Upgrading to 4.20K8

ARTICLE ID: \[\[..\/\|Q\]\]

BUG# 1727

SYMPTOMS

After upgrading to SQL Server version 4.20k8, an update trigger may fail with the following type of message:

   Invalid column name ''.
   (Msg 207, Level 16, State 3).
   Invalid column name ''.
   (Msg 207, Level 16, State 3).
   Invalid column name ''.
   (Msg 207, Level 16, State 3).

The column names contain indecipherable characters. This can also happen if a dump made on a server running a version earlier than 4.20k8 is loaded into a server running 4.20k8. WORKAROUND

Drop and recreate the UPDATE trigger to avoid this problem.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2. This problem was corrected in SQL Server version 4.2aK11.

FIX: Insert into View Causes GP Fault

ARTICLE ID: \[\[..\/\|Q\]\]

BUG# 1729

SYMPTOMS

An insert into a view after dropping an underlying table may cause a general protection fault (GP fault). A typical scenario would be as follows:

create table t1
create view v1 referencing table t1
drop table t1
insert into v1

WORKAROUND

Make sure you recreate all underlying tables before an INSERT into a view.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2. This problem was corrected in SQL Server version 4.2aK11.

FIX: RPC Uses Case Sensitive Passwords

ARTICLE ID: \[\[..\/\|Q\]\]

BUG# 1730

SYMPTOMS

When trying to initiate a Remote Procedure Call from one untrusted server to another, you get error message:

   7221, Login to site server2 failed.

Other logins on the same server, however, can use the same RPC with no problems. CAUSE

When SQL Server initiates an RPC between two servers who are not in a trusted relationship, the password that the user logged on with will be compared to the password for that id on the remote machine. The comparison of the two passwords will be a character for character comparison. Even if the two servers have been installed with a case insensitive sort order, the comparison of the passwords during the RPC call will be case sensitive.

WORKAROUND

Ensure that the passwords on each server are the same case, even though the servers may be case insensitive. When logging in, ensure that your password is entered in the same case as the password on the server.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2. This problem was corrected in SQL Server version 4.2aK11.

FIX: String Functions for Char Datatype in a View

ARTICLE ID: \[\[..\/\|Q\]\]

BUG# 1734

SYMPTOMS

Using string functions RTRIM, LOWER, UPPER, LTRIM, and Reverse on a

char datatype column of a View returns the result as padded with

blanks to be 256 character in length. Datalength function does return correct length. This does not happen for varchar datatype column in the view. Using the base table instead of the view also returns correct results.

For example, in the following script, function RTRIM returns results padded with blanks to be 256 characters in length.

   create table T1 (textdat char(30))
   go
   insert into T1 (textdat) values ('a')
   insert into T1 (textdat) values ('ab')
   go
   create view VT1 (textdat) as select textdat from T1
   go
   select rtrim(textdat) from VT1 /* incorrect */
   go

WORKAROUND

Note that the problem does not occur on the varchar datatype columns in a view. Therefore, you may modify your view creation statement as:

   create view VT1 (textdat) as
      select convert(varchar(30),textdat) from T1

This causes the results to be correctly returned for the string functions. Alternately, you can also convert the char value to a varchar before applying the rtrim or other string functions; for example:

   select rtrim(convert(varchar(30),textdat)) from VT1

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2. This problem was corrected in SQL Server version 4.2aK11.

FIX: Error 4305 Loading Log Dump

ARTICLE ID: \[\[..\/\|Q\]\]

BUG# 1740 (v1.11), 1741 (v4.20)

SYMPTOMS

Loading a transaction log dump may fail with the following 4305 error:

   Specified file <dump file> is out of sequence. Current time stamp
   is <date/time 1> while dump was from <date/time 2>.
   (Msg 4305, Level 16, State 1)

CAUSE

SQL Server incorrectly allows a DUMP TRANsaction command to be issued while inside a user-defined transaction. If after dumping the transaction log, the user-defined transaction is rolled back, any attempt to later load that transaction log dump will fail with the 3305 error.

WORKAROUND

Do not issue a DUMP TRANsaction command while you are inside of a user-defined transaction. To verify this, issue the following command:

   select @@trancount

If this returns any value other than zero, your connection is inside of a transaction, and the transaction should either be committed or rolled back before issuing the DUMP TRANsaction command. STATUS

Microsoft has confirmed this to be a problem in SQL Server versions 1.11 and 4.2. This problem was corrected in SQL Server version 4.2aK11.

FIX: Trap on Insert with Select Union on Same Table

ARTICLE ID: \[\[..\/\|Q\]\]

BUG# 1755

SYMPTOMS

An insert with a select union on the same table will trap the SQL Server. The following will cause a general protection fault (GP fault) in SQL Server:

   create table testtrap (cola int)
   go
   insert testtrap select * from testtrap union select * from
   testtrap

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2. This problem was corrected in SQL Server version 4.2aK11.

FIX: SHOWPLAN ON and SELECT INTO Involving UNION

ARTICLE ID: \[\[..\/\|Q\]\]

BUG# 1591

SYMPTOMS

Under SQL Server 4.2 and 4.2a, attempting to execute a SELECT INTO statement using the UNION operator will cause the server to general protection fault (GP fault) if SHOWPLAN is set to ON. This behavior is independent of any WHERE or ORDER BY clauses within the SELECT statements.

WORKAROUND

By setting SHOWPLAN OFF, the SELECT INTO statement will execute without causing a GP fault.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2. This problem was corrected in SQL Server version 4.2aK11.

FIX: Dividing by Negative Money Value Causes Timeslice Error

ARTICLE ID: \[\[..\/\|Q\]\]

BUG# 1574

SYMPTOMS

When dividing by certain negative money values, the operation may cause SQL Server to generate a timeslice error:

   timeslice -1501, current process infected.

SQL Server will temporarily lock up until the timeslice is detected. At that time, the process is terminated and SQL Server resumes as normal. Note: Timeslice errors may cause SQL Server version 1.11 to general protection fault (GP fault) (bug# 1574/1575). CAUSE

SQL Server does not correctly handle division of certain negative money values. Values that are know to cause problems are -$6.53, -$6.54, and -$6.55.

STATUS

Microsoft has confirmed this to be a problem in SQL Server versions 1.11 and 4.2. This problem was corrected in SQL Server version 4.2aK11.

FIX: Index Incorrectly Chosen when Highest Key Value Used

ARTICLE ID: \[\[..\/\|Q\]\]

BUG# 1666

SYMPTOMS

A query that uses the highest key value of an index runs very slowly. Using showplan indicates that the optimizer is not using the index when it should, or using it when it should not.

This only happens when the high value is used in a greater than comparison (>), or in the high value of a between statement. Using the highest key in an equality test (=), or a value one above or below will show the index being used correctly.

CAUSE

If the highest key value is used in a greater than statement, the optimizer will incorrectly not use a beneficial index, and perform a table scan instead.

If the highest key value is used as the high value in a between statement, the optimizer will incorrectly use an index when it should do a table scan. Using the index, in this case, causes more page I/Os to be performed than would be in a table scan, causing the query to run much longer than it should.

WORKAROUND

For lookup tables and other tables in which the data content is known beforehand, make sure that the highest value is not used in the comparison operator.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2. This problem was corrected in SQL Server version 4.2aK11.

FIX: INSERT INTO a Temporary Table Can Cause GP Fault

ARTICLE ID: \[\[..\/\|Q\]\]

BUG# 1760

SYMPTOMS

An INSERT INTO a temporary table can cause SQL Server to general Pprotection fault (GP fault).

CAUSE

The following circumstances are required in order to receive the GP fault:

- The INSERT INTO the temporary table must be within a stored procedure.

- You must be inserting into the temporary table using a SELECT statement

  that returns at least part of the temporary table in question.

- It will only occur with specific datasets. STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2. This problem was corrected in SQL Server version 4.2aK11.

Additional query words: 4.20aK11
Keywords : SSrvGen kbfixlist
Version : 4.2a
Platform : OS/2
Issue type : kbref

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.