Microsoft KB Archive/108144

From BetaArchive Wiki

FIX: SQL Server FixList for Version 4.20b

Article ID: Q108144
Creation Date: 05-DEC-1993
Revision Date: 15-APR-1997 '
The information in this article applies to:

  - Microsoft SQL Server version 4.2b for OS/2

The following is a list of fixes and other various improvements that have been made in SQL Server version 4.2b. Version 4.2b includes server patches K6 through K11. SQL Server version 4.2b 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 4.20aK6:


  • FIX: 30-Byte Column Name Causes Error on Select Into
  • FIX: SELECT CONVERT(char(1), STUFF(,1,0,NULL)) GP Faults
  • FIX: Executing Stored Procedure Gives Error 707

Description of Problems Corrected in 4.20aK6:


FIX: 30-Byte Column Name Causes Error on Select Into


ARTICLE ID: Q93908 BUG# OS/2: 1522 (4.2)

SYMPTOMS


A column name in SQL Server may legally have up to 30 characters. However, if a column name is exactly 30 characters long, a select into a temporary table with the column will generate error 103:

   The identifier that starts with <column name> is too long. Maximum
   length is 30 characters.

For example, the following will generate a 103 error:

   create table longname
     (abcdefghijklmnopqrstuvwxyz1234 char(4))
   go
   select abcdefghijklmnopqrstuvwxyz1234 = '1234'
   into #temptab
   from longname
   go

CAUSE

SQL Server does not correctly identify a column name as legal if the column name is 30 characters long and referenced in a SELECT INTO statement that is selecting data into a temporary table.

WORKAROUND


Use a CREATE TABLE statement to create the temporary table, then an INSERT statement to insert the data:

   create table #temptab
     (abcdefghijklmnopqrstuvwxyz1234 char(4))
   go
   insert #temptab
     select abcdefghijklmnopqrstuvwxyz1234
     from longname

FIX: SELECT CONVERT(char(1), STUFF(,1,0,NULL)) GP Faults


ARTICLE ID: Q94567 BUG# OS/2: 1535 (4.2a)

SYMPTOMS


The following SELECT statement causes a general protection fault (GP fault):

   SELECT CONVERT(char(1), STUFF('',1,0,NULL))

CAUSE

SQL Server 4.2a does not handle the NULL correctly in this convert statement.

FIX: Executing Stored Procedure Gives Error 707


ARTICLE ID: Q94723 BUG# OS/2: 1544 (4.2)

SYMPTOMS


When executing a stored procedure, an error 707:

   system error detected when trying to free memory at address 0x%lx

or a protection violation may occur. WORKAROUND


To work around this problem, upgrade to SQL Server version 4.2aK6.

Problem Corrected in 4.20aK7:


  • FIX: System Cannot Be Restarted with a Full Master..Syslogs

Description of Problem Corrected in 4.20aK7: FIX: System Cannot Be Restarted with a Full Master..Syslogs


ARTICLE ID: Q95336 BUG# OS/2: 1559 (4.2)

SYMPTOMS


If a SQL Server is shutdown with a full master..syslogs, it may fail to restart. On the attempted restart, the recovery of the master DB may fail with an 1105 error on master..syslogs, the master DB is marked suspect, and SQL Server terminates.

CAUSE


During system initialization, SQL Server recovers all outstanding, uncompleted transactions in all databases. After completing the recovery, SQL Server attempts to write a checkpoint record to the database syslog. If the master..syslogs cannot accept this checkpoint record, SQL Server marks the master DB as suspect and terminates.

WORKAROUND


Sites with a version of SQL Server earlier than version 4.2aK7 must contact their primary support provider for instructions on restarting the system.

Starting with version 4.2aK7, SQL Server will complete its startup under these conditions. The sa user can then dump transaction master with no log to create space in the master..syslogs table.

List of Problems Corrected in 4.20aK8:


  • FIX: Table Insert from View Might GP Fault Server
  • FIX: Insert with LIKE and ORDER BY from System Tables
  • FIX: Select from a View with Recreated Tables May GP Fault

Description of Problems Corrected in 4.20aK8:


FIX: Table Insert from View Might GP Fault Server


ARTICLE ID: Q87481 BUG# OS/2: 1426 (4.2)

SYMPTOMS


SQL Server general protection faults (GP faults) when inserting into a table using a SELECT statement from a view defined as a two-table join, whose columns contain the aggregate function AVG().

The following is a sample query:

   create table t1(c int)
   create table t2(c int)
   insert t1 values(1)
   insert t2 values(1)
   go
   create view v (c, av) as
   select t1.c, avg(t1.c)
   from t1,t2
   where t1.c=t2.c
   group by t1.c
   go
   create table t3(c1 int, c2 int)    /* Note c2 is int */
   insert t3 select * from v          /* Insert AVG() result */
   go                                 /* into int column */

CAUSE

SQL Server incorrectly handles the automatic type conversions when using a view to insert into a table. Note that column c2 in table t3 is defined as int, but the result of AVG() should be float. The problem occurs when trying to insert the result of AVG() into the

int column.

WORKAROUND

The workaround is to declare the column that receives the AVG() result as a float. In the sample query, if you declare column c2 in table t3 as a float, the problem goes away.

FIX: Insert with LIKE and ORDER BY from System Tables


ARTICLE ID: Q88569 BUG# OS/2: 1451 (4.2) and 1452 (1.11)

SYMPTOMS


SQL Server may cause a general protection fault (GP fault) when a SELECT statement concatenates text characters onto a column value from a certain system table (sysobjects, sysdatabases, or sysdevices) in the master database and assigns the result to a local variable. The GP fault occurs when that SELECT statement contains a LIKE operator and an ORDER BY clause.

For example, the following SELECT statement causes a GP fault:

   DECLARE @t varchar(255)
   SELECT @t="AAA"+name
      FROM master..sysobjects
      WHERE name LIKE "syso%"
      ORDER BY name
   GO

CAUSE

SQL Server handles the insertion incorrectly when it inserts a value coming from a column value into a local variable while it is using the LIKE operator and the ORDER BY clause to obtain the value from certain system tables in the master database.

WORKAROUND


Break the single SELECT statement into two parts. For example, if you want to insert the last row of the qualified data rows into the local variable, break the query into these two parts:

  1. Insert the qualified rows into a temporary table without using the ORDER BY clause.
  2. Insert the rows into the local variable from the temporary table using the ORDER BY clause.

FIX: Select from a View with Recreated Tables May GP Fault


ARTICLE ID: Q93420 BUG# OS/2: 1529 (4.2a)

SYMPTOMS


When selecting from a view that is defined as a three table join, and if one of these tables has been recreated since the view was defined, SQL Server may general protection fault (GP fault).

For example, assume the view is defined as:

   create table A (id char(5))
   create table B (id char(5))
   create table C (id char(5))
   go
   create view V as
   select A.id from A,B,C
   where A.id=B.id and A.id=C.id
   go

If table C is dropped and recreated again after the view is defined (which is allowed), the query:

   select * from V

will cause SQL Server to GP fault. CAUSE


SQL Server incorrectly handles the queries on views that are defined with a three or more object join, if some of the objects are recreated after the views are defined.

WORKAROUND


The workaround is to drop and recreate the view.

List of Problems Corrected in 4.20aK9:


  • FIX: Error 511 when INSERT into Large Table
  • FIX: STR() Function with Length of 255 Causes GP Fault
  • FIX: Negative DPAGES May Cause an INSERT/SELECT to GP Fault
  • FIX: Client Not Notified when Locks Are Exceeded
  • FIX: UNION of Two SELECTs with Subqueries May Cause GP Fault
  • FIX: Uninitialized Variables in Empty Trigger Causes Trap
  • FIX: CONVERT() Does Not Handle Styles 13 and 113 Correctly
  • FIX: 511 on SELECT INTO with over 127 varchar(255)

Description of Problems Corrected in 4.20aK9:


FIX: Error 511 when INSERT into Large Table


ARTICLE ID: Q93237 BUG# OS/2: 1542 (4.2a)

SYMPTOMS


When a row is inserted into a table created with a large number of large varchar columns, you may encounter the following error:

   Updated or inserted row is bigger than the maximum size allowed for
   this table.
   (Msg 511, Level 16, State 2)

For example, if you create a table with 127 varchar(255) columns, the table will be created without any problems, but you will get the above error when you attempt to insert the row. A look at the row in sysindexes for the table will show a negative number for maxlen field.

WORKAROUND


Try creating the table with smaller size of the varchar columns or reduce the size of the table.

FIX: STR() Function with Length of 255 Causes GP Fault


ARTICLE ID: Q94421 BUG# OS/2: 1573 (4.2)

SYMPTOMS


Issuing a query that uses the STR() function to convert a floating point number to a string may cause SQL Server to general protection fault (GP fault).

CAUSE


SQL Server incorrectly handles the STR() function when a length of 255 is used. For example, the following query will cause a GP fault:

   select STR(0.1, 255, 5)
   go

If a length of less than 255 is used, the query works properly. FIX: Negative DPAGES May Cause an INSERT/SELECT to GP Fault


ARTICLE ID: Q98321 BUG# OS/2: 1588 (4.2)

SYMPTOMS


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

   INSERT tableA
     SELECT colA1
     FROM  tableA A,
        tableB B
     WHERE  A.colA1 = B.colB1

NOTE: The above query causes a GP fault only if the DPAGES column in SYSINDEXES for tableA is negative. WORKAROUND


Run DBCC CHECKTABLE on the table. This corrects the dpages count. Note that if DBCC CHECKDB is run regularly, the chances of running into this problem diminish.

FIX: Client Not Notified when Locks Are Exceeded


ARTICLE ID: \[\[..\/\|Q\]\] BUG# OS/2: 1597 (4.2)

SYMPTOMS


When SQL Server runs out of locks, the client application is not notified of the error. However, the following error is logged in the SQL Server ERRORLOG:

   SQL Server has run out of LOCKS. Re-run your command when there
   are fewer active users, or ask your System Administrator to
   reconfigure SQL Server with more LOCKS.
   (Msg 1204, Level 19, State 1)

CAUSE

SQL Server incorrectly handles the out of locks condition, and rather than sending the 1204 error to both the client and the SQL Server ERRORLOG, it only sends it to the ERRORLOG.

FIX: UNION of Two SELECTs with Subqueries May Cause GP Fault


ARTICLE ID: Q98323 BUG# OS/2: 1603 (4.2)

SYMPTOMS


The following statement may cause a general protection fault (GP fault) in SQL Server version 4.2:

   SELECT DISTINCT A.col1
     FROM tab   A
     WHERE 0 <> (SELECT SUM(col2)+SUM(col3)
          FROM tab B
          WHERE A.col1=B.col1)
   UNION
   SELECT DISTINCT A.col1
     FROM tab A
     WHERE 0 <> (SELECT  SUM(col2)+SUM(col3)
          FROM tab B
          WHERE A.col1=B.col1)

MORE INFORMATION

The problem occurs when the subquery returns more than one row. In that case, SQL Server should raise error message 512:

   Subquery returned more than 1 value. This is illegal when the
   subquery follows =, !=, <, <=, >, >=, or when the subquery is used
   as an expression

and terminate the query instead of attempting to evaluate the second SELECT involved in the UNION. FIX: Uninitialized Variables in Empty Trigger Causes Trap


ARTICLE ID: Q95802 BUG# OS/2: 1604 (4.2) and 1178 (1.11)

SYMPTOMS


When one or more local variables are declared in a trigger (for UPDATE, INSERT, or DELETE) and no value is assigned to any one of these local variables and there are no other statements defined within the trigger, SQL Server will TRAP D upon execution of the trigger. The TRAP D occurs after the trigger is created and then SQL Server is shutdown and restarted once.

After the trap occurs on the server, the client connections will be lost. PSTAT indicates that SQLSERVR.EXE is still running but all other attempts to log into SQL Server will fail until the process executing SQLSERVR.EXE is stopped and then SQL Server is restarted.

WORKAROUND


Add an additional valid statement to the trigger, or ensure that all variables are initialized to a valid value.

FIX: CONVERT() Does Not Handle Styles 13 and 113 Correctly


ARTICLE ID: Q96094 BUG# OS/2: 1616 (4.2)

SYMPTOMS


When converting a DATETIME data type to character with the Transact- SQL CONVERT() function, styles 13 and 113 do not convert as stated in the documentation.

CAUSE


In the "Language Reference" guide under CONVERT, it states that styles 13 and 113 should be in the form of:

   DD MMM YYYY hh:mi:ss:mmmm(24h)

However, style 13 comes out with the month as a number and the year as only the last two digits:

   DD MM YY hh:mi:ss:mmmm(24h)

and style 113 comes out with the month as a number:

   DD MM YYYY hh:mi:ss:mmmm(24h)

FIX: 511 on SELECT INTO with over 127 varchar(255)


ARTICLE ID: Q97426 BUG# OS/2: 1637 (4.2)

SYMPTOMS


A SELECT INTO that references a table with more than 127 varchar(255) columns will fail with the following error:

   Error 511:
   Updated or inserted row is bigger than maximum size (-nnnnn)
   allowed for this table.

(Where -nnnnn is a large negative number.) Assuming a table named "bigtab" has been defined with more than 127 varchar(255) columns, the following SELECT will get the 511 error:

   select * into newtab from bigtab

The table newtab will have been created and is fully functional, but no data rows will have been inserted. WORKAROUND


If a 511 error is detected on a SELECT INTO from a table with over 127 varchar(255) columns, a SELECT based INSERT can be used to copy over the data rows to the new table. For example:

   insert newtab select * from bigtab

List of Problems Corrected in 4.20aK10:


  • FIX: Correlated Subquery with Aggregate f'n Causes GP Fault
  • FIX: SELECT INTO Variable and Table May Trap Server
  • FIX: Incorrect Error Message if Device Unavailable
  • FIX: Join and Ordering of Bit and Text Results Traps Server
  • FIX: Store Procedure with UNION and ORDER BY Causes Errors
  • FIX: Uncommitted WRITETEXT May Cause 7902 Error
  • FIX: Network Error During RPC May Hang New Connections

Description of Problems Corrected in 4.20aK10:


FIX: Correlated Subquery with Aggregate f'n Causes GP Fault


ARTICLE ID: Q98322 BUG# OS/2: 1596 (4.2)

SYMPTOMS


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

   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.

FIX: SELECT INTO Variable and Table May Trap Server


ARTICLE ID: Q101810 BUG# OS/2: 1626 (4.2)

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. FIX: Incorrect Error Message if Device Unavailable


ARTICLE ID: Q101728 BUG# OS/2: 1641 (4.2)

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. FIX: Join and Ordering of Bit and Text Results Traps Server


ARTICLE ID: Q98165 BUG: OS/2: 1656 (4.2)

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.

CAUSE


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.

WORKAROUND


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

FIX: Store Procedure with UNION and ORDER BY Causes Errors


ARTICLE ID: Q98861 BUG# OS/2: 1676 (4.2)

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 may also be 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 that causes the server to 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

WORKAROUND

As a workaround, you can avoid the use of a 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

FIX: Uncommitted WRITETEXT May Cause 7902 Error


ARTICLE ID: Q99513 BUG# OS/2: 1681 (4.2) and 1682 (1.11)

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)

CAUSE

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

FIX: Network Error During RPC May Hang New Connections


ARTICLE ID: Q100958 BUG# OS/2: 1695 (4.2)

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.

List of Problems Corrected in 4.20aK11:


  • FIX: Join Between Two Tables with 1=2 in WHERE Clause
  • FIX: Multiple Procedures in Transaction Causes GP Fault
  • FIX: 803 Error on Select Based Insert
  • FIX: SELECT Local Variable with FOR BROWSE Drops Connection
  • FIX: Clients Hang Issuing Concurrent WRITETEXTs
  • FIX: SELECT INTO with Outer Join Can Trap Server
  • FIX: Update Trigger Fails After Upgrading to 4.20K8
  • FIX: Insert Into View Causes GP Fault
  • FIX: RPC Uses Case Sensitive Passwords
  • FIX: String Functions for Char Datatype in a View
  • FIX: Error 4305 Loading Log Dump
  • FIX: Trap on Insert with Select Union on Same Table
  • FIX: SHOWPLAN ON and SELECT INTO Involving UNION
  • FIX: Dividing by Negative Money Value Causes Timeslice Error
  • FIX: Index Incorrectly Chosen when Highest Key Value Used
  • FIX: INSERT INTO a Temporary Table Can Cause GP Fault

Description of Problems Corrected in 4.20aK11:


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


ARTICLE ID: Q96856 BUG# OS/2: 1639 (4.2a)

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.

FIX: Multiple Procedures in Transaction Causes GP Fault


ARTICLE ID: Q97955 BUG# OS/2: 1669 (4.2)

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.

FIX: 803 Error on Select Based Insert


ARTICLE ID: Q104967 BUG# OS/2: 1692 (4.2)

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.

FIX: SELECT Local Variable with FOR BROWSE Drops Connection


ARTICLE ID: Q104784 BUG# OS/2: 1705 (4.2)

SYMPTOMS


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

   10008 Bad Token

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 cases like this.

FIX: Clients Hang Issuing Concurrent WRITETEXTs


ARTICLE ID: Q104113 BUG# OS/2: 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.

FIX: SELECT INTO with Outer Join Can Trap Server


ARTICLE ID: Q100088 BUG# OS/2: 1713 (4.2a)

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:

  1. The first table has at least three columns, at least two of which are VARCHAR or CHAR.
  2. The first table has at least 580 rows of data.
  3. The second table has a least one CHAR and a MONEY column, zero or more rows of data, and zero or more indexes.
  4. The table selected into can be either a temp table or permanent.
  5. 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.
  6. 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

FIX: Update Trigger Fails After Upgrading to 4.20K8


ARTICLE ID: Q102416 BUG# OS/2: 1727 (4.2)

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.

FIX: Insert Into View Causes GP Fault


ARTICLE ID: Q102417 BUG# OS/2: 1729 (4.2)

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:

  1. create table t1
  2. create view v1 referencing table t1
  3. drop table t1
  4. insert into v1

WORKAROUND

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

FIX: RPC Uses Case Sensitive Passwords


ARTICLE ID: Q104785 BUG# OS/2: 1730 (4.2)

SYMPTOMS


When trying to initiate a Remote Procedure Call (RPC) 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.

FIX: String Functions for Char Datatype in a View


ARTICLE ID: Q101883 BUG# OS/2: 1734 (4.2)

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

FIX: Error 4305 Loading Log Dump


ARTICLE ID: Q104117 BUG# OS/2: 1741 (4.2) and 1740 (1.11)

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

FIX: Trap on Insert with Select Union on Same Table


ARTICLE ID: Q104101 BUG# OS/2: 1755 (4.2)

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

FIX: SHOWPLAN ON and SELECT INTO Involving UNION


ARTICLE ID: Q95132 BUG# OS/2: 1591 (4.2)

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.

FIX: Dividing by Negative Money Value Causes Timeslice Error


ARTICLE ID: Q95561 BUG# OS/2: 1574 (4.2)

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.

FIX: Index Incorrectly Chosen when Highest Key Value Used


ARTICLE ID: Q105347 BUG# OS/2: 1666 (4.2)

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.

MORE INFORMATION


The following script demonstrates the behavior.

use pubs go create table optim (

     number int      not null,
     filler char(10) not null)

go declare @counter int select @counter=1 while @counter < 768 begin

     insert optim values (@counter, 'some text')
     select @counter=@counter + 1

end go create nonclustered index number_index on optim (number) go set showplan on set statistics io on go select * from optim where number > 766 go select * from optim where number > 767 go select * from optim where number > 768 go

/* Remove the noexec on if you want to see the io    */
/* counts, but at least you will get to see the plan */
/* without getting 70 rows back.                     */

set noexec on go select * from optim where number between 700 and 766 go select * from optim where number between 700 and 767 go select * from optim where number between 700 and 768 go set noexec off go set showplan off set statistics io off drop table optim go FIX: INSERT INTO a Temporary Table Can Cause GP Fault


ARTICLE ID: Q105918 BUG# OS/2: 1760 (4.2)

SYMPTOMS


An INSERT INTO a temporary table can cause SQL Server to general protection 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.



Additional query words:
Keywords : SSrvGen kbfixlist
Version : 4.2b
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.

©1997 Microsoft Corporation. All rights reserved. Legal Notices.