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

ID: Q160732

The information in this article applies to:

• Microsoft SQL Server, version 6.5 Service Pack 2

The following is a list (Part 2 of 3) of fixes and other various improvements that have been made in Microsoft SQL Server version 6.5 Service Pack 2, now available from your primary support provider. For more information, contact your primary support provider.

Please note that workarounds described in these articles 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 SERVICE PACK 2 (Part 2 of 3)

SERVER COMPONENTS

Q140606: FIX: ORDER BY DESC Queries May Cause High Logical Reads Q149697: FIX: Numeric Datatype in Stored Proc Causes Connection Broken Q150891: FIX: LOAD HEADERONLY Doesn't Report Files Cont on Another Tape Q153542: FIX: Updates in Browse Mode Applications Can Cause AVs Q153693: FIX: SELECT from Table with >223 Columns Fails with Error 230 Q153917: FIX: SQL Terminates on Repeated Cursor Calls w/ ODS Handlers Q154164: FIX: TEXT Operations Can AV During DUMP DATABASE Q154627: FIX: 16-bit TCP/IP Fails to Connect With Long DNS Names Q154887: FIX: AV if Subquery GROUP BY and HAVING with ROUND() Q155182: FIX: AV Declaring a Cursor Involving Conversion to Bit Q155231: FIX: 100% CPU Spin Opening Cursor on a RECOMPILE proc.. Q155710: FIX: Select Statement Can Cause Arithmetic Overflow Q155714: FIX: Error Msg 1007 Occurs While Inserting Value '0. Q155815: FIX: SH_PAGE Locks Held on Text or Image Data in Service Pack 1 Q155816: FIX: Table Lock Escalation in Service Pack 1 Causes Error 1203 Q155825: FIX: Updates with Foreign Key Constraint Cause Exception Error Q155826: FIX: Browse Mode Updates with Check Constraint Causes Exception Q156287: FIX: Unable to Truncate a Table That Had a Constraint Defined Q156292: FIX: Create View with Nested SELECT in CASE Causes Error 206 Q156680: FIX: Access Violation with Extra SELECT in Aggregate Function Q156736: FIX: CASE Expression in WHERE Clause of SELECT May Cause AV Q156862: FIX: AV Assigning Decimal from VIEW to OUTPUT Parameter Q157570: FIX: Temp Table Stranded If Deadlock in Stored Proc. w/ Cursor Q157746: FIX: Error 8158 Caused by Create View Statement Q157809: FIX: AV Caused by View with Distinct and Correlated Subquery Q157846: FIX: Nested Cursors Can Cause a Handled Access Violation Q157847: FIX: Permission Denied Err 229 Updating Table in Another DB Q157981: FIX: AV from View with Group By and Incorrect Column List Q158234: FIX: Checkpoint Process Access Violation when Log Fills Q158269: FIX: AV Selecting View with Aggregate Subquery and Outer Join Q158288: FIX: Error 1203 Using Dynamic Cursor Within Transaction Q158290: FIX: Insert/Select May Cause Error 818 If IRL Is Enabled Q158335: FIX: Undetected Deadlock on System Catalogs During Dump Q158401: FIX: Selecting From Views With Nested Select Causes Errors

Below are excerpts from each of the articles listed above. For the full text of the articles, search for the article number in the Microsoft Knowledge Base.

FIX: ORDER BY DESC Queries May Cause High Logical Reads

ARTICLE-ID: Q140606 BUG #: 15670 (6.50)

SYMPTOMS

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

WORKAROUND

Restructure the query to use an intermediate temporary table such as the following:

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

   SELECT *
   FROM #tmp_inv
   ORDER BY inv_no DESC

FIX: Numeric Datatype in Stored Proc Causes Connection Broken

ARTICLE-ID: Q149697 BUG #: 14684 (6.50)

SYMPTOMS

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.

WORKAROUND

This problem goes away by creating the stored procedure with "with recompile."

You can also get a good execution by executing the stored procedure with "with recompile."

FIX: LOAD HEADERONLY Doesn't Report Files Cont on Another Tape

ARTICLE-ID: Q150891 BUG #: 15076 (6.50)

SYMPTOMS

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

ARTICLE-ID: Q153542 BUG #: 15587

SYMPTOMS

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.

WORKAROUND

Convert the application to use normal locking or cursors.

FIX: SELECT from Table with >223 Columns Fails with Error 230

ARTICLE-ID: Q153693 BUG #: 15610

SYMPTOMS

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:

   Msg 230, Level 14, State 1
   SELECT permission denied on column <column> of object <table>,
   database <db>, owner <owner>

WORKAROUND

Consider trimming the number of tables in the columns or use aliasing or different security schemas to allow data to be seen by all users.

FIX: SQL Terminates on Repeated Cursor Calls w/ ODS Handlers

ARTICLE-ID: Q153917 BUG #: 14803 (6.50)

SYMPTOMS

A DB-Library application making repeated cursor calls encounters one or more of the following errors:

   SQL Server message 1101, state 1, severity 17: unable to allocate new
   page for database 'tempdb'. There are no more pages available on valid
   allocation pages.

   space can be created by dropping objects, extending the database or
   dumping the log with no_log.

   DB-LIBRARY error:  General SQL Server error: Check error messages from
   SQL Server

WORKAROUND

Stop any ODS applications, such as SQL Trace, that may be running at the server. This causes the ODS handlers to be de-installed and prevents the server from terminating.

FIX: TEXT Operations Can AV During DUMP DATABASE

ARTICLE-ID: Q154164 BUG #: NT: 15703 (6.5) (sqlserver)

SYMPTOMS

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.

WORKAROUND

Schedule database dumps so that they do not execute while applications are using text operations such as dbwritetext, dbmoretext, or WRITETEXT.

FIX: 16-bit TCP/IP Fails to Connect With Long DNS Names

ARTICLE-ID: Q154627 BUG #: Windows NT: 15694

SYMPTOMS

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:

   Msg No.:10004     Severity: 9  State: 0
   Unable to connect: SQL Server is unavailable or does not exist.
   General network error. Check your documentation.

   OS Error: 11004      ConnectionOpen(gethostbyname())

WORKAROUND

To work around this problem, use shorter DNS names or use the IP address of the SQL server.

FIX: AV if Subquery GROUP BY and HAVING with ROUND()

ARTICLE-ID: Q154887 BUG #: (SQL 6.5) 15765

SYMPTOMS

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):

   Msg 913, Level 22, State 2
   Could not find row in Sysdatabases with database id 0.
   Run DBCC CHECKTABLE on Sysdatabases.

WORKAROUND

Re-code the query so that it does not fit the pattern.

FIX: AV Declaring a Cursor Involving Conversion to Bit

ARTICLE-ID: Q155182 BUG #: NT: 15771 (6.5)

SYMPTOMS

If you declare a non-insensitive cursor, which involves selecting a value converted to the bit datatype, a handled Access Violation occurs.

WORKAROUND

Do not convert to the bit datatype or declare the cursor as INSENSITIVE.

FIX: 100% CPU Spin Opening Cursor on a RECOMPILE proc.

ARTICLE-ID: Q155231 BUG #: SQL 6.5 15804

SYMPTOMS

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.

WORKAROUND

Change the procedure to not have WITH RECOMPILE.

FIX: Select Statement Can Cause Arithmetic Overflow

ARTICLE-ID: Q155710 BUG #: 15785 (6.50)

SYMPTOMS

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:

   a. an integer constant multiplied by
   b. an integer returned from a function multiplied by
   c. a decimal or numeric value in the form of either a constant, local
      variable or a column value.

   Condition 2 above.

WORKAROUND

To work around this problem, convert the decimal or numeric values to integer values using the Convert function. For more information on how to use the Convert function, please see the Transact-SQL Reference guide.

FIX: Error Msg 1007 Occurs While Inserting Value '0

ARTICLE-ID: Q155714 BUG #: Windows NT: 15866 (6.50)

SYMPTOMS

When you attempt to insert the value "0." (without quotation marks) into a column defined as numeric, float, or any other exact or approximate numeric datatype, the following error message appears:

   Msg 1007, Level 15, State 1:
   The number '0.' is out of the range for numeric representation
   (max precision: 33).

WORKAROUND

Enable trace Flag 107 for the server or any connection that needs to use this value in or against a column defined as float, decimal, numeric or real.

FIX: SH_PAGE Locks Held on Text or Image Data in Service Pack 1

ARTICLE-ID: Q155815 BUG #: Windows NT: 15870 (6.50)

SYMPTOMS

Table lock escalation, and subsequent blocking, can occur if a query selects text, or image columns and rows are searched with a unique index.

WORKAROUND

Increase the LE Threshold Maximum configuration value with sp_configure so that it takes more locks before the query escalates from page locks to table locks.

FIX: Table Lock Escalation in Service Pack 1 Causes Error 1203

ARTICLE-ID: Q155816 BUG #: Windows NT: 15871 (6.50)

SYMPTOMS

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.

WORKAROUND

To work around this problem:

1. Use non-unique index.

2. Use the optimizer locking hint "TABLOCK."

3. Increase the LE Threshold Maximum configuration value with sp_configure.

FIX: Updates with Foreign Key Constraint Cause Exception Error

ARTICLE-ID: Q155825 BUG #: 15716 (Windows NT: 6.50)

SYMPTOMS

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.

WORKAROUND

To work around this problem, either create a trigger to manage referential integrity (instead of using the Check constraint), or convert the application to use normal locking or cursors.

FIX: Browse Mode Updates with Check Constraint Causes Exception

ARTICLE-ID: Q155826 BUG #: Windows NT: 15715 (6.50)

SYMPTOMS

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.

WORKAROUND

To work around this problem, create a Rule and bind it to the column instead of using the Check constraint, or convert the application to use normal locking or cursors.

FIX: Unable to Truncate a Table That Had a Constraint Defined

ARTICLE-ID: Q156287 BUG #: 15405 (6.5)

SYMPTOMS

The TRUNCATE statement fails with the following error message:

   Msg 4712, Level 16, State 1
   Cannot truncate table '<tablename>' because it is being referenced by a
   foreign key constraint.

   Msg 439, Level 16, State 1
   Unable to find foreign key constraints for table '<tablename>' in dbid
   'x' though table is flagged as having them.

WORKAROUND

To work around this problem, do the following:

1. Back up the database.

2. Drop and re-create the table.

FIX: Create View with Nested SELECT in CASE Causes Error 206

ARTICLE-ID: Q156292 BUG #: 15817 (6.50)

SYMPTOMS

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:

   Msg 206, Level 16, State 2
   Operand type clash: UNKNOWN TOKEN is incompatible with varchar

WORKAROUND

Use the CONVERT function to convert the result of the SELECT statement to the same datatype as the other result expressions.

FIX: Access Violation with Extra SELECT in Aggregate Function

ARTICLE-ID: Q156680 BUG #: 15979 (SQL 6.5)

SYMPTOMS

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:

   This command did not return data, and it did not return any rows.
   DB-Library Process Dead - Connection Broken.

WORKAROUND

To work around this problem, remove the extra SELECT keyword.

FIX: CASE Expression in WHERE Clause of SELECT May Cause AV

ARTICLE-ID: Q156736 BUG #: 15877 (6.5)

SYMPTOMS

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:

   DB-Library Process Dead - Connection Broken

WORKAROUND

This problem only occurs if the variable into which the parameter values are passed to the stored procedure is used in the CASE expression of the SELECT statement. Therefore, the problem can be avoided by transferring the parameter value to another variable that is defined inside the stored procedure, and then using that second variable in the CASE expression. See the MORE INFORMATION section of this article for an example.

FIX: AV Assigning Decimal from VIEW to OUTPUT Parameter

ARTICLE-ID: Q156862 BUG #: 15860 (Windows NT: 6.5)

SYMPTOMS

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.

WORKAROUND

To work around this problem, do one of the following:

• Avoid assigning the decimal datatype OUTPUT parameter to a stored procedure.
• Avoid using views; select directly from the underlying tables.
• Instead of using OUTPUT parameters, return values from the stored procedure.
• Create a temporary table to hold the results of the SELECT statement. Then assign the results to OUTPUT parameters, selecting data from the temporary table.

ARTICLE-ID: Q157570 BUG #: 16037 (Windows NT, 6.50)

SYMPTOMS

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:

   SQL Server message 2714, state 1, severity 16:
   There is already an object named '#temp' in the database.

   SQL Server message 3701, state 1, severity 11:
   Cannot drop the table '#temp', because it doesn't exist in the system
   catalogs.

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.

WORKAROUND

To work around this problem, try to close out the connection on which the deadlock occurred, prior to resubmitting the command. It is not necessary to cycle SQL Server in order to clean up an object left stranded in this manner, because that process (and any associated resources) appear to be released once the creating process is closed.

FIX: Error 8158 Caused by Create View Statement

ARTICLE-ID: Q157746 BUG #: 15976 (SQL 6.5)

SYMPTOMS

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:

   Msg 8158, Level 16, State 1
   'View_name' has more columns than were specified in column name list.

WORKAROUND

To work around this problem, do one of the following:

• Correct the view to drop the column list.

  -or-

• Do not use either the correlated subquery or group by.

ARTICLE-ID: Q157809 BUG #: 15964 (SQL 6.5)

SYMPTOMS

Selecting from a view that contains a correlated subquery and a distinct clause can cause a thread level access violation (AV) in SQL Server.

WORKAROUND

Recode the query so that a distinct clause and a correlated subquery are not combined.

FIX: Nested Cursors Can Cause a Handled Access Violation

ARTICLE-ID: Q157846 BUG #: 15710 (6.50)

SYMPTOMS

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:

   16921 Cursorfetch: Must declare variable '%s'.

   16916 A cursor with the name '%s' does not exist.

WORKAROUND

Do not use cursors in a nested fashion where the inner cursor relies on the results of the outer cursor.

FIX: Permission Denied Err 229 Updating Table in Another DB

ARTICLE-ID: Q157847 BUG #: 16008 (6.50)

SYMPTOMS

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.

WORKAROUND

Do not use cursors to update tables when the cursor is in a stored procedure that is called by another procedure. Another workaround is to give the user permissions to the underlying table.

FIX: AV from View with Group By and Incorrect Column List

ARTICLE-ID: Q157981 BUG #: 15978 (SQL 6.5)

SYMPTOMS

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.

WORKAROUND

To work around this problem, do one of the following:

• Correct the view's column list.

  -or-

• Drop the column list.

ARTICLE-ID: Q158234 BUG #: 16060 (Windows NT: 6.5)

SYMPTOMS

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.

WORKAROUND

To work around this problem, do not use the database option "trunc. log on chkpt." Instead, do one of the following:

• Develop a procedure to regularly truncate your transaction log using the

   -or-

   Create view testview as
   select titles.title_id, totalcount = (select count(*) from titles) from

   Left Outer Join titleauthor On titles.title_id = titleauthor.title_id

   Error: 1203, Severity: 20, State: 2
   Caller of lock manager is incorrectly trying to unlock an unlocked
   object.
   spid=%d locktype=%d dbid=%d lockid=%Id

   Msg 818, Level 19, State 1
   There is no room to hold the buffer resource lock %S_BUF in SDES %S_SDES

   Msg 2805, Level 18, State 0
   Bad pointer 0x13a5388 encountered while remapping stored procedure
   'vw21'. Must re-create procedure.

   Msg 4401, Level 16, State 2
   View 'vw21' no longer exists.

Keywords          : kbother SSrvGen 
Version           : 6.5 SP2
Platform          : WINDOWS