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 and later

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.

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

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.

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

Q153542 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 <column> of object <table>, database <db>, owner <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:

  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:
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 '<tablename>' 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 '<tablename>' 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.


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: Numeric Datatype in Stored Proc Causes Connection Broken

Q149697 BUG #: 14684 (6.50)
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

Q150891 BUG #: 15076 (6.50)
Convert the application to use normal locking or cursors.

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

Q153693 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

Q153917 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

Q154164 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

Q154627 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()

Q154887 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

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

Q155231 BUG #: SQL 6.5 15804
Change the procedure to not have WITH RECOMPILE.

FIX: Select Statement Can Cause Arithmetic Overflow

Q155710 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

Q155714 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

Q155815 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

Q155816 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

Q155825 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

Q155826 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

Q156287 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

Q156292 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

Q156680 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

Q156736 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

Q156862 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

Q157570 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

Q157746 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

Q157809 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

Q157846 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

Q157847 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

Q157981 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

Q158234 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

Q158269 BUG #: 16035
Replace Left Outer Join with "*=" to avoid the access violation.

FIX: Error 1203 Using Dynamic Cursor Within Transaction

Q158288 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

Q158290 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

Q158335 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

Q158401 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 : kbother SSrvGen
Version : winnt:6.5 Service Pack 2 and later
Platform : winnt
Issue type :


Last Reviewed: December 14, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.