FIX: SQL Server 6.0 Service Pack 2 Fixlist

ID: Q138399

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 2. Service Pack 2 is now available from your primary support provider. For more information, contact your primary support provider.

Service Pack 2 includes all fixes previously released in Service Pack 1 for version 6.0. The list of fixes for Service Pack 1 is 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 2

FIX: SELECT During UPDATE STATISTICS Can Terminate w/ Msg 814

Q134629 BUG# NT: 11144 (6.00)

SYMPTOMS

Queries that run during execution of UPDATE STATISTICS can terminate with Msg 814, Level 20, State 1:

Keep count of buffer <n> holding logical page <n> in database <db>
has become negative.

The DBCC NEWALLOC commmand may not detect extent chain inconsistencies if the problem is on TEXT or IMAGE chains.

An INSERT INTO a table with an IDENTITY column inside a trigger can cause a handled access violation (AV).

On Microsoft SQL Server version 6.0, a SELECT INTO from a table with an identity column when the set IDENTITY_INSERT option is ON can cause a handled access violation (AV).

The AV will not occur when the IDENTITY_INSERT option is switched OFF.

On Microsoft SQL Server version 6.0, if you update the primary key of a table, the original primary key parameters passed to a custom stored procedure defined for replication will be replaced with the updated primary key values.

FIX: Multiple ORDER BY DESC May Cause 614 Errors

Q137432 BUG# NT: 11164 (6.00)

A query with multiple ORDER BY <col> DESC clauses may get a 614 error:
A row on page X was accessed that has an illegal length of Y in database Z.

FIX: DBCC SQLPERF(THREADS) Fails w/ AV on 6.0 SP 1

Q138013 BUG# NT: 11497 (6.00)

The command DBCC SQLPERF(THREADS) on SQL Server version 6.0 Service Pack 1 fails with the error:
DBCC function 'threads' in the library 'sqlper60' generated access
violation; SQL Server is terminating process <n>

The client connection that executed this command is terminated, but other client connections are unaffected. The client may see the access violation error listed above, or encounter DB-Library errors 10037, "Unexpected EOF from SQL Server," or 10008, "Bad Token from SQL Server: Datastream processing out of synchronization."

This problem does not exist in SQL Server 6.0 without Service Pack 1 applied.

A simple SELECT statement with an IN clause and a subquery of the form SELECT NULL may cause a handled access violation in SQL Server version 6.0. The problem also occurs with a NOT IN clause instead of an IN clause in the query. However, if the subquery returns null results, the access violation does not occur.

If a published article has a stored procedure created FOR REPLICATION that references a column in a non-clustered index on the article's base table, the filter procedure can prevent a transaction from being replicated. Examining the MSjob_commands table in the distribution database can help you detect this problem.

Extended character values embedded in SQL statements, such as an INSERT command, can be incorrectly distributed by the LogReader on servers installed with non-ANSI character sets. In most situations, the values will appear as unreadable characters. In other situations, it could cause the command to fail with a syntax error when applied to the subscriber.

A stored procedure created with a text/image parameter may cause a handled access violation (AV) when executed through dbrpcexec or Transact-SQL.

Syntax error reporting is disabled when the query plan size of a stored procedure exceeds 68 pages.

You can verify the query plan size by running DBCC MEMUSAGE after creating a stored procedure without any syntax-errors.

The stored procedure has been created, but will not exist in the database, thus it will not be displayed in the DBCC MEMUSAGE output.

If you execute a stored procedure that declares a cursor not using exec(), and then close the connection before the cursor is opened or deallocated, and then try to drop the procedure from a new connection, a 3702 error similar to the following will be generated:
Msg 3702, Level 16, State 3
Cannot drop the procedure 'cursor3702' because it is currently in use.

Many users performing queries against tables with non-clustered indexes on symmetric multiprocessing (SMP) computers with more than two processors can get processes blocking each other, but there are no related locks shown by sp_lock. KILLing the blocker may or may not alleviate the problem. This problem is very rare.

A subquery that references an invalid column may cause a handled access violation (AV). The expected output would be the 207 error message, "Invalid column name <table.column>".

The milliseconds portion of the datetime field does not get replicated. When sp_replcmds generates the inserted value for a datetime field, the milliseconds portion is not included.

If a cursor is opened that contains a union between two queries, you may receive errors such as:
DB-Library: Possible network error: Bad token from SQL Server:
Datastream processing out of sync.
Net-Library error 0:
DB-Library: Possible network error: Bad token from SQL Server:
Datastream processing out of sync. Connection broken.
<above is repeated many times>
Net-Library error 6: ConnectionClose (CloseHandle()).
Msg 533, Level 20, State 4
Can't find a range table entry for range 5.
DB-Library Process Dead - Connection Broken

And in some cases, the following message may also occur:
Msg 16942, Level 16, State 2
Asyncronous keyset generation failed, the cursor has been deallocated

You may get a handled access violation (AV), 632 errors, or 614 errors on symmetric multiprocessing (SMP) computers running SQL Server if there are multiple users dropping and creating tables concurrently. This is true of TEMPDB as well as user databases. The timing involved for this bug to occur makes it very rare, and there are no side effects outside of the client being terminated.

When using Transact-SQL cursors, if BEGIN TRAN is issued after OPEN CURSOR, and then the cursor is closed, there can be an open transaction that cannot be removed with COMMIT TRAN or ROLLBACK TRAN.

If you explicitly insert a value into an IDENTITY column inside a stored procedure, it can cause an thread level access violation.

Error number 3307, "Process x was expected to hold logical lock on page y," can be generated if UPDATE STATISTICS is run when the database has run out of space. This can also leave stranded locks that can only be cleared by stopping/starting SQL Server.

If the last subscription to an article is removed while there are still outstanding unreplicated transactions in the published database log, the LogReader task for replication can fail with the following error:
The replicated transaction (xxx, yyy) no longer exists in the log.
Unable to execute sp_repldone on '<server>'.

Where xxx = the value of xactid_page and yyy = the value of xactid_row in the MSjobs table in the distribution database.

The LogReader task will fail and be shutdown in this situation. No replication transactions for the published database will be processed until the error is corrected. Since the log cannot be truncated past the oldest unreplicated transaction, the log may fill to a point where Msg 1105 will be encountered.

Under certain conditions using horizontal or vertical partitioning, the LogReader task for replication can fail with the following error:
The replicated transaction (xxx, yyy) no longer exists in the log.
Unable to execute sp_repldone on <server>.

where xxx = the value of xactid_page and yyy = the value of xactid_row in the MSjobs table in the distribution database.

When you insert a row inside a stored procedure (SP) into a temporary table that contains a timestamp column in a situation where the temporary table was created outside the SP and was deleted after the creation of the SP, the timestamp column is set to 0 when the SP is called.

A handled access violation (AV) or 632 error may be generated if an UPDATE statement is issued that sets a column in the update table equal to the "SELECT COUNT(DISTINCT column)" results of a subquery. The subquery must contain a comparison expression against a column which contains all null values.

The 632 error will generate the text:
Error : 632, Severity 20, State 1
Memmove() was called with a length of n - maximum allowed length is m.

Opening a server-side cursor where the SELECT statement is a stored procedure may result in error 225:
Cannot run query--referenced object (name NAME NOT RECOVERABLE) dropped
during query optimization

after you load the database with LOAD DATABASE.

If a table has a non-unique clustered index and is running on a multiprocessor computer with SMP support on, INSERTs can cause the following 1203 error message to occur:
Msg 1203 Level 20 State 1
Caller of lock manager is incorrectly trying to unlock an unlocked
object. spid=%d locktype=%d dbid=%d lockid=%ld.

During the execution of a stored procedure, the following error messages could occur:
Msg 2806, Level 18, State 0
Stored procedure %s is corrupted. Must re-create procedure.

In addition to the client receiving this message, message 707 will appear in the errorlog.
Msg 707, Level 20, State 9
System error detected during attempt to free memory at address
0xdcdcdcdc.

Until SQL Server is restarted, you will not be able to run the stored procedure reported as corrupt by error message 2806.

A stored procedure that contains a cursor can cause an access violation if the following conditions are met:
  1. The cursor needs to have an expression clause.


  2. The cursor must have an ORDER BY clause.


  3. The stored procedure must be executed, dropped, recreated, and executed again for the AV to occur.


A SELECT against a view can result in the following error message:
Msg 530, level 18, State 0
Attempt to insert NULL value into column %d in work table (table id);
column does not allow NULLS, UPDATE fails.

The connection will be terminated and the SQL Server error log will show a language exec error and an access violation.

Using FOR BROWSE in a SELECT statement in a user-defined transaction holds shared page locks until the end of the transaction.

A call to dbopen with an invalid password or an attempt to establish an invalid trusted connection causes a 32K memory leak. The private bytes for the process will climb by 32K each time the dbopen fails. You may also see Process\Non-Paged Pool bytes increase. Continued leaks may cause Windows NT to run to a low virtual memory state.

A SELECT from a VIEW on a table that has a data length greater than 1962 bytes (a table created with VARCHAR datatype) may hang the server. The server will crash with the following error message:
SQLSERVR.EXE - Application error
The exception Integer Division By Zero
(0xc000094) at location 0x00513098

ADDENDUM: LIST OF PROBLEMS CORRECTED IN SERVICE PACK 1

For the complete description of these bugs, please see Knowledge Base article Q136575.


CAUSE

Though rare, you could encounter this problem if the following conditions apply:


If, after encountering the initial error, execution of other commands that involve tables from the SELECT statement also fails with Msg 814, you may have to restart SQL Server to avoid encountering the error in the future.

The DBCC NEWALLOC command traverses extent chains for all indexes of a table, but does not check the extent chains for indid = 255, which is the number used for TEXT and IMAGE chains. Inconsistencies of this type are also not detectable with the DBCC TEXT_AL or DBCC TEXT_ALLOC commands. DBCC CHECKALLOC does correctly detect and report TEXT extent chain problems.

The client AV occurs only if the table with an IDENTITY column has a trigger. The trigger may be fired by either an INSERT, DELETE, or UPDATE operation. The client does not cause an AV when a SELECT INTO operation is performed on a table that is not using the IDENTITY property.

The SELECT statement in the replication filter procedure is being incorrectly evaluated by SQL Server when applying the filter procedure against the logged change in the published database.

The most common occurrence of this problem is when a "restriction clause" for the article is created using SQL Enterprise Manager where the column listed in the clause is part of a non-clustered index.

This problem can also occur if you develop your own filter procedure using the CREATE PROCEDURE FOR REPLICATION command. This problem does not affect the proper synchronization of data when first subscribing, only changes made after synchronization.

The sp_replcmds procedure used by the LogReader is using an ANSItoOEM conversion which can cause extended characters on non-ANSI servers to become unreadable.

FIX: RPC w/ Text/Image Parameters May Cause Handled AV

Q138324 BUG# NT: 11373 (6.00)

SQL Server fails to handle the text parameter properly when re-mapping stored procedure variables after the stored procedure was bumped out of procedure cache.

It is only reproducible if the stored procedure is executed with the length of the text/image parameter greater than 2048 bytes immediately after it is reloaded into procedure cache.

This problem can occur if a transaction is run against a published article that does not meet the criteria of a horizontal or vertical partition. The LogReader task will fail and be shutdown in this situation. No replication transactions for the published database will be processed until the error is corrected. Since the log cannot be truncated past the oldest unreplicated transaction, the log may fill to a point where Msg 1105 will be encountered.

If the stored procedure has not already been executed by itself prior to being used in the cursor, the re-resolution of the stored procedure leads to this error.

If the SQL Server procedure cache gets full, this can result in corrupt stored procedures. In addition to the cache being full, the stored procedures running need to be above 64 pages in size. Also, they need to be accessing temporary tables that were created outside the stored procedure.

For this bug to occur, the view needs to meet the following conditions:
  1. The SELECT clause needs to have a DISTINCT in it.


  2. The query being performed by the SELECT must use an outer join.


  3. A column in one of the tables must be a bit field.


When SQL Server forms the outer join work table, it mistakenly puts NULLS in the work table where the bit field is. By definition, a bit field must be either 0 or 1, and in the case of a NULL it should default to 0; however, in this case it does not.

This problem occurs only when the TEXT datatype column is included in the SELECT list. The locks are not held until the end of the transaction when the TEXT datatype columns are not selected.

FIX: dbopen Memory Leak of 32K On Failed Login Attempt

Q139556 BUG# NT: 11480 (6.00)


WORKAROUND

Run UPDATE STATISTICS prior to or after the query that fails.

-OR-

Drop and recreate affected indexes instead of running UPDATE STATISTICS.

FIX: DBCC NEWALLOC May Not Detect TEXT Extent Inconsistencies

Q135350 BUG# NT: 11026 (4.20)

Use the DBCC CHECKALLOC command in addition to or in place of DBCC NEWALLOC if you have TEXT or IMAGE columns.

FIX: INSERT Into w/ IDENTITY Col Inside Trigger Causes AV

Q136534 BUG# NT: 10242 (6.00)

If your purpose in performing an INSERT operation inside a trigger is to implement Unique Increasing key values without using the IDENTITY property of SQL Server version 6.0, then please refer to the following article in the Microsoft Knowledge Base:
Q75164 : Implementing a Unique, Increasing Key Value

If your objective is to implement Declarative Referential Integrity (DRI), use the DRI feature of SQL Server version 6.0, or implement it by using tables without IDENTITY columns. Refer to the SQL Server "Transact-SQL Reference" manual for details on DRI implementation with triggers.

FIX: SELECT INTO From Table w/ IDENTITY Col Causes Handled AV

Q136536 BUG# NT: 10340 (6.00)

FIX: Update of Primary Key Causes Repl Custom St. Proc to Fail

Q136544 BUG# NT: 11329 (6.00)

Most of the information from SQLPERF(THREADS) can be found using a combination of other resources such as SQL Enterprise Manager (Current Activity), sp_who, examining the sysprocesses table, and Windows NT Performance Monitor.

FIX: SELECT w/ IN Clause Causes AV w/ Subquery SELECT NULL

Q138263 BUG# NT: 11525 (6.00)

If the subquery is replaced with the NULL keyword, the access violation does not occur.

FIX: Procedure for Repl. Can Fail When Referencing NCI

Q138290 BUG# NT: 10981 (6.00)

  1. Drop the non-clustered index.


  2. Remove the column referenced in the filter procedure from the non- clustered index.


  3. Filter the replicated changes on the subscriber by using custom stored procedures.


FIX: Repl w/ Ext Chars on Non-ANSI Char Sets May Fail

Q138291 BUG# NT: 11374 (6.00)

Because this would not happen the first time a stored procedure is loaded into procedure cache, drop and recreate the stored procedure, which should avoid the problem. Another way to avoid the problem would be to fake a call to the stored procedure with the length of the text/image parameter that is shorter than 2048 bytes, then call it with the real value. For example: 

   sp_recompile test_v
   go
   test_v_proc 1,'Fake one'
   go
   test_v_proc 1,'Real, longer then 2048 byte value'
   go

FIX: Syntax Error Reporting Disabled w/ St Proc >68 Pages

Q138477 BUG# NT: 11209 (6.00)

Reduce the size of the stored procedure so that it is less than 68 pages.

FIX: Drop Procedure Gives 3702 Error if Cursor Not Deallocated

Q138538 BUG# NT: 11556 (6.00)

Declare your cursor using exec(), or make sure that you always open and/or deallocate the cursor before you close your connection. For example: 

   use pubs
   go
   create procedure cursor3702 as
       exec("declare testcursor cursor for
       select * from authors")
   go
   cursor3702
   go

If you now close this connection, you can drop the procedure from a new connection.

If you receive the 3702 error, and the connection which executed the stored procedure is already closed, you will need to restart SQL Server before you can drop the procedure.

FIX: Users Blocking With No Locks Displayed in sp_lock

Q138603 BUG# NT: 11660 (6.00)

If KILL fails to alleviate the blocking, shut down and restart SQL Server. The problem is caused by applications issuing many "cancels" and can be alleviated by reducing the number of cancels sent to the server.

FIX: Invalid Column in Subquery May Result in Handled AV

Q138673 BUG# NT: 11623 (6.00)

Make sure you are referencing valid column(s) within the subquery.

FIX: Milliseconds Are Not Replicated w/ DATETIME Fields

Q138718 BUG# NT: 11767 (6.00)

Do not include milliseconds for a datetime field in any table that is going to be included in an article for replication.

FIX: Cursor w/ Union May Cause Errors if Not First in Batch

Q138746 BUG# NT: 11237 (6.00)

This generally occurs when there are other Transact-SQL statements preceding the declaration of the cursor. Moving the cursor to the beginning of the batch may prevent the errors from occurring.

FIX: Handled AV If Many Users Dropping/Creating Tables

Q138748 BUG# NT: 11417 (6.00)

Set the sp_configure "SMP concurrency" option to "1".

FIX: BEGIN TRAN After OPEN CURSOR May Not Commit

Q138749 BUG# NT: 11659 (6.00)

Call BEGIN TRAN before you call OPEN CURSOR.

FIX: Explicit Insert of Identity Value in SP Can Cause AV

Q138750 BUG# NT: 10308 (6.00)

Use a batch file instead of a stored procedure if you need to insert a value explicitly into an IDENTITY column.

FIX: Error 3307 Running Update Stats After 1105 Error

Q138787 BUG# NT: 11167 (6.00)

Ensure there is adequate space in the database such that error 1105 "Can't allocate space" is not encountered when you run UPDATE STATISTICS.

FIX: LogReader Can Fail On sp_repldone After Unsubscribing

Q138826 BUG# NT: 11493 (6.00)

Find the row in MSjobs in the distribution database whose xactid_page and xactid_row match the values listed in the error message above. This row should contain the maximum job_id value for the table.

After you do this, update the values for xactid_page and xactid_row for this row to 0. It is recommended that you run the UPDATE statement with a BEGIN TRAN command so that you can rollback changes if you make a mistake. Make sure to execute COMMIT TRAN if the statement is successfully applied.

The simplest way to perform this update is: 

   UPDATE MSjobs SET xactid_page = 0, xactid_row = 0
   WHERE  xactid_page = <xactid_page> AND  xactid_row = <xactid_row>

where
<xactid_page> and <xactid_row> are the values that are displayed in the
LogReader error message <x,y>

If the LogReader is setup as "Autostart," restart SQLExecutive. Otherwise, you may choose to execute the task "On Demand" to make sure it can run successfully. If the problem is resolved, the LogReader task will show up as a "Running Task" under SQL Enterprise Manager. However, it will not show a successful status in the Task History if you restarted SQLExecutive.

FIX: LogReader Can Fail On sp_repldone Using Partitioning

Q138825 BUG# NT: 11636 (6.00)

Find the row in MSjobs in the distribution database whose xactid_page and xactid_row match the values listed in the error message above. This row should contain the maximum job_id value for the table. Then update the values for xactid_page and xactid_row for this row to 0. It is recommended that you run the UPDATE statement with a BEGIN TRAN command so that you can rollback changes if you make a mistake.

Make sure to execute COMMIT TRAN if the statement is successfully applied. If the LogReader is setup as "Autostart," restart SQLExecutive. Otherwise, you may choose to execute the task "On Demand" to make sure it can run successfully. If the problem is resolved, the LogReader task will show up as a "Running Task" under SQL Enterprise Manager. However, it will not show a successful status in the Task History if you restarted SQLExecutive.

FIX: Timestamp Col of Temp Table Set to 0 When Inserting Row in SP

Q138848 BUG# NT: 11220 (6.00)

If the SP gets called once after its creation and before the original table is dropped, the timestamp is set to a correct value in all subsequent calls.

FIX: AV or Error 632 on Update w/ Select Count(distinct) Subqu

Q138846 BUG# NT 11505 (6.00)

Rewrite the query to eliminate the use of the COUNT(distinct) expression within the same statement as the comparison against the null column(s). A possible option would be to select the distinct results into a temporary table and perform futher manipulation on that data set.

FIX: Cursor Using Stored Proc After LOAD DB May Get 225 Error

Q140871 BUG# NT: 12136 (6.00)

Execute the stored procedure outside the cursor prior to using it with a cursor after a LOAD DATABASE has taken place.

FIX: Inserts on SMP Computer Can Cause Error Message 1203

Q141539 BUG# NT: 11223 (6.00)

There are several options available to avoid this problem, including:
  1. Turn off dedicated multiprocessor support to allow the INSERT to complete.


  2. Replace the clustered index on the problem table with a nonclustered index.


  3. Make the non-unique clustered index into a unique clustered index.


FIX: Stored Procedures Can Corrupt Themselves in Small Caches

Q141540 BUG# NT: 11322 (6.00)

You can avoid this problem by increasing the amount of memory available to the SQL Server procedure cache. This can be done by either altering the memory available to SQL Server or by increasing the percentage of SQL Server's memory available to the procedure cache.

FIX: A Cursor in a St. Procedure Can Cause an Access Violation

Q141541 BUG# NT: 11508 (6.00)

Remove or change the expression clause to not be an expression clause. Remove the ORDER BY and do the sort another way using temporary tables, or sort the result set on your client. Do not drop the stored procedure and immediately recreate and execute it. If the stored procedure must be dropped, then drop the stored procedure and shut down and restart SQL Server; this will allow it to execute upon re-creation.

FIX: A View With an Outer Join Can Cause An Access Violation

Q141542 BUG# NT: 12004 (6.00)

You can circumvent the problem by avoiding any one of the three conditions that must be present to cause it.

FIX: SELECT TEXT Columns with FOR BROWSE Holds sh_page Locks

Q141550 BUG# NT: 11535 (6.00)

Ensure that all login attempts are valid.


MORE INFORMATION

Some specific troubleshooting areas:

  1. Check all passwords and password mappings. (Use the SQL Security Manager for mappings and the Control Panel Service application to verify startup information for the specific service.)


  2. Enable failed and successful SQL Server logins. (Use the SQL Server Setup Security option.)


  3. Logon as the secured user and use ISQL with the -E parameter to verify the user can establish a secured login.


  4. Verify the net-library supports a trusted connection. (Use the SQL Server Client Configuration Utility to verify a named pipes or multi-protocol connection is being established.)


  5. Verify the pipe name is correct. The pipe name must begin with \\.\pipe\... Anything else will cause an error when SQL Server attempts to create the pipe. Thus, named pipe connections will fail.

    Verify the pipe is opend by looking in the SQL Server errorlog.


  6. Run SQLExecutive in debug mode.
    (sqlexec -c -v)

    **Specifically, retries of the replication processes can indicate failed logins.


By default, the first dbopen allocates 4K to track connections for the application. This memory is not released until the application exits.

FIX: SELECT From View w/ Data Length > 1962 Bytes May Hang Svr

Q147179 BUG# NT: 12766 (6.00) 


Keywords          : 
Version           : 6.0
Platform          : WINDOWS 
Issue type        :

Last Reviewed: April 12, 1999