MICROSOFT SQL SERVER 6.5 SERVICE PACK 3 FIXLIST --------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 Service Pack 3 --------------------------------------------------------------------- The following is a list of fixes and other various improvements that have been made in the Microsoft SQL Server version 6.5 Service Pack 3, which is now available from your primary support provider. For more information, contact your primary support provider. Please note that workarounds described in the Q articles have been provided for your information only. It is not necessary to implement these workarounds if you have the updated software. For the most recent updates to these article, please query the Microsoft Knowledge Base using the Qxxxxxx number that precedes the title of the article. ============================================ LIST OF PROBLEMS CORRECTED IN SERVICE PACK 3 ============================================ Note: The full text of each article follows the list. ODBC Q164442 FIX: ODBC Clients Show as Sleeping but Command Is EXECUTE SERVER Q170296 FIX: Deadlock on Table with Text/Image Column May Cause Errors Q167779 FIX: Query Slow w/ ORDER BY DESC on Table w/ Composite Index Q167336 FIX: A Full Outer Join with the Same Table May Cause an AV Q167335 FIX: Invalid Text Handle Errors May Occur in Transactions Q167334 FIX: Error Msg 201 Incorrectly Returned Twice Q167333 FIX: SQL Server Stops Responding When Canceling Tape Backup Q167332 FIX: Error 3201 Does Not Log a Message to the Errorlog Q167331 FIX: Tape Backup May Cause Error 3201 Device Offline Q166337 FIX: Auto-Start Stored Procedure w/ WAITFOR Causes 100% CPU Use Q166298 FIX: #Deleted After Microsoft Access Sorts a SQL Server Table Q166188 FIX: Server Stops/AV on Client Disconnect w/ an Open Transaction Q165857 FIX: Delete/Rollback Can Cause AV on Table w/Text Col & Triggers Q165756 FIX: A SELECT with a Large GROUP BY Clause May Return Error 614 Q165754 FIX: INT Divide by Zero Exception Error When Running a Query Q165621 FIX: Error 231 After Opening a Server Side Cursor Q165599 FIX: Datalength Function Returns 0 for NULL Text Columns Q165597 FIX: Convert to Text Function w/NULL Input Returns Blank String Q165596 FIX: HOLDLOCK on READTEXT Causes Error 7134 Q165579 FIX: DBCC NEWALLOC May Cause Incorrect Database Usage Count Q165560 FIX: Existing Fill Factor Changed w/ Rebuild of Clustered Index Q165559 FIX: Error 403 on Select w/ Nested Subqueries, Sums, and Groups Q165558 FIX: AV on Cursor Declare Ssing Select on View w/ ORDER BY Q165557 FIX: Thread AV in SP1 Using a Cursor Defined w/Constant and View Q165513 FIX: Running a Global Temporary Stored Procedure May Cause AV Q165512 FIX: Err 1203 Running Dynamic Cursor in a Stored Procedure Q165511 FIX: A Derived Table Using a Group By May Cause Error 8158 Q165510 FIX: Numeric and Decimal Datatypes Do Not Always Use an Index Q165389 FIX: Spid Missing with Trace Flag 4032 on RPC Events Q165166 FIX: 'Load Tran' May Cause 806, 605, 2620, or 6902 with IRL on Q165136 FIX: SQL 6.5 Does Not Allow Updates to a View If Grant Specified Q165131 FIX: Stress of Lock Limit Threshold Causes Bufwait Error Message Q165099 FIX: OrigFillFactor Reports Wrong Value for Non-Clustered Index Q164997 FIX: Dump to Tape Fails Without Error in Errorlog Q164972 FIX: Err 7134 w/ Text/Image Col. Update Using 'Where Current Of' Q164734 FIX: Repetitive Connect/Disconnect May Shut Down SQL Server Q164691 FIX: Insert Through View May Cause Duplicated Identity Value Q164578 FIX: Error 1105 on Default Segment When Running DBCC DBREINDEX Q164577 FIX: Error 803 on INSERT into Table with Multiple Indexes Q164576 FIX: Error 2610 on Insert into Table with Multiple Indexes Q164293 FIX: Correlated Subquery in Select List w/ ANSI JOIN Causes AV Q164290 FIX: Srv_message_handler Text Limit Q164287 FIX: Stored Procedure Can't Be Upgraded Q164215 FIX: Bad Token or AV If Sp_cursoropen After Dropping Index Q164178 FIX: Bcp_batch Stops Responding After Interleaved Bcp_sendrow Q164056 FIX: Error 16934 If Sp_cursor Update on Sproc After Dump/Load Q163908 FIX: Undetected Deadlock with Checkpoint and SELECT INTO Q163730 FIX: AV Joining a Table with a Rowsize Over 1,962 Bytes Q163452 FIX: Failed RPC Call from SQL Server Generates Error 18481 Q163128 FIX: Updates to Tables w/Text on SP2 Cols May Result in Errors Q163047 FIX: Load and Recovery of a Read-Only Database May Cause AV Q162906 FIX: AV If Extended Cursor Opens SP Without Execute Permission Q161892 FIX: Error 8104 When DBO Tries to Run SET IDENTITY_INSERT ON Q161741 FIX: Dump Aborts if PG_DMPTRLR Bit Set (MSG 3220) Q161726 FIX: AV Selecting from VIEW with DISTINCT and Join Q161645 FIX: Sp_Cursor Positioned Update Causes Access Violation Q161599 FIX: Temporary Dump Devices May Be Created with the Wrong Name Q161125 FIX: DUMP or LOAD with Tape May Fail on Windows NT 3.51 Q160584 FIX: Rollback in Single User Database Causes Msg 3307 and AV Q159782 FIX: Concurrent CREATE TABLE & ALTER TABLE May Cause Deadlock Q159744 FIX: Message 2 After Issuing ROLLBACK TRANSACTION Command Q158464 FIX: Sp_columns Returns More Than One Row for a Column Q156265 FIX: Create Nonclustered Index Blocks Selects from Table Q151573 FIX: Striped Dump to 7 or More Devices is Not Logged SQL ENTERPRISE MANAGER Q135866 FIX: SEM - Edit Devices Dialog Displays Negative Device Size REPLICATION Q164288 FIX: Replication Filter Stored Procedures Not Upgraded to 6.5 Q164442 - FIX: ODBC Clients Show as Sleeping but Command Is EXECUTE -------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 -------------------------------------------------------------------------- BUG #: 16453 (6.5) SYMPTOMS ======== When an ODBC client runs a stored procedure, and after the execution is complete, the Current Activity in SQL Enterprise Manager or the results of a sp_who query may show that the process' (spid) status is sleeping, but the command is EXECUTE instead of AWAITING COMMAND. WORKAROUND ========== If the process is not blocked by another process, the query has been completed, and you can ignore the EXECUTE command. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. MORE INFORMATION ================ This problem may also occur with DB-Library clients that use the stored procedure API calls (those with RPC in the function name). Q170296 - FIX: Deadlock on Table with Text/Image Column May Cause Errors ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16861 (6.5) SYMPTOMS ======== Heavy stress that causes an extremely high number of deadlocks on a table with image/text columns may cause the following errors and cause the server to stop responding: udread: Operating system error 38(Reached end of file.) on device 'C:\MSSQL\DATA\MASTER.DAT' (virtpage 0x00ffff00). Buffer 10da140 from database 'MSS' has page number 2855763 in the page header and page number -256 in the buffer header mirrorproc: i/o error on primary device 'C:\MSSQL\DATA\MASTER.DAT' udread: Operating system error 6(The handle is invalid.) on device 'C:\MSSQL\DATA\MASTER.DAT' (virtpage 0x00000114). udread: Operating system error 6(The handle is invalid.) on device 'C:\MSSQL\DATA\MASTER.DAT' (virtpage 0x00000394). udread: Operating system error 6(The handle is invalid.) on device 'C:\MSSQL\DATA\MASTER.DAT' (virtpage 0x00000105). bufwait: timeout, BUF_IO, bp 0x10fdea0, pg 0x110, stat 0x1004/0x6, obj 0x1d906ebe, bpss 0 The errorlog is filled with bufwait error messages until server is killed (it cannot shut down normally); all existing connections become unresponsive, and no new connection can be made. This problem only happens infrequently on very high performance servers. In the scenario in which Microsoft was able to reproduce these errors, an SMP computer with fairly high CPU speed was required, and even then this problem only surfaced occasionally. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q167779 - FIX: Query Slow w/ ORDER BY DESC on Table w/ Composite Index --------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 --------------------------------------------------------------------------- BUG #: 16669 (6.5) SYMPTOMS ======== An ORDER BY DESC query against a table that has a non-clustered composite index may take significantly longer to run than the equivalent ASC query. This difference is especially noticed in cases in which a WHERE clause contains an equivalency test of an indexed column (including the most significant) and a constant value. WORKAROUND ========== To work around this problem, select the result set (ASC) into a temporary table, then run the ORDER BY DESC query only on the results of a select from the temporary table. Another option, for certain numeric columns, is to multiply the key by -1 and then run the ORDER BY ASC query. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q167336 - FIX: A Full Outer Join with the Same Table May Cause an AV ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16457 SYMPTOMS ======== A select statement that does a full outer join on the same table may cause a handled access violation (AV). For this problem to occur, the following conditions must be true: - The select statement does a full outer join on the same table. - An ORDER BY clause is also included. - The query causes a REFORMAT to occur. WORKAROUND ========== To work around this problem, do any of the following: - Remove the ORDER BY clause. - Use another sorting method. - Change some indexes on your table to avoid the REFORMAT condition. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q167335 - FIX: Invalid Text Handle Errors May Occur in Transactions ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16426 SYMPTOMS ======== Even if you have the lock isolation level set to HOLDLOCK or REPEATABLE READ, a text pointer can be changed while you are in a transaction. This will result in an invalid text handle when you try to use it again. For example, during the WAITFOR delay in the following query, another user can update the @mytextptr before you can use it, resulting in an invalid textpointer error: BEGIN TRAN declare @mytextptr varbinary(16) select @mytextptr=TEXTPTR(pr_info) from pub_info where pub_id='9999' and TEXTVALID('pub_info.pr_info',TEXTPTR(pr_info))=1 WAITFOR DELAY "00:00:10" IF @mytextptr IS NOT NULL WRITETEXT pub_info.pr_info @mytextptr WITH LOG 'Hello Again' COMMIT TRAN WORKAROUND ========== Use the locking hint UPDLOCK, which causes the textpointer to be correctly held. For example: select @mytextptr=TEXTPTR(pr_info) from pub_info (UPDLOCK) where pub_id='9999' and TEXTVALID('pub_info.pr_info',TEXTPTR(pr_info))=1 STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q167334 - FIX: Error Msg 201 Incorrectly Returned Twice ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16359 SYMPTOMS ======== Running a stored procedure by incorrectly passing the DEFAULT parameter to it causes error Msg 201, "Procedure %s expects parameter %s, which was not supplied." to be returned twice instead of once. For example, the following query should return error Msg 201 once, but instead returns it twice: use pubs go CREATE PROC SP_OutParm (@outvar int output) As SELECT @outvar = 75 go exec sp_outparm DEFAULT go WORKAROUND ========== To work around this problem, upgrade to SQL Server 6.5 Service Pack 3. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q167333 - FIX: SQL Server Stops Responding When Canceling Tape Backup ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16358 SYMPTOMS ======== If you attempt a SQL Server tape backup while there is no tape in the tape drive, SQL Server may stop responding. This problem may occur if you click the cancel button twice after receiving the error message stating there is no tape in the tape drive. WORKAROUND ========== To work around this problem, do not click the cancel button. Instead, load the appropriate tape and continue your backup. If SQL Server has stopped responding, you must stop and restart SQL Server to recover. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q167332 - FIX: Error 3201 Does Not Log a Message to the Errorlog ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16351 SYMPTOMS ======== When error 3201 occurs, it indicates that more specific errors will be written to the SQL Server error log. Unfortunately, they are not. The following is error 3201: Msg 3201, Level 16, State 1 Can't open dump device '\\.\tape0', device error or device off line. Please consult the SQL Server error log for more details. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q167331 - FIX: Tape Backup May Cause Error 3201 Device Offline ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16349 SYMPTOMS ======== When backing up a database to a tape drive, error 3201 may occur: Msg 3201, Level 16, State 1 Can't open dump device '\\.\tape0', device error or device off line. Please consult the SQL Server error log for more details. Often, there will not be an accompanying error message in the errorlog, even though the message indicates that there should be one. This error may be caused if the tape block size of the tape drive is larger than the backup buffer size in SQL Server's sp_configure. WORKAROUND ========== To work around this problem, use sp_configure to modify backup buffer size to a value that is equal to or greater than the tape drive's block size. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q166337 - FIX: Auto-Start Stored Procedure w/ WAITFOR Causes 100% CPU Use ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, versions 6.5 ------------------------------------------------------------------------- BUG #: 16379 (WINDOWS: 6.5) SYMPTOMS ======== The SQL Server service starts up and the % Total Processor Time Performance Monitor counter for the System object rapidly increases to 100 percent. The system appears to be very slow. CAUSE ===== The auto-start stored procedure contains a WAITFOR statement. WORKAROUND ========== To work around this problem, avoid having a WAITFOR statement in a startup stored procedure. Run the stored procedure using a query tool instead. Unmark the procedure as a startup procedure by doing the following: 1. Start up SQL Server with the -f option to skip autoexec stored procedures and run in single-user mode. 2. Open up a query window and run sp_unmakestartup . STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q166298 - FIX: #Deleted After Microsoft Access Sorts a SQL Server Table ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16387 (Windows NT: 6.5) SYMPTOMS ======== If you use Microsoft Access 97 to link to a SQL Server 6.5 table, open the table in datasheet view, and right-click a column heading and select Sort Descending, #Deleted appears as the value for some of the records. CAUSE ===== The problem occurs when variable length fields have padded spaces stored in the SQL Server table. The worktable used for sorting does not maintain the padded spaces, so Microsoft Access thinks that the values have been changed. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. MORE INFORMATION ================ This problem may also occur when you connect from Microsoft Access for Windows 95. The client computer should use a SQL Server 6.5 version of the SQL Server ODBC Driver (Sqlsrvr32.dll version 6.50.0201 or higher). If you connect to SQL Server 6.5 with the SQL Server 6.0 drivers, you will continue to have this problem. Q166188 - FIX: Server Stops/AV on Client Disconnect w/ an Open Transaction ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, versions 6.5 Service Pack 1 and Service Pack 2 ------------------------------------------------------------------------- BUG #: 16290 (Windows NT: 6.5) SYMPTOMS ======== When a client disconnects without a commit or a rollback on a transaction involving a global temporary table, and if the temporary table is being accessed by another client, it generates an access violation (AV) and the server stops responding. When this happens, the MSSQLServer service is not controllable using Service Control Manager functions, and must be closed by using Windows NT Resource Kit utilities or by shutting down Windows NT. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5 Service Pack 1 and 2. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact yourprimary support provider. Q165857 - FIX: Delete/Rollback May Cause AV on Table w/Text Col & Triggers ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16719 SYMPTOMS ======== Issuing a delete statement on a table from within a transaction, followed by a rollback may cause access violations (AVs) and a variety of errors including error 840, 826, and bufwait timeouts. The server may stop responding. Sometimes SQL Server will not shut down after this problem occurs, and you need to cycle Windows NT Server to recover SQL Server. This problem will only occur if both of the following conditions are true: - You have a table that has at least one text column. - You have a delete trigger that deletes further rows in the same table. These symptoms may vary on different hardware platforms, but when exposed to this error, an AV is always generated. WORKAROUND ========== To work around this problem, do any one of the following: - Remove the delete trigger from the table in question. - Remove the delete statement from within the user-defined transaction. - Change table schema so that it does not require any text columns. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q165756 - FIX: A SELECT with a Large GROUP BY Clause May Return Error 614 ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16675 (6.5) SYMPTOMS ======== Doing a SELECT that contains a large GROUP BY list may result in error 614 and termination of the client session. The following is error 614: Msg 614, Level 21, State 1 A row on page %ld was accessed that has an illegal length of %d in database '%.*s'. The SQL Server is terminating this process. You may experience this problem if the total length of the columns making up the GROUP BY clause exceeds 512 bytes. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q165754 - FIX: INT Divide by Zero Exception Error When Running a Query ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 Service Pack 2 ------------------------------------------------------------------------- BUG #: 16455 (Windows NT: 6.5) SYMPTOMS ======== An INT divide by zero exception error occurs when you run a query joining tables with wide rows. The scenario that reproduced this bug was a select with a join between a very wide table (2,698 bytes row length) and a view with one where clause. The handled exception error stops the query. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5 Service Pack 2. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. MORE INFORMATION ================ The following query caused the access violation: select pr.*,p.* from tblprospects pr, tblprospectsdetail p where pr.prospect_key=(select min(p2.prospect_key) from tblprospects p2) and pr.prospect_key=p.prospect_key You can usually avoid the exception error by reducing the select list or the where conditions. Q165621 - FIX: Error 231 After Opening a Server Side Cursor ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 - Microsoft SQL Server driver, version 2.65 - Microsoft Open Database Connectivity, versions 2.5 and 3.0 ------------------------------------------------------------------------- BUG #: 16328 (2.65.0240) SYMPTOMS ======== In a multi-threaded application that has two threads, when the first thread does a SQLExecDirect of a select statement and the second thread does a SQLCancel in the middle of the select, the driver does not get a response back from the server, and therefore closes the connection. The following error is reported by the Net-Library: Error 231: ConnectionTransact (GetOverLappedResult()). The SQL Server driver then reports the following error: szSqlState = "08S01", *pfNativeError = 0, *pcbErrorMsg = 61 szErrorMsg = "[Microsoft][ODBC SQL Server Driver]Communication link failure" WORKAROUND ========== There is no known workaround at this time. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server driver version 2.65. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available. Q165599 - FIX: Datalength Function Returns 0 for NULL Text Columns ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16397 (WINDOWS: 6.5) SYMPTOMS ======== Select datalength(textcol) from t1 returns 0 if the parameter is a text column that contains NULL. WORKAROUND ========== To work around this problem, convert the NULL text column to varchar, and use the result as the parameter for the datalength function, as shown in the following example: select datalength(convert(varchar(255),textcol)) from t1 STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q165597 - FIX: Convert to Text Function w/ NULL Input Returns Blank String ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16417 (WINDOWS: 6.5) SYMPTOMS ======== Select convert(text,NULL) returns a blank string instead of NULL. WORKAROUND ========== To work around this problem, use select convert(varchar(255),NULL). STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q165596 - FIX: HOLDLOCK on READTEXT Causes Error 7134 ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 15738 (Windows NT: 6.5) SYMPTOMS ======== When you use HOLDLOCK with READTEXT, the query fails with the following error message: Msg 7134 Level 15 State 2: The text table and the table referenced by the text pointer disagree. WORKAROUND ========== Do not use HOLDLOCK with READTEXT in Microsoft SQL Server version 6.5. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q165579 - FIX: DBCC NEWALLOC May Cause Incorrect Database Usage Count ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, versions 6.0 and 6.5 ------------------------------------------------------------------------- BUG #: NT: 13108 (6.00) 16262 (6.50) SYMPTOMS ======== After you run the DBCC NEWALLOC command, the database usage count is left one more than the actual count. This would prevent you from setting the database in single user mode. The count remains incorrect until SQL Server is restarted. WORKAROUND ========== Run DBCC CHECKALLOC instead. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server versions 6.0 and 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. MORE INFORMATION ================ The same problem may occur if you run "DBCC FIX_AL (database_name)". Q165560 - FIX: Existing Fill Factor Changed w/ Rebuild of Clustered Index --------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 --------------------------------------------------------------------------- BUG #: 16418 (6.5) SYMPTOMS ======== After rebuilding the clustered index, Sysindexes.OrigFillFactor for all the existing non-clustered indexes on a table gets overwritten to that of the clustered index OrigFillFactor value. This happens only when the index is rebuilt using the "DBCC DBREINDEX" routine. WORKAROUND ============ When rebuilding the clustered index, avoid using DBCC DBREINDEX, and instead use the ANSI SQL commands DROP INDEX and CREATE INDEX. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q165559 - FIX: Error 403 on Select w/ Nested Subqueries, Sums, and Groups ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16381 (6.50) SYMPTOMS ======== While you are running a complex query consisting of a select with a number of nested subqueries, aggregate functions such as sum() and/or count(), and a 'group by' clause, you may receive the following error message: Msg 403, Level 16, State 1 Invalid operator for datatype op: UNKNOWN TOKEN type: decimal WORKAROUND ========== To work around this problem, avoid running such complex queries described above. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q165558 - FIX: AV on cursor declare using select on view w/ order by ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16383 (6.5) SYMPTOMS ======== You may receive a thread level access violation (AV) when you use the DECLARE CURSOR statement. This error occurs if both of the following conditions are true: - The cursor's select statement has an order by and operates against a view that is defined to use at least two tables. -and- - Each table used by the view has a compound unique index. WORKAROUND ========== To work around this problem, try changing the cursor type by adding the keywords SCROLL or INSENSITIVE to the DECLARE CURSOR statement. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q165557 - FIX: Thread AV in SP1 Using a Cursor Defined w/Constant and View ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 Service Pack 1 ------------------------------------------------------------------------- BUG #: 16333 (6.50.213) SYMPTOMS ======== You may receive a handled access violation (AV) when you declare a cursor using a constant in the select list and a view in the from clause. This problem only occurs if the view referenced in the cursor declaration contains a group by and a correlated subquery. WORKAROUND ========== Avoid the conditions mentioned in the symptoms section. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5 Service Pack 1. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q165513 - FIX: Running a Global Temporary Stored Procedure May Cause AV ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16498 SYMPTOMS ======== You may receive a handled access violation (AV) when you run a global temporary stored procedure. This problem occurs if the process that created the global temporary stored procedure exits or is killed just as another process tries to use that global stored procedure. The following is the sequence of events that causes this problem: 1. Process A creates a global temporary stored procedure. 2. Process B starts to run the global temporary stored procedure created by Process A. 3. Process A then exits or is killed. WORKAROUND ========== To work around this problem, ensure that the process that creates the global temporary stored procedure stays in existence until all other processes are done using its procedures. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q165512 - FIX: Err 1203 Running Dynamic Cursor in a Stored Procedure ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 Service Pack 2 ------------------------------------------------------------------------- BUG #: 16523 SYMPTOMS ======== A cursor that is retrieving data from within a stored procedure may fail with error 1203 "Caller of lock manager is incorrectly trying to unlock an unlocked object. Spid=%d locktype=%d dbid=%d lockid=%id". This error may happen a few times, or it may go into an endless loop in the errorlog, quickly filling up the disk it resides on. This problem only occurs on SQL Server version 6.5 Service Pack 2. CAUSE ===== The problem occurs when SQL Server upgrades a page lock to a table lock. The dynamic cursor's connection is not informed of the upgrade, and repeatedly tries to unlock the page lock. WORKAROUND ========== To work around this problem, change the cursor from a dynamic cursor to a SCROLL cursor by adding the keyword SCROLL to the cursor create. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5 Service Pack 2. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q165511 - FIX: A Derived Table Using a Group By May Cause Error 8158 ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16571 SYMPTOMS ======== If you drop and then rebuild a table that a stored procedure relies on, the stored procedure may fail with error 8158 "%s has more columns then were specified in the column list". The stored procedure will also fail with this error if the database is dumped and then restored. For this problem to occur, the stored procedure must have a derived table that is based on the table that was dropped and re-created and uses a GROUP BY to retrieve its data. The example below illustrates the problem if table "test" is dropped and re- created. On versions of SQL Server 6.5 before Service Pack 2, the error occurs when you attempt to create the procedure below, due to bug 15976. Once Service Pack 2 has been applied, you can create and run the procedure until the underlying table is dropped or the database is dumped and restored. Example: create proc test_proc_1 as select * from (select col1, col2 from test group by col1, col2 ) as x(col1, col2) return go WORKAROUND ========== To work around this problem, rewrite the query so that it does not use a GROUP BY clause. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q165510 - FIX: Numeric and Decimal Datatypes Do Not Always Use an Index ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 Service Pack 2 ------------------------------------------------------------------------- BUG #: 16301 SYMPTOMS ======== A query can be very slow if the qualifying column used in the where clause is either a numeric or decimal data type. The query can be slow even if the columns have indexes. A show plan of the query shows that the optimizer chooses to do a table scan rather than use the index. This problem only occurs in Microsoft SQL Server version 6.5 Service Pack 2. Earlier versions of SQL Server do not have this problem. WORKAROUND ========== Change your query to use another column as the qualifier, or use a composite index with the first part of the index key not being a decimal or numeric data type. Another workaround is to change the qualifying column used in the where clause to truly be numeric or decimal. For example, instead of using 9, use 9.0, or do a convert to numeric or decimal. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5 Service Pack 2. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q165389 - FIX: Spid Missing with Trace Flag 4032 on RPC Events ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16566 (6.5) SYMPTOMS ======== When using trace flag 4032 to trace incoming queries to SQL Server, RPC events do not document the process id (spid) or the time that the event occurred. WORKAROUND ========== Use the SQL Trace utility, which will document this information. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. MORE INFORMATION ================ With the 4032 trace flag enabled, RPC events are normally documented as EXECRPC in the errorlog. ODBC clients and DB-Library clients (that use the stored procedure functions) create RPC events instead of Language events. Q165166 - FIX: 'Load Tran' May Cause 806, 605, 2620, or 6902 with IRL on ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG#: 16666 (6.5) SYMPTOMS ======== With Insert Row Locking (IRL) turned on, if lock escalation occurred during an index page split, and the transaction was still open when 'dump tran' was performed, 'load tran' using this dump file may result in any one of the following error messages: - Msg 806, Level 21, State 1, Line 1 Could not find virtual page for logical page %Id in database '%.*s' - Msg 605, Level 21, State 1, Line 1 Attempt to fetch logical page %ld in database '%.*s' belongs to object '%.*s', not to object '%.*s'. - Msg 2620, Level 21, State 3, Line 1 The offset of the row number at offset %d does not match the entry in the offset table of the following page: %S_PAGE. - Msg 6902, Level 21, State 0, Line 1 Page timestamp value falls between the old and new timestamps from log. Page #=%ld, object id = %ld, page timestamp=%04x %08lx. Log: old timestamp=%04x %08lx, new timestamp=%04x %08lx. CAUSE ===== The log record type was set to IRL at beginning of the transaction, but size was set to something else since lock escalation had occurred. WORKAROUND ========== It is possible to avoid this problem by increasing sp_configure option 'LEThreshold Maximum'. See SQL Server Books-Online for more information on this option. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q165136 - FIX: SQL 6.5 Does Not Allow Updates to a View If Grant Specified ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG#: 16509 (6.50) SYMPTOMS ======== When you use the grant statement and specify column names that can be updated, an update statement fails if you use a column with select permission in the where clause. The problem occurs when setting column level update permissions to a view. Suppose you revoke update on column xxx but grant update on all other columns to public. When a user tries to update the data in the view, but not the column with the permissions revoked, he or she will receive the following error: Error 230 Severity 14 UPDATE permission denied on column xxx of object yyy, database zzz, owner dbo STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q165131 - FIX: Stress of Lock Limit Threshold Causes Bufwait Error Message ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG#: 16530 (6.5) SYMPTOMS ======== The SQL Server errorlog file may contain one or more of the following errors if there is stress on the SQL Server lock limit threshold: - Error : 1204, Severity: 19, State: 3 SQL Server has run out of LOCKS. Re-run your command when there are fewer active users, or ask your System Administrator to reconfigure SQL Server with more LOCKS. - Error : 5904, Severity: 17, State: 0 Background checkpoint process suspended until locks are available. Continued stress may cause one or more of the following errors: - bufwait: timeout, BUF_IO, bp 0xee55e0, pg 0x26f7, stat 0x1004/0x6, obj 0x8, bpss 0xfa31f0 - udread: Operating system error 6(The handle is invalid.) on device 'C:\MSSQL\DATA\devTempdb.DAT' (virtpage 0x020022f7). - EXCEPTION_ACCESS_VIOLATION raised, attempting to create symptom dump - WARNING: Pss found with open sdes; pspid 11, psuid 1, pcurdb 1, system table entry 0, sdesp 0xf51ba0, objid 1 - Error : 826, Severity: 20, State: 1 Attempt made to hold allocation page 11520 that is - Error : 602, Severity: 21, State: 3 Could not find row in Sysindexes for dbid '2', object '16003088',index '0'. Run DBCC CHECKTABLE on Sysindexes. - Error : 913, Severity: 22, State: 2 Could not find row in Sysdatabases with database id -27352. Run DBCC CHECKTABLE on Sysdatabases. WORKAROUND ========== To work around this problem, use sp_configure to increase the locks value. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q165099 - FIX: OrigFillFactor Reports Wrong Value for Non-Clustered Index -------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 -------------------------------------------------------------------------- SYMPTOMS ======== OrigFillFactor reports the incorrect value for a non-clustered index once a clustered index is rebuilt with "zero" fillfactor using DBCC DBREINDEX. Another possible side effect of specifying a zero fillfactor is the intermittent problem of the users of the database in question experiencing error 1105 on the "default" segment, even though there is plenty of space available for this segment within the database. This problem is described more fully in the following article in the Microsoft Knowledge Base: ARTICLE-ID: Q164578 TITLE : FIX: Error 1105 on Default Segment When Running DBCC DBREINDEX Shutting down and restarting the server may temporarily clear the symptoms of this problem. However, a shutdown is unnecessary when using the solution described in the WORKAROUND section of this article. WORKAROUND ========== To work around this problem, specify a non-zero fillfactor when rebuilding the clustered index. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q164997 - FIX: Dump to Tape Fails Without Error in Errorlog -------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 -------------------------------------------------------------------------- BUG#: 16432 (6.5) SYMPTOMS ======== A dump or load from tape may fail to report errors in the SQL Server errorlog when the client receives the following error: Msg 3201, Level 16, State 1 Can't open dump device '', device error or device off line. Please consult the SQL Server error log for more details. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q164972 - FIX: Err 7134 w/ Text/Image Col. Update Using 'Where Current Of' ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 Service Pack 2 ------------------------------------------------------------------------- BUG#: 16518 (6.5 SP2) SYMPTOMS ======== An attempt to update a text and/or image column from a cursor based on the position by using 'where current of' may cause error 7134: The text table and the table referenced by the text pointer disagree. WORKAROUND ========== To work around this problem, initialize text and image columns with values during inserts, so that a valid text pointer is created. Avoid updates of binary and/or text columns from within a cursor. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5 Service Pack 2. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q164734 - FIX: Repetitive Connect/Disconnect May Shut Down SQL Server ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16416 (6.5) SYMPTOMS ======== If a client application calls dbopen/dbclose (using DB-Library) or SQLConnect/SQLDisconnect (using ODBC) repetitively, it may cause the following error and shut down SQL Server. Web Servers also exhibit this behavior. 18001 Process %d entered sequencer without owning dataserver semaphore This problem only happens on computers running Windows NT Server 4.0, and it occurs more frequently when both the clients and server are running on the same computer. In the scenarios where Microsoft was able to reproduce this error, the processor speed of the server needed to be fairly high, and even then this problem only surfaced occasionally. WORKAROUND ========== To work around this problem, either move the client application to a separate computer or try to take advantage of the 'Connection Pooling' functionality provided by ODBC Driver Manager 3.0. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q164691 - FIX: Insert Through View May Cause Duplicated Identity Value ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16450 (6.5) SYMPTOMS ======== Inserts performed through a view may generate duplicated identity values in the base table if SQL Server's 'open objects' is not configured high enough. If a table has a primary key or a unique index, the insert fails with either of the following errors: 2627 14 Violation of %s constraint '%.*s': Attempt to insert duplicate key in object '%.*s'. -or- 2601 14 Attempt to insert duplicate key row in object '%.*s' with unique index '%.*s'. The following message usually appears in SQL Server before the problem: Warning: OPEN OBJECTS parameter may be too low attempt was made to free up descriptors in localdes() Run sp_configure to increase parameter value. WORKAROUND ========== To work around this problem, use sp_configure to increase 'open objects' (see the documentation on DBCC MEMUSAGE to find the memory overhead of each 'open object'). STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q164578 - FIX: Error 1105 on Default Segment When Running DBCC DBREINDEX ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16286 SYMPTOMS ======== When you run DBCC DBREINDEX with a fillfactor of 0, you may experience error 1105 (Can't allocate space) or errors on the "default" segment, even though there is plenty of space available for this segment within the database. CAUSE ===== Running DBCC DBREINDEX(,'',0) exposes this intermittent bug. Specifying a non-zero fillfactor avoids the problem. In some cases, once you have experienced this error, you may not be able to build any new indexes on any table, even in other databases on the same SQL Server. The fillfactor used when originally creating the index makes no difference to this behavior. Also, using the "SORTED_DATA" option has no effect on the problem. Shutting down and restarting the server may temporarily clear the symptoms of this bug. However, a shut down is unnecessary when using the workaround described below. WORKAROUND ========== To work around this problem, specify a fillfactor other than 0; that is, explicitly code the appropriate fillfactor for the index in question. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q164577 - FIX: Error 803 on INSERT into Table with Multiple Indexes ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16335 SYMPTOMS ======== When you insert data into a table that has a clustered index and at least two non-clustered indexes, you may encounter the following error: Msg 803, Level 20, State 1 Unable to place buffer 0x%lx holding logical page %ld in sdes for object '%.*s'3/4either there is no room in sdes or buffer already in requested slot. The SQL Server is terminating this process. DB-Library: Possible network error: Write to SQL Server Failed. Net-Library error 232: ConnectionWrite (WriteFile()). DB-Library Process Dead - Connection Broken WORKAROUND ========== To work around this problem, do either of the following: - Drop some or all of the non-clustered indexes for the duration of the insert. -or- - Drop the clustered index for the duration of the insert. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q164576 - FIX: Error 2610 on Insert into Table with Multiple Indexes ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16591 SYMPTOMS ======== If you use "INSERT INTO SELECT
ON, he or she may encounter the following error message (8104): Current user is not the DBO or object owner for table
, unable to perform SET operation. CAUSE ===== This error only occurs if the DBO is not the object owner for the relevant table, or is not the system administrator (SA). WORKAROUND ========== To work around this problem, do either of the following: - Set the DBO to be the SA. -or- - Make the DBO the object owner for the relevant table. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server versions 6.0 and 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q161741 - FIX: Dump Aborts if PG_DMPTRLR Bit Set (MSG 3220) --------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 --------------------------------------------------------------------------- SYMPTOMS ======== When you attempt to dump a database in which the PG_DMPTRLR bit has been set, the dump will terminate prematurely with the following error: Msg 3220, Level 16, State 1, Server , Line 1 page of database has the PG_DMPTRLR bit set Msg 3204, Level 16, State 1, Server , Line 1 Operator aborted Dump or Load. WORKAROUND ========== This problem occurs most frequently in databases in which other corruption problems exist, typically in the form of page or extent allocation errors. Correction of existing database errors usually results in the ability to perform a successful dump of the database for which the error 3220 has been reported. If the error occurs on a page for which a 2540 error exists, run the DBCC FIX_AL() command against the database in question. Deallocation of the mis- allocated page will correct the problem under this condition. NOTE: it is necessary to carefully review the output from DBCC FIX_AL(), to ensure that error 7915 has not been reported. If error 7915 has been reported, the FIX_AL() will not correct the allocation discrepancies, and you will receive the following message: Allocation page extid is referenced, but there are no referenced pages within this extent. Contact Tech Support for object . Should message 7915 be reported from DBCC FIX_AL(), contact technical support for assistance in resolving this error. In cases in which DBCC NEWALLOC() does not report allocation errors, the error may exist on a correctly allocated data page. Identify the object on which the error occurs (see the SQL Server Troubleshooting Guide, Appendix A, "Associating a page with an Object"). If the error occurs on a table object, export the data, drop and re-create the table, and import the data again. It is also possible that the error will occur on an index page. Should this situation arise, drop and re-create the index to which the page specified in the error message belongs. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. MORE INFORMATION ================ SQL Server versions prior to 6.5 did not check to see if this bit was set while performing a database dump. This allowed for situations in which an error 605 would occur when attempting to reload a database that was dumped while the PG_DMPTRLR bit was set. The check for the PG_DMPTRLR bit, which was added in SQL Server 6.5, was designed to address SQL Server 6.0 bug 11857, "Need to check and clear PG_DMPTRLR status at DUMP DATABASE time." Q161726 - FIX: AV Selecting from VIEW with DISTINCT and Join ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 Service Pack 1 and 2 ------------------------------------------------------------------------- BUG #: 16361 (Windows NT: 6.5) SYMPTOMS ======== You may receive a handled access violation (AV) when you attempt to select from a view that was created with a DISTINCT clause, and there is a natural JOIN between base tables inside a stored procedure. When the access violation occurs, you may find the following error message in the SQL Server errorlog: spid10 EXCEPTION_ACCESS_VIOLATION raised, attempting to create symptom dump kernel The current contents of process' input buffer are 'exec myproc' WORKAROUND ========== To work around this problem, place the SELECT statement of the stored procedure inside an EXEC() statement. For example, if the SELECT against the view was the following: select * from view Change the statement to: exec("select * from view") Otherwise, move the query outside of the stored procedure, or avoid using the DISTINCT keyword in the view definition. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5 Service Pack 1 and 2. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q161645 - FIX: Sp_Cursor Positioned Update Causes Access Violation ------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------- BUG #: 16439 (6.5) SYMPTOMS ======== Under rare conditions, an sp_cursor positioned update may cause a handled access violation (AV). This can usually be identified by the "sp_cursor" in the input buffer following the errorlog stack trace. The sp_cursor command is typically sent by DB-Library or the ODBC Microsoft SQL Server driver in response to certain DB-Library or ODBC calls. The problem conditions require opening multiple cursors with a certain sequence of differing concurrency options on a stored procedure, such that the cached access plan of a previous cursor is reused by a subsequent cursor with a different concurrency option. The cached plan must be that of a read-only keyset-driven cursor; the stored procedure must reference a table containing a timestamp column; the subsequent cursor must be non-read- only; and a positioned update must be done. WORKAROUND ========== To work around this problem, do any of the following: - Use trace flag -T7502 to disable caching of extended cursor access plans. - Create the stored procedure on which the cursor is declared using the WITH RECOMPILE keyword. - Change to a different sequence of cursor operations, such that cursors are not opened with differing concurrency modes on a given object. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q161599 - FIX: Temporary Dump Devices May Be Created with the Wrong Name --------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, versions 6.0 and 6.5 --------------------------------------------------------------------------- BUG #: 15681 (6.0) 16411 (6.5) SYMPTOMS ======== If temporary disk dump devices are used that contain spaces in the path or file name, the dump file may be created in the incorrect directory, or may be created with the incorrect name. However, the dump is valid to load if the file is specified correctly. WORKAROUND ========== To work around this problem, do either of the following: - Create disk dump devices with the stored procedure sp_addumpdevice or SQL Enterprise Manager, and then dump and load with the device name rather than using the temporary dump device syntax. -or- - Do not use spaces in the path or file name for temporary disk dump devices. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server versions 6.0 and 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. MORE INFORMATION ================ Temporary disk dump devices are created by using the DISK="" for the dump device. The following example illustrates the problem: DUMP DATABASE pubs TO DISK='c:\mssql\backup\disk test.dat' Instead of creating a dump file named "disk test.dat," a file called disk. is created. Q161125 - FIX: DUMP or LOAD with Tape May Fail on Windows NT 3.51 --------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 --------------------------------------------------------------------------- BUG #: 16346 (6.5) SYMPTOMS ======== A SQL Server dump or load with a tape may fail on a computer running Windows NT 3.51 and some SCSI controllers. WORKAROUND ========== To work around this problem, do any of the following: - Try to put the tape drive on a different SCSI adapter. - Upgrade to Windows NT 4.0. - Dump to disk files instead of tape files. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. MORE INFORMATION ================ The following are some of the possible errors that can be seen in the SQL Server errorlog: Error : 3201 Can't open dump device '', device error or device off line. Please consult the SQL Server error log for more details. kernel Unnamed tape \\.\tape0 mounted on tape drive \\.\tape0 kernel tprhdrs: failed to seek Tape Mark, operating system error: 1101 (A tape access reached a filemark.) kernel tbsreadhdr: Tape rejected kernel tprvol: read returned 1104 (no more data is on the tape.) reading volume header Q160584 - FIX: Rollback in Single User Database Causes Msg 3307 and AV ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, versions 6.0 and 6.5 ------------------------------------------------------------------------- BUG #: 15657 (Windows NT: 6.0) 16163 (6.5) SYMPTOMS ======== If a transaction is rolled back in a 'single user' database, a Msg 3307 error will occur, followed by a handled access violation. The server will stop responding, and no connections or queries will be allowed into the server at this point. WORKAROUND ========== To work around this problem, avoid rolling back transactions in a 'single user' database. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server versions 6.0 and 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. MORE INFORMATION ================= When this problem occurs, the following type of error information is returned to the client application (specific process and page information will likely be different for each scenario): Msg 3307, Level 21, State 1 Process 13 was expected to hold logical lock on page 544. Error while undoing log row in database 'pubs'. Rid pageid is 0x315 and row num is 0x8. The SQL Server is terminating this process. The following error information is written to the SQL Server errorlog: spid13 Error : 3307, Severity: 21, State: 1 spid13 Process 13 was expected to hold logical lock on page 544. spid13 Error : 3307, Severity: 21, State: 1 spid13 Process 13 was expected to hold logical lock on page 544. kernel WARNING: Process being freed while holding Dataserver semaphore spid-1 Error : 3307, Severity: 21, State: 1 spid-1 Process -1 was expected to hold logical lock on page 544. [handled access violation information follows these messages] This problem may occur if the transaction is rolled back explicitly using the ROLLBACK command, or if the client connection is terminated before the transaction can be committed. SQL Server may stop responding under this scenario, and cannot be shut down normally as a service. If the server was started from the command line, it may be possible to shut it down by using a CTRL+C key sequence. In either case, when the server is restarted, this transaction should be properly rolled back by recovery. Q159782 - FIX: Concurrent CREATE TABLE & ALTER TABLE May Cause Deadlock ------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, versions 4.2x, 6.0 and 6.5 ------------------------------------------------------------- BUG #: 16016 (WINDOWS: 6.50) SYMPTOMS ======== If you perform a CREATE TABLE and an ALTER TABLE ADD CONSTRAINT concurrently, you may receive deadlocks on system tables. WORKAROUND ========== To work around this problem, place the ALTER TABLE in a user transaction preceded by a SELECT (UPDLOCK) on syscolumns and sysindexes, as shown below: begin tran select count(*) from syscolumns(UPDLOCK) where id=object_id('TT1a') select count(*) from sysindexes(UPDLOCK) where id=object_id('TT1a') alter table TT1a add constraint TT1idx1a PRIMARY KEY (a, b, c, d, e, f, g, h) commit tran STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server versions 4.2x, 6.0, and 6.5. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q159744 - FIX: Message 2 After Issuing ROLLBACK TRANSACTION Command ------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------- BUG #: 16292 (6.5) SYMPTOMS ======== After you issue a ROLLBACK TRANSACTION command, you receive the following error: Msg 2, Level 16, State 16 However, there is no such thing as msg 2. In addition, as with any server message causing a DB-Library call to fail, it is likely that you will receive DB-Library error 10007 "General error, check for other server messages," as a signal to check for server messages. This problem occurs if you have done the following in sequence: 1. Created a local temporary table in a stored procedure. 2. Entered a user-defined transaction. 3. Cancelled the query. 4. Issued the ROLLBACK TRANSACTION command. WORKAROUND ========== Other than the unexpected DB-Library error 10007 and server message 2 being returned to the client, there are no other adverse effects. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. MORE INFORMATION ================ The sequence that causes this problem is normal for many applications. It is common to get blocked on a lock while in a user-defined transaction, then timeout and have the DB-Lib message handler return INT_CANCEL or otherwise issue dbcancel(). If the other criteria are met as well, you will receive message 2. However, message 2 does not usually cause any problems. Applications should always be prepared to receive any server message. They should not be hard coded to expect specific numbers or types of server messages. Q158464 - FIX: Sp_columns Returns More Than One Row for a Column ----------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ----------------------------------------------------------------------- BUG #: 15895 (NT, 6.5) SYMPTOMS ======== If a table contains a column bound to a default with a larger comment, the system stored procedure sp_columns may return more than one row for each column of a user table. This problem causes error 3191, "Can't define field more than once," in the Microsoft Jet engine if you try to attach such a table. WORKAROUND ========== To work around this problem, do one of the following: - Drop and re-create the default without any comments. -or- - Use default constraints rather than defaults. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. MORE INFORMATION ================ The following script illustrates the problem: create table t1(c1 int not null) go /****** Object: Default dbo.dev_zero Script Date: 10.09.96 18:39:01 ******/ /****** Object: Default dbo.dev_zero Script Date: 10.09.96 18:37:49 ******/ /****** Object: Default dbo.dev_zero Script Date: 10.09.96 18:37:08 ******/ create default dev_zero as 0 go exec sp_bindefault dev_zero, 't1.c1' go sp_columns t1 go Q156265 - FIX: Create Nonclustered Index Blocks Selects from Table ---------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ---------------------------------------------------------------------- BUG #: 15618 (6.50) SYMPTOMS ======== When creating a nonclustered index on a table with SQL Server 6.5, the indexing process will block all activity in the table, including selects, until the indexing is completed. WORKAROUND ========== Generate nonclustered indexes in off-hours when users are not accessing the table. MORE INFORMATION ================ When creating a nonclustered index, SQL Server takes an SH_TABLE lock on the underlying table to prevent changes to the data. This lock can be seen by looking at the output of the sp_lock stored procedure. SQL Server is also locking an internal data structure during the indexing process, which also prevents select operations from accessing the table. The selecting process may not be killed during this time, and will show as blocked with a waittype of 0x0012. The waittype can be seen by looking at the row in sysprocesses for the corresponding spid. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem does not occur in SQL Server 6.0, and has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q151573- FIX: Striped Dump to 7 or More Devices is Not Logged ---------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ---------------------------------------------------------------------- BUG#: 15253 SYMPTOMS ======== When doing a striped dump to 7 or more devices, the dump event is not logged in the SQL Server errorlog nor in the Windows NT event logs. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. MORE INFORMATION ================ Even though the dump event is not logged, the dump completes successfully. A load from the dump set is successful. Q135866 - FIX: SEM - Edit Devices Dialog Displays Negative Device Size ---------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, versions 6.0 and 6.5 ---------------------------------------------------------------------- BUG # 11171 (NT: 6.00, 6.50) SYMPTOMS ======== SQL Enterprise Manager (SEM) can sometimes show a negative number for the size of a device in the Edit Devices dialog. This problem only occurs if the device being edited is on a drive that has more than 2 gigabytes of free space. When this problem occurs, the size of the device cannot be changed in the dialog box. WORKAROUND ========== To work around this problem, use the DISK RESIZE command to manually increase the size of the device. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server versions 6.0 and 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. Q164288 - FIX: Replication Filter Stored Procedures Not Upgraded to 6.5 ------------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 ------------------------------------------------------------------------- BUG #: 16372 (Windows NT: 6.5) SYMPTOMS ======== Replication filter stored procedures created in Microsoft SQL Server 6.0 are not upgraded during the upgrade process to Microsoft SQL Server 6.5. Because these procedures are not upgraded, any articles that rely on these procedures may not have their replication jobs processed. No error is provided by replication when running the filter procedure under these conditions. The upgrade process (either by an actual upgrade using Setup.exe or LOAD DATABASE) should provide a message in the SQL Server errorlog or Upgrad1b.out file (found in the Mssql\Install directory) indicating a problem upgrading a replication filter procedure. NOTE: Any procedure not upgraded will be listed by name. The error does not indicate it is a replication filter procedure. NOTE: In Microsoft SQL Server 6.0 and 6.5, a replication filter stored procedure is designated in sysobjects with a type = 'RF' value. WORKAROUND ========== To properly upgrade any replication filter procedure, you must drop and re- create the replication filter procedure. There are basically two techniques to properly re-create a replication filter procedure: - Drop and re-create the article that is associated with the procedure using SQL Enterprise Manager. Specifying a restriction clause when creating the article automatically generates a filter stored procedure. - Manually drop and re-create the procedure by using the Transact-SQL commands DROP PROCEDURE and CREATE PROCEDURE. If you do not have a script to perform this operation, note that the syscomments table does not contain the keyword FOR REPLICATION necessary to identify a replication filter procedure when running CREATE PROCEDURE. Furthermore, if you do not use SQL Enterprise Manager to associate the filter procedure with an article, you can directly run master.dbo.sp_changearticle. See the Transact-SQL Reference for more information on running sp_changearticle to specify a filter procedure for an article. STATUS ====== Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider. MORE INFORMATION ================ Because SQL Server 6.5 Service Pack 3 is applied after upgrading from SQL Server 6.0 to SQL Server 6.5, replication filter procedures are not upgraded automatically by just applying the service pack. However, after upgrading to SQL Server 6.5 and applying Service Pack 3, you can run the stored procedure master.dbo.sp_db_upgrade '' to upgrade all replication filter procedures. Sp_db_upgrade is documented in the SQL Server Setup 6.0 book in Chapter 7, "Installation Troubleshooting." If you load a database from SQL Server 6.0 with replication filter procedures into a server running SQL Server 6.5 Service Pack 3, the procedures will be upgraded correctly without requiring any further action.