FIX: SQL Server 6.5 Service Pack 2 Fixlist (Part 2 of 3) (160732)

SYMPTOMS

A SELECT query containing an ORDER BY DESC clause can sometimes cause a high number of logical reads in SQL Server 6.0.

The second execution of a user-defined stored procedure causes the connection to be broken, if an exact numeric datatype is used in the compute clause of the procedure.

The Dump Database command can continue or span a dump across multiple tapes. If the Load Headeronly command is used to verify the dump, it will not report files that span tapes.

FIX: Updates in Browse Mode Applications Can Cause AVs

If a DB-Library browse mode client updates a row and the row has changed since the client selected the row, a handled exception of the client thread on the SQL Server can occur.

If a table is created with greater than 223 columns, and SELECT permissions are then granted to a user or group, then a subsequent SELECT, issued by a permitted user, can result in the following message being reported for each column defined after column 223 in the table structure: A DB-Library application making repeated cursor calls encounters one or more of the following errors: Attempting to dump a database while a dbwritetext, dbmoretext, or WRITETEXT is being executed on one of the tables in the database may cause a handled access violation (AV) followed by a SQL Server hang. A partial dump is created, and the loading header from the dump shows the dumpsize to be 0 (0 pages). It may not be possible to shut down SQL Server under these conditions. In order to restart SQL Server, you may have to restart Windows NT.

When you attempt to connect to a SQL Server using the 16 bit TCP/IP sockets network library with a fully qualified DNS name 20 characters or greater, the following error message appears: A select that contains a subquery combined with a GROUP BY and a HAVING clause with the function ROUND() results in the following error message and terminates with a thread level Access Violation (AV): If you declare a non-insensitive cursor, which involves selecting a value converted to the bit datatype, a handled Access Violation occurs.

The SQL Server process can go into a 100% CPU spin when a DB-Library or ODBC application opens a server cursor which executes a procedure created using the Recompile option.

In general, when you use the Select statement and a query that involves the multiplication of a decimal number and the value returned from a function, an Arithmetic Overflow error message appears. This error message also appears in the following three specific conditions:

1. Calculation must involve at least three operands
2. Calculation must include the following three operations:
   
   
   1. an integer constant multiplied by
   2. an integer returned from a function multiplied by
   3. a decimal or numeric value in the form of either a constant, local variable or a column value.
3. Calculation be performed in the order in which they are listed in Condition 2 above.

When you attempt to insert the value 0 into a column defined as numeric, float, or any other exact or approximate numeric datatype, the following error message appears: Table lock escalation, and subsequent blocking, can occur if a query selects text, or image columns and rows are searched with a unique index.

If a query's page locks on a table escalate to a table lock and the table is searched with a unique index, an Error 1203 occurs and terminates the client connection.

If a DB-Library browse mode client updates a row, and the table has a Foreign key constraint on any column, a handled exception error of the client thread on the computer running SQL Server occurs.

If a DB-Library browse mode client updates a row and the table has a Check constraint on any column, a handled exception of the client thread on the SQL Server occurs.

The TRUNCATE statement fails with the following error message: Deleting from the table produces the following error: If you create a view with a CASE statement, and one or more of the result expressions is a SELECT statement, the following error may occur: The extra SELECT keyword in the selection list of an aggregate function may cause a thread level access violation, and result in the following error message: A CASE expression in the WHERE clause of a SELECT statement may cause a handled access violation (AV).

The client's connection to the server is broken when the access violation occurs. The client will receive the following message: Other processes on the server are not affected.

Assigning a decimal value from a VIEW to an OUTPUT parameter of a stored procedure causes a handled access violation (AV). The client reports the AV message on SQL Server version 6.0, and appears to stop responding on SQL Server version 6.5. The errorlog contains the details of the access violation.

If a stored procedure is selected as the victim in a deadlock situation, a temporary table created within the procedure may become stranded in tempdb. This situation will only occur if, in addition to the temporary table, a cursor is declared within the stored procedure, and the stored procedure then acts upon the temporary table.

Because the standard method for handling a deadlock is to resubmit the command that was terminated, problems may arise if that command attempts to re-create the temporary table upon resubmission.

Specifically, error message 2714 may be reported when the query is resubmitted, as follows: Attempts to drop the temporary table prior to re-creating it will fail, and you will receive error message 3701: The existence of the temporary table can be confirmed by selecting from tempdb.sysobjects for that table.

This problem does not occur if a cursor is not used within the stored procedure. Thus, if a cursor is absent, the temporary table is correctly cleaned up from tempdb after a deadlock.

When you issue a create view statement with a view column list, a correlated subquery, and a group by clause, the SQL Server may incorrectly produce the following error message: Selecting from a view that contains a correlated subquery and a distinct clause can cause a thread level access violation (AV) in SQL Server.

A nested cursor fetch on basic select type cursors can cause a handled access violation, as well as the inability to locate locally defined variables or the cursor. The SQL Server error log will contain a Language Exec error, followed by a stack trace. The following errors, as well as a client disconnect, are reported on the client: A non-system administrator (SA) user will receive a Permission Denied error message (number 229) when he or she tries to update a table through a stored procedure, even if the user has permission to run the stored procedure. If the SA or database owner (DBO) runs the stored procedure first, the user will then be able to use the stored procedure. This problem occurs when the following sequence of events occurs:

1. Non-SA user runs stored procedure A.
2. Stored procedure A calls procedure B.
3. Procedure B updates a table through a cursor in which the table is located in another database.

Selecting from a view which contains a correlated subquery, a group by clause, and an extra column in the views column list may cause a thread level access violation (AV) in SQL Server.

On SQL Server 6.5 SP1 (6.00.213) Alpha platforms, the checkpoint process will encounter an access violation (AV) if both of the following are true:

• A database's transaction log becomes full between cycles of the checkpoint process. -and-
  
  
• The database has the option "trunc. log on chkpt" enabled.

This problem is specific to Alpha platforms, and does not occur on non- Alpha versions of Microsoft SQL Server.

When you select from a view with an aggregate subquery using an ANSI style outer join, you receive an access violation error message.

For example, the following view will cause this behavior:

   Create view testview as
   select titles.title_id, totalcount = (select count(*) from titles) from
titles
   Left Outer Join titleauthor On titles.title_id = titleauthor.title_id
				

Using Dynamic Cursor within a user-defined transaction can cause hundreds of 1203 errors in the SQL Server errorlog and Windows NT Event Viewer. The SQL Server shuts down afterwards, with the following error message: If Insert Row Level Locking (IRL) is enabled, using INSERT/SELECT may cause the following error message: An undetected deadlock can occur between a database dump and a user process that holds locks on syscolumns. When this deadlock occurs, all activity within the database is suspended until the deadlock is manually resolved.

Selecting from a view which has nested selects in its text may cause errors 2804 or 4401, if the SELECT is run after recycling the server. The text of each error message is:

   SELECT * INTO #tmp_inv
   FROM inv_table
   WHERE inv_no BETWEEN 100 AND 500

   SELECT *
   FROM #tmp_inv
   ORDER BY inv_no DESC