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.
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 , 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:
- 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.
- 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:
- Non-SA user runs stored procedure A.
- Stored procedure A calls procedure B.
- 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.
Convert the application to use normal locking or cursors.
FIX: SELECT from Table with >223 Columns Fails with Error 230
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
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()
Re-code the query so that it does not fit the pattern.
FIX: AV Declaring a Cursor Involving Conversion to Bit
Do not convert to the bit datatype or declare the cursor as INSENSITIVE.
FIX: 100% CPU Spin Opening Cursor on a RECOMPILE proc.
Change the procedure to not have WITH RECOMPILE.
FIX: Select Statement Can Cause Arithmetic Overflow
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:
- Use non-unique index.
- Use the optimizer locking hint "TABLOCK."
- 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
To work around this problem, do the following:
- Back up the database.
- Drop and re-create the table.
FIX: Create View with Nested SELECT in CASE Causes Error 206
Use the CONVERT function to convert the result of the SELECT statement to the same datatype as the other result expressions.
To work around this problem, remove the extra SELECT keyword.
FIX: CASE Expression in WHERE Clause of SELECT May Cause AV
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
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
Recode the query so that a distinct clause and a correlated subquery are not combined.
FIX: Nested Cursors Can Cause a Handled Access Violation
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
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
To work around this problem, do one of the following:
- Correct the view's column list. -or-
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
Replace Left Outer Join with "*=" to avoid the access violation.
FIX: Error 1203 Using Dynamic Cursor Within Transaction
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
To work around this problem, do either of the following:
- Drop the clustered index on the table.
FIX: Undetected Deadlock on System Catalogs During Dump
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
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
