The information in this article applies to:
The following is a list of fixes and various other improvements that have been made in the Microsoft SQL Server version 6.0 Service Pack 3. Service Pack 3 is now available from your primary support provider. For more information, contact your primary support provider. Service Pack 3 includes all fixes previously released in Service Packs 1 and 2 for version 6.0. The list of fixes for Service Packs 1 and 2 are appended at the end of this article. Please note that workarounds described 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 3DB-Library ComponentsReplication ComponentsServer ComponentsQ151301: dbcursorfetchex() Can Cause Blocking in DB-LibraryBUG#: 15039SYMPTOMS
If a DB-library application calls dbcursorfetch() or dbcursorfetchex()
successfully in one thread, subsequent calls to dbcursoropen() or dbclose()
from other threads using the same DBPROCESS would be blocked. Sp_who and
sp_lock show no blockage on the server side, and the DB-library application
would appear to hang.
Long published and subscribed database names can cause an access violation (AV) failure of the distribution task with the following error reported in the Event Log:
The Task History for the distribution task will report "Failure" with no message explanation and the task will no longer be Active. On Microsoft SQL Server version 6.0, you can not perform a SELECT INTO from a table that has been ALTERed to add an IDENTITY column. A SELECT INTO will fail with the following error message:
The @@identity global variable will be NULL under the following conditions:
NOTE: This problem only occurs under rare conditions, appears to be extremely data and hardware sensitive, and is not likely to occur with most CREATE INDEX statements. Database dumps to remote devices and the xp_loginconfig extended stored procedure can fail with access denied errors if Microsoft SQL Server is using Per Seat licensing mode. For database dumps, the client will get a 3201 error and the Windows NT Application log of the computer SQL Server is running on will contain the following event:
When running xp_loginconfig, the query will return the message:
When you declare a cursor on a view with DISTINCT, the operation will cause a handled access violation (AV) resulting in the client process being terminated. The same behavior can occur in both Transact-SQL and in applications written to use server cursors. The behavior only occurs if the table has a unique index. If a text value of length 256 bytes is passed to a stored procedure as a parameter and in the stored procedure the DATALENGTH function is used to check the length of the parameter, it will return 0 instead of 256. If the text parameter's length is either greater than 256 or less than 256, the DATALENGTH function will return the correct length. Q147177: FIX: Complex T-SQL Batch May Hang During Query Plan GenerationBUG# NT: 13132The complex transact-SQL batch hangs when executed in SET NOEXEC ON mode. The transact-SQL query batch has two inserts with a co-related update statement. Execution with SET NOEXEC ON causes the connection to be stranded with CPU utilization at 100 percent. Execution with SET NOEXEC OFF causes the connection and locks to be stranded with CPU utilization at 100 percent. When running DBCC CHECKDB or CHECKTABLE, SQL Server may encounter an unhandled exception. The last message in the errorlog prior to the exception will be the following:
If you are running Service Pack 2, the above message will be followed by:
Fetching from multiple updatable cursors using VIEWs of the same underlying table can get a handled exception. A SELECT from multiple base tables with no join clauses may cause a handled Access Violation in Microsoft SQL Server 6.0 Global temporary tables exhibit inconsistent IF EXISTS behavior from multiple connections. The conditional check for table (with IF EXISTS) may return FALSE even if the global temp table exists. Create table statements executed after this will return the following error message:
Q148267: FIX: Correlated Subquery Nested 3+ Levels May Give Error 403BUG# NT: 13721A correlated subquery nested to three levels or more may give the following error message:
During object resolution, the server terminates the client process if the object resolution is not successful for five consecutive attempts. This applies only to version 6.0 Service Pack 2. The problem characteristics are completely different on SQL Server version 6.0 without any Service Packs installed. Please see the following Knowledge Base article for details: Q134659 FIX: Unexpected Behavior During Object Resolution Running an UPDATE on a table results in more EX_PAGE locks when the table contains more than 15 indexes, even when the column being updated is not a part of any index. The same set of statements uses a smaller number of locks if there are 15 or fewer indexes on the updated table. Q152046: FIX: DBCC Traceon(3604,4031) Causes Unhandled Access ViolationBUG#: 13262Using the combination of trace flags 3604 (Sends trace output to the client) and 4031 (Prints both a byte and ASCII representation of the send buffers) can crash the server with an unhandled access violation. For example: The client thread may access violate if it has the datetime column in correlation, and multiple conditions in the WHERE clause of the correlated subquery and the main outer query. Applications attempting to update a view through SQL Server server cursors will receive the following error even if the operation observes the SQL Server requirement that updates through views only update a single base table:
If a stored procedure is executed after setting the TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED, the procedure can fail with the following error:
Under certain conditions, you may receive 4409 errors. A SQL Server tape dump may not be usable on a similar tape drive if the two tape drives do not support the same tape marking mechanisms. The system stored procedure sp_processmail can fail to reply to a message sent to the server if the sender uses an ambiguous e-mail address. An insert performed on a view with the option DEFAULT VALUES can result in a thread level Access Violation. The following provides an example where the Access Violation would occur: An INSERT into a table with a UNIQUE CLUSTERED INDEX can fail with fatal error 3307:
INSERTing to a table from a UNION of two or more SELECT statements can result in a handled Access Violation. It is possible to experience a handled access violation (AV) when using an IN condition with a sub-query. The time a query takes to produce output can be too long when using join(s) between two or more tables and a GROUP BY ALL. The scan count and physical IO count are extremely high. The tables involved in the join have lots of duplicate data in the column involved and does not have any indexes. Testing has shown that the situation under which this problem has been encountered is narrow in scope, and the problem can be reproduced only with the provided replication data. A GRANT or REVOKE statement can report the following error when used within the context of a stored procedure. The error is encountered when you attempt to create an object and then grant permission within the same procedure.
In an UPDATE statement within a stored procedure, if a subquery is used to set the value of a column and includes one or more aggregate functions with arithmetic operations, and the arithmetic operation references a column, then a handled thread level access violation may occur. A five-way (or more) table join with a search clause introduced by an OR clause may cause handled exception which terminates the client thread. The absence of a comma between columns in the SELECT list may cause a handled AV under some narrow circumstances. If 'Insert table select * from table (NOLOCK) is called within a transaction, and this transaction is rolled back afterwards, error 3307 might be seen on both the client side and the SQL Server errorlog:
A select using 'or' in the where clause on a view that is defined using a '>=' (greater than or equal to) comparison operator in the where clause can result in an a Thread Level Handled AV (Access Violation). The AV occurs during the process of determining the cost of a particular access plan that might be used to fulfill the requested select statement. A SELECT statement against multiple tables that includes a CASE expression can result in parser error 403:
To reproduce this problem, the tables should have at least two columns of datatype CHAR, and three tables must be involved in the SELECT statement. Also, a join must be specified between two of these tables. A query with a subselect that contains a join may cause a handled exception that terminates the client connection. WORKAROUND
Use a separate DBPROCESS for each thread.
Q152063: BUG: DB-Lib Unable to Handle Burst of TDS Packets Under TCP/IPBUG# NT: 15078This problem only occurs under TCP/IP; therefore, you may want to use other IPCs (such as named pipes, IPX/SPX, or RPC) instead. Also, reducing the window size on the client side TCP/IP can make the problem less likely to occur. Q150258: BUG: Long Database Names Can Cause AV in the Distribution TaskBUG#: 13530Avoid using long names for published and subscribed databases. To avoid this particular problem, a database name of no longer than 10 characters is recommended for both the published and subscribed database. Q136531: FIX: SELECT INTO From a ALTERed TABLE Cannot be PerformedBUG# NT: 10972Instead of performing an ALTER TABLE to add the IDENTITY column, CREATE a TABLE with the identity column and then transfer the data to the new table with an INSERT operation. Q139620: FIX: @@IDENTITY in Nested Stored Procedure Returns NULLBUG# NT: 11897Do not query the @@identity global variable from more than one stored procedure nesting level. For example, modify the "add_test1" stored procedure (from example in the following MORE INFORMATION section) to the following code and it works properly: Q140265: FIX: CREATE INDEX Aborts with Error 1509 or 632BUG# NT: 12183Increase the "sort pages" configuration parameter from the default of 64 to a higher number using the sp_configure system stored procedure. In most cases, increasing this parameter from the default of 64 to 128 will allow the index to be created; however, it can be increased to a maximum of 511 if necessary. NOTE: Before modifying the number of sort pages, you need to issue the command: sp_configure 'allow updates', 1 along with a corresponding: sp_configure 'allow updates', 0 at the end of the process. Q140332: FIX: Remote Backups and xp_loginconfig Fail With Access DeniedBUG# NT: 12222Switch the SQL Server security mode to mixed or integrated security. Q148324: FIX: DECLARE CURSOR on View with DISTINCT Causes AVBUG# NT: 11550Rewrite the query so that you are using DISTINCT in the cursor declaration rather than in the view declaration. Another possibility is to drop the unique index on the table if it is not being used for integrity constraints. DB-Library and ODBC applications can also use their respective cursor libraries. Q146637: FIX: DATALENGTH Function Returns 0 on 256 Byte Text ParameterBUG# NT: 13179Break the transact-SQL batch into smaller components. You can do this by adding "Go" between the transact-SQL statements. Q147414: FIX: Unhandled Exception When Running DBCC CheckdbBUG# NT: 13125You can work around this problem by doing one of the following:
Q149934: FIX: AV on FETCH From Updatable CURSOR Using VIEWBUG#: 12627Use cursors against the direct tables instead of views. Q152627: FIX: SELECT from Multiple Unjoined Tables Can Cause AVBUG#: 13087Simplifying the SELECT statement or restricting the number of rows or tables in the query will avoid the Access Violation. Q148219: FIX: Inconsistent Behavior with Global Temporary TablesBUG# NT: 13490Split the query so that the nesting level is less than three. Q150955: FIX: Client Terminated on Repeated Unsuccessful Obj ResolutionBUG#: 13393Ensure that all the views have proper base tables. Alternately, drop and recreate the views whenever the base tables are dropped. Q150988: FIX: Extraneous Locks During UPDATE on Table w/ > 15 IndexesBUG#: 13626Use send trace output to the Errorlog file: Q149174: FIX: Correlated Subquery on Datetime Col May Cause Client AVBUG#: 14267Split the query into multiple queries by using intermediate temporary tables. Q149917: FIX: 16933 Error on View Updated Through Server CursorBUG#: 13762Open the cursor with a select against the base tables instead of the view. Alternatively, open a second connection and issue an SQL update command based on key values through the second connection. Q150396: FIX: Stored Proc Can Fail w/ Msg 1203 Using READ UNCOMMITTEDBUG#: 14238Use the locking hint keyword NOLOCK in the SELECT statement instead of SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. - OR - Recompile the stored procedure so it does not use the existing plan in cache that was not executed with READ UNCOMMITTED. This can be accomplished in several different ways, including EXEC WITH RECOMPILE, drop/recreate the stored procedure, or restarting SQL Server. Just retrying the operation will not avoid the problem unless the existing plan is flushed from cache. This can be determined by using DBCC MEMUSAGE. Q151590: FIX: Error 4409 Generated When Using Multiple Database ViewsBUG#: 14645Drop and add the views when you are sure no one is accessing them. Q152414: FIX: Compatible Tape Drives May Not Read SQL Server DumpsBUG#: 13749 | 13753Starting with Service Pack 3, SQL Server now contains the trace flag 3206 that will reverse the SQL Server tape mark algorithm to the following:
Q152415: FIX: sp_processmail Gets Ambiguous Recipient ErrorBUG#: 13309Make sure that there are no ambiguous e-mail addresses. Perform the insert with the option DEFAULT VALUES on the base table rather than through the view. Q149932: FIX: 3307 Lock Error on INSERT SELECT UNION Abort w/ Dup KeyBUG#: 14745The INSERT would otherwise fail with a non-fatal 2601 "Attempt to insert duplicate keys" error. The INDEX can be changed to use the IGNORE_DUP_KEY option so that the 2601 error is not raised. Q149938: FIX: AV on INSERT of UNIONed SELECTs to Table With DEFAULTsBUG#: 14833Implement the INSERT as distinct INSERT SELECTs for each table in the UNION. If there is a UNIQUE INDEX on the target table, use the IGNORE_DUP_KEY option on that INDEX to filter out duplicate rows that would previously have been filtered out by the UNION. In many cases, this method can be much faster anyway because it can eliminate one or more intermedate work tables. Q150882: FIX: Possible Handled AV w/ Sub-query Using NOT IN and '>'BUG#: 12628There are two possible means to workaround this problem. The first is to drop the current index and create either a unique clustered or non- clustered index. Or you can use the convert() function to step-up the column referenced in the sub-query to the same size as the column referenced on the left of the IN condition. An example of this 'step-up' would be to change the query to the following: select a from test1 where a not in (select convert(char(2),c) from test2 where b = '1') and a <= '1' Q151408: FIX: Query w/ Group by All Can Take Too Long to CompleteBUG# 12809Reduce the duplicates and create appropriate index(s) to reduce the amount of time to produce the output. Q151605: FIX: Grant or Revoke Statements Fail When Used in an SPBUG#: 13040Use the EXECUTE statement to complete the operation. For example: can be changed to: Q152214: FIX: UPDATE Using Aggregate and Arithmetic Operator Causes AVBUG#: 15062One workaround is to run the UPDATE statement outside of a stored procedure. Otherwise, break up the query and assign the result of the subselect to a variable, and then use the variable in the UPDATE statement. Q152264: FIX: Five-way Table Join w/OR May Cause Handled ExceptionBUG#: 15089Rewrite the query such that the rows not in the join qualify for the query because of the OR clause. Q152615: FIX: Syntax Error in SELECT List May Cause Handled AVBUG#: 15027Add the comma to the SELECT clause. Q149243: FIX: Insert/Select/(NOLOCK) w/ Rollback May Cause Error 3307BUG#: 13733
Q151583: FIX: Select Using 'or' From View Defined w/ '>=' Can Cause AVBUG#: 13873Changing the information available prior to SQL Server's evaluation of the query cost can be used to workaround this problem. Removing 'or' from the where clause as defined in the view, changing the type of comparison operator used in the view where clause, changing the index on the base table to 'clustered not unique', changing the columns being referenced by select on the view, and/or removing the 'or' found in the select where clause can all affect the occurrence of this problem. Q151681: FIX: SELECT with CASE Can Cause Parser Error 403BUG#: 13817Avoid using the CASE expression by rewriting the query using the IF..ELSE conditional Transact-SQL statement. Q152263: FIX: Query with Join in Subselect May Cause Handled ExceptionBUG#: 13120Rewrite the query, possibly using a normal join instead of the subselect. MORE INFORMATION
This problem will only occur if SQL Server is running in Per Seat licensing
mode and is configured to use standard security, and even under these
conditions, it is rare.
Here is an example of this problem: Suppose there are the following two email addresses, "SQL" and "SQLSERVER." If e-mail user "SQL" sends a message to a SQL Server that has sp_processmail running, SQL Server will receive the message, but SQL Server fails in sending a reply because the intended recipient's address is ambiguous. The address is ambiguous because e-mail address resolution says that both "SQL" and "SQLSERVER" are possibilities for the address "SQL." Here is the updated documentation of xp_readmail from the SQL Server 6.5 documentation: If the sp_processmail stored procedure is modified to perform the xp_sendmail back to the value of the @originator_address instead of the @originator, sp_processmail will not fail because of an ambiguous recipient. For additional syntax information for xp_readmail see the Microsoft SQL Server Transact-SQL Reference. Q149711: FIX: Insert View with DEFAULT VALUES Causes AVBUG#: 12923If the insert is from UNIONed SELECT statements and there are duplicate keys (but not rows) generated such that the INSERT should fail, the 3307 error can be raised if the level of data reaches a certain threshold, somewhere around 1000 rows depending on row size. At a certain threshold of data, a work table needs to be created to resolve the UNION correctly. If the target table has DEFAULTs, these are not applied to intermediate work tables but other attributes of the target table, such as NOT NULL, are applied, and this would cause the query to fail with a different error if there was not the AV. Limited testing has shown that the situation under which this problem has been encountered is narrow in scope, and the following conditions must be met:
Q152800: FIX: GROUP BY Clause without Index Executes More SlowlyBUG#: 14825SYMPTOMSIf a Transact-SQL query contains a GROUP BY clause and an index is not defined on the column involved in GROUP BY, the query exhibits better performance when executed on the SQL server with no service packs or service pack 1, as opposed to SQL Servers with service pack 2 installed. This performance anomaly is exhibited only on the Alpha platform.WORKAROUNDSort the columns in the GROUP BY clause. Alternately, define an index encompassing the columns involved in the GROUP BY clause and make sure the index is used when grouping the columns in the query.ADDENDUM: LIST OF PROBLEMS CORRECTED IN SERVICE PACK 1For the complete description of these bugs, please see the Microsoft Knowledge Base article Q136575.ADDENDUM: LIST OF PROBLEMS CORRECTED IN SERVICE PACK 2For the complete description of these bugs, please see the Microsoft Knowledge Base article Q138399.Additional query words: sp3 database patch service pack
Keywords : SSrvGen |
|
Last Reviewed: July 15, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |