Microsoft KB Archive/119772

{|
 * width="100%"|

FIX: SQL Server FixList for Version 4.20bK15
'Article ID: Q119772

Creation Date: 23-AUG-1994

Revision Date: 30-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.2bK15 for OS/2. SQL Server version 4.2bK15 is now available from your primary support provider. For more information, please 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.20bK15


 * FIX: Repeated Exec of Stored Proc May GP Fault the Server
 * FIX: Default Using Global Variable GP Fault the Server
 * FIX: Multiple Procedures in Transaction Causes GP Fault
 * FIX: SELECT INTO with Outer Join Can GP Fault the Server
 * FIX: Column Mismatch on SELECT-UNION INSERT May Cause GP Fault
 * FIX: SELECT with Outer Join and a GROUP BY Can GP Fault Server
 * FIX: SELECT with More Than 125 SUM Columns Can GP Fault Server
 * FIX: INSERT INTO a Temporary Table Can Cause GP Fault

FIX: Repeated Exec of Stored Proc May GP Fault the Server

ARTICLE ID: Q94596 BUG# OS/2: 1562 (4.2)

SYMPTOMS

A stored procedure doing a select in conjunction with the count and distinct functions may cause a general protection fault (GP fault) on SQL Server. Furthermore, the stored procedure may have executed multiple times on the server without its actually bringing the server down every time.

The stored procedure definition must be like the following:

create procedure test_proc as  select count(distinct  column1+column2) from table1 where ... (Assume that both column1 and column2 are defined as char(2).) WORKAROUND

To avoid potential GP faults with the above stored procedure, it may be recoded by selecting into a temporary table and then performing the "distinct" on the temporary table (the 'distinct column1+column2' is the source of the problem):

create procedure test_proc as  begin

create table #temp_sum (sum1 char(4))

insert into #temp_sum select column1+column2 from table1

select count(distinct sum1) from #temp_sum end FIX: Default Using Global Variable GP Faults the Server

ARTICLE ID: Q97147 BUG# OS/2: 1648 (4.2)

SYMPTOMS

When inserting records into a table and using a default to fill in a column with a value derived from a system variable, SQL Server experiences a general protection fault (GP fault).

CAUSE

If a default of the following form is used to fill in values in a column, the server will GP fault:

create default problem_default as @@spid If values are specified for the column so that the default is not used to insert a value into the field, a GP fault will not occur. A default that produces a value derived from a system variable, such as  create default problem_default as convert( char(10), @@spid ) will produce a GP fault. WORKAROUND

If you want a record of the current @@spid, a trigger should be created on the table to update the inserted records with the desired @@spid. If you want to make record of the user inserting the record, you can use system functions such as USER_ID, SUSER_ID, and HOST_ID within a default to identify the creator.

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: SELECT INTO with Outer Join Can GP Fault 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 general protection fault (GP fault) 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 GP fault 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: Column Mismatch on SELECT-UNION INSERT May Cause GP Fault

ARTICLE ID: Q100700 BUG# OS/2: 1709 (4.2)

SYMPTOMS

A column mismatch in the two SELECTs making up a UNION in a SELECT-based insert will cause the server to general protection fault (GP fault). This should only generate a syntax error for the user issuing the INSERT.

The following command causes the server to GP fault:

INSERT INTO table1 (month_date, year_date ) SELECT t2.month_d, t2.year_d FROM table2 t2  UNION SELECT t3.month_d      /* missing year_d */ FROM table3 t3  go NOTE: This syntax error in the UNION column lists only causes of the GP fault if the UNION is in an INSERT. Running the UNION by itself generates a syntax error. WORKAROUND

Correct the column lists and run the query again.

FIX: SELECT with Outer Join and a GROUP BY Can GP Fault Server

ARTICLE ID: Q103679 BUG# OS/2: 1749 (4.2)

SYMPTOMS

A SELECT statement with an OUTER JOIN and a GROUP BY involving two tables can cause a general protection fault (GP fault) with the following error message:

SYS1943: A Program Caused a Protection Violation. The first table should have three columns, at least two of which should be VARCHAR or CHAR. The first table should have at least 580 rows. The second table should have at least one CHAR and a FLOAT column and zero or more rows. There should be at least one outer join between the tables. The group by should contain a minimum of at least two CHAR or VARCHAR columns from the first table and the float column from second table. NOTE: The error occurrence is data specific and cannot be reproduced through random set of data. MORE INFORMATION

Steps to Reproduce Problem

use pubs go  dump tran pubs with no_log 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 float) go

Select c2, c3, c5  from t1, t2  where t1.c1 *= t2.c4  group by       c2, c3, c5   go FIX: SELECT with More Than 125 SUM Columns Can GP Fault Server

ARTICLE ID: Q103748 BUG# OS/2: 1757 (4.2a)

SYMPTOMS

A SELECT statement with more than 125 SUM columns of INT NULL will cause SQL Server to get a general protection fault (GP fault).

Example ---  select sum(c2), sum(c3), sum(c4), ... sum(c124), sum(c125), sum(c126) from t1  group by c1 WORKAROUND

To avoid this problem, you can split the table into multiple tables and do a join, or you can do an inner join on the single table.

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 : SSrvServer kbfixlist

Version : 4.2b 4.2bK15

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.

''