Microsoft KB Archive/160732

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

Article ID: 160732

Article Last Modified on 8/9/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Service Pack 2

-



This article was previously published under Q160732



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

SERVER COMPONENTS
Q140606: FIX: ORDER BY DESC Queries May Cause High Logical Reads

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

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
153542 BUG #: 15587

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:

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

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

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:

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)

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.

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:

 Calculation must involve at least three operands Calculation must include the following three operations:

 an integer constant multiplied by an integer returned from a function multiplied by a decimal or numeric value in the form of either a constant, local variable or a column value. </li> Calculation be performed in the order in which they are listed in Condition 2 above.</li></ol>

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:

Msg 1007, Level 15, State 1:

The number '0.' is out of the range for numeric representation (max precision: 33).

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:

Msg 4712, Level 16, State 1

Cannot truncate table ' ' because it is being referenced by a foreign key constraint.

Deleting from the table produces the following error:

Msg 439, Level 16, State 1

Unable to find foreign key constraints for table ' ' in dbid 'x' though table is flagged as having them.

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

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.

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

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:

SQL Server message 2714, state 1, severity 16:

There is already an object named '#temp' in the database.

Attempts to drop the temporary table prior to re-creating it will fail, and you will receive error message 3701:

SQL Server message 3701, state 1, severity 11:

Cannot drop the table '#temp', because it doesn't exist in the system catalogs.

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:

Msg 8158, Level 16, State 1

'View_name' has more columns than were specified in column name list.

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:

16921 Cursorfetch: Must declare variable '%s'.

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

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:

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

If Insert Row Level Locking (IRL) is enabled, using INSERT/SELECT may cause the following error message:

Msg 818, Level 19, State 1

There is no room to hold the buffer resource lock %S_BUF in SDES %S_SDES

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:

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.

<div class="workaround_section">

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 Note that this workaround should only be necessary in rare circumstances. Testing should be done to determine if the workaround uses fewer logical reads than the original query in your environment.

FIX: LOAD HEADERONLY Doesn't Report Files Cont on Another Tape
150891 BUG #: 15076 (6.50)

Convert the application to use normal locking or cursors.

FIX: SELECT from Table with >223 Columns Fails with Error 230
153693 BUG #: 15610

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
153917 BUG #: 14803 (6.50)

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
154164 BUG #: NT: 15703 (6.5) (sqlserver)

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
154627 BUG #: Windows NT: 15694

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
154887 BUG #: (SQL 6.5) 15765

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

FIX: AV Declaring a Cursor Involving Conversion to Bit
155182 BUG #: NT: 15771 (6.5)

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

FIX: 100% CPU Spin Opening Cursor on a RECOMPILE proc.
155231 BUG #: SQL 6.5 15804

Change the procedure to not have WITH RECOMPILE.

FIX: Select Statement Can Cause Arithmetic Overflow
155710 BUG #: 15785 (6.50)

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
155714 BUG #: Windows NT: 15866 (6.50)

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
155815 BUG #: Windows NT: 15870 (6.50)

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
155816 BUG #: Windows NT: 15871 (6.50)

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
155825 BUG #: 15716 (Windows NT: 6.50)

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
155826 BUG #: Windows NT: 15715 (6.50)

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
156287 BUG #: 15405 (6.5)

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
156292 BUG #: 15817 (6.50)

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
156680 BUG #: 15979 (SQL 6.5)

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

FIX: CASE Expression in WHERE Clause of SELECT May Cause AV
156736 BUG #: 15877 (6.5)

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
156862 BUG #: 15860 (Windows NT: 6.5)

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.

FIX: Temp Table Stranded If Deadlock in Stored Proc. w/ Cursor
157570 BUG #: 16037 (Windows NT, 6.50)

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
157746 BUG #: 15976 (SQL 6.5)

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.

FIX: AV Caused by View with Distinct and Correlated Subquery
157809 BUG #: 15964 (SQL 6.5)

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

FIX: Nested Cursors Can Cause a Handled Access Violation
157846 BUG #: 15710 (6.50)

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
157847 BUG #: 16008 (6.50)

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
157981 BUG #: 15978 (SQL 6.5)

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


 * Correct the view's column list. -or-


 * Drop the column list.

FIX: Checkpoint Process Access Violation when Log Fills
158234 BUG #: 16060 (Windows NT: 6.5)

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 DUMP TRANSACTION command. -or-


 * Extend your transaction log to avoid encountering a transaction log full

condition.

FIX: AV Selecting View with Aggregate Subquery and Outer Join
158269 BUG #: 16035

Replace Left Outer Join with "*=" to avoid the access violation.

FIX: Error 1203 Using Dynamic Cursor Within Transaction
158288 BUG #: 16082 (6.50)

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


 * Change the cursor definition to use either SCROLL or INSENSITIVE. -or-


 * Increase "LE Threshold Maximum" setting in sp_configure. -or-


 * Do not use transaction around Dynamic Cursor.

FIX: Insert/Select May Cause Error 818 If IRL Is Enabled
158290 BUG #: 16114 (6.50)

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


 * Disable IRL. -or-


 * Drop the clustered index on the table.

FIX: Undetected Deadlock on System Catalogs During Dump
158335 BUG #: 15796

Killing either the user process or the database dump process will resolve the deadlock and allow activity within the database to continue.

FIX: Selecting From Views With Nested Select Causes Errors
158401 BUG #: 16027

Drop and re-create the view after recycling server. The problem occurs as a result of the view resolution at server startup.

Additional query words: SP2 database patch

Keywords: kbfix kbother KB160732

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.