FIX: SQL Server 6.0 Service Pack 3 Fixlist

• Microsoft SQL Server, version 6.0 Service Pack 3

The following is a list of fixes and various other improvements that have been made in the Microsoft SQL Server version 6.0 Service Pack 3. Service Pack 3 is now available from your primary support provider. For more information, contact your primary support provider.

Service Pack 3 includes all fixes previously released in Service Packs 1 and 2 for version 6.0. The list of fixes for Service Packs 1 and 2 are appended at the end of this article.

Please note that workarounds described have been provided for your information only. It is not necessary to implement these workarounds if you have the updated software.

LIST OF PROBLEMS CORRECTED IN SERVICE PACK 3

DB-Library Components

Q151301: FIX: dbcursorfetchex() Can Cause Blocking in DB-Library Q152063: FIX: DB-Lib Unable to Handle Burst of TDS Packets Under TCP/IP

Replication Components

Q150258 FIX: Long Database Names Can Cause AV in the Distribution Task

Server Components

Q136531: FIX: SELECT INTO From a ALTERed TABLE Cannot be Performed Q139620: FIX: @@IDENTITY in Nested Stored Procedure Returns NULL Q140265: FIX: CREATE INDEX Aborts with Error 1509 or 632 Q140332: FIX: Remote Backups and xp_loginconfig Fail With Access Denied Q148324: FIX: DECLARE CURSOR on View with DISTINCT Causes AV Q146637: FIX: DATALENGTH Function Returns 0 on 256 Byte Text Parameter Q147177: FIX: Complex T-SQL Batch May Hang During Query Plan Generation Q147414: FIX: Unhandled Exception When Running DBCC Checkdb Q149934: FIX: AV on FETCH From Updatable CURSOR Using VIEW Q152627: FIX: SELECT from Multiple Unjoined Tables Can Cause AV Q148219: FIX: Inconsistent Behavior with Global Temporary Tables Q148267: FIX: Correlated Subquery Nested 3+ Levels May Give Error 403 Q150955: FIX: Client Terminated on Repeated Unsuccessful Obj Resolution Q150988: FIX: Extraneous Locks During UPDATE on Table w/ > 15 Indexes Q152046: FIX: DBCC Traceon(3604,4031) Causes Unhandled Access Violation Q149174: FIX: Correlated Subquery on Datetime Col May Cause Client AV Q149917: FIX: 16933 Error on View Updated Through Server Cursor Q150936: FIX: Stored Proc Can Fail w/ Msg 1203 Using READ UNCOMMITTED Q151590: FIX: Error 4409 Generated When Using Multiple Database Views Q152414: FIX: Compatible Tape Drives May Not Read SQL Server Dumps Q152415: FIX: sp_processmail Gets Ambiguous Recipient Error Q149243: FIX: Insert/Select/(NOLOCK) w/ Rollback May Cause Error 3307 Q149711: FIX: Insert View with DEFAULT VALUES Causes AV Q149932: FIX: 3307 Lock Error on INSERT SELECT UNION Abort w/ Dup Key Q149938: FIX: AV on INSERT of UNIONed SELECTs to Table With DEFAULTs Q150882: FIX: Possible Handled AV w/ Sub-query Using NOT IN and '>' Q151408: FIX: Query w/ Group by All Can Take Too Long to Complete Q151605: FIX: Grant or Revoke Statements Fail When Used in an SP Q152214: FIX: UPDATE Using Aggregate and Arithmetic Operator Causes AV Q152264: FIX: Five-way Table Join w/OR May Cause Handled Exception Q152615: FIX: Syntax Error in SELECT List May Cause Handled AV Q151583: FIX: Select Using 'or' From View Defined w/ '>=' Can Cause AV Q151681: FIX: SELECT with CASE Can Cause Parser Error 403 Q152263: FIX: Query with Join in Subselect May Cause Handled Exception Q152800: FIX: GROUP BY Clause without Index Executes More Slowly

Q151301: dbcursorfetchex() Can Cause Blocking in DB-Library

SYMPTOMS

If a DB-library application calls dbcursorfetch() or dbcursorfetchex() successfully in one thread, subsequent calls to dbcursoropen() or dbclose() from other threads using the same DBPROCESS would be blocked. Sp_who and sp_lock show no blockage on the server side, and the DB-library application would appear to hang.

WORKAROUND

Use a separate DBPROCESS for each thread.

Q152063: BUG: DB-Lib Unable to Handle Burst of TDS Packets Under TCP/IP

SYMPTOMS

Under TCP/IP socket Net-Library, a DB-Library (DB-Lib) application may not be able to handle a large number of TDS packets that are sent back to the client very quickly. As a result, the following error occurs, which also breaks the connection:

   DB-Library: Possible network error: Read from SQL Server failed.
   Net-Library error 0: (null)

WORKAROUND

This problem only occurs under TCP/IP; therefore, you may want to use other IPCs (such as named pipes, IPX/SPX, or RPC) instead.

Also, reducing the window size on the client side TCP/IP can make the problem less likely to occur.

Q150258: BUG: Long Database Names Can Cause AV in the Distribution Task

SYMPTOMS

Long published and subscribed database names can cause an access violation (AV) failure of the distribution task with the following error reported in the Event Log:

   Event ID 212; Source: SQLExecutive; Category: Task Engine
   Event <task id> - <dist task name> caused an exception violation in the
   Distribution subsystem, and has been terminated.

WORKAROUND

Avoid using long names for published and subscribed databases. To avoid this particular problem, a database name of no longer than 10 characters is recommended for both the published and subscribed database.

Q136531: FIX: SELECT INTO From a ALTERed TABLE Cannot be Performed

SYMPTOMS

On Microsoft SQL Server version 6.0, you can not perform a SELECT INTO from a table that has been ALTERed to add an IDENTITY column.

A SELECT INTO will fail with the following error message:

   Msg 511, Level 16, State 2
   Updated or inserted row is bigger than maximum size (10 bytes)
   allowed for this table.

WORKAROUND

Instead of performing an ALTER TABLE to add the IDENTITY column, CREATE a TABLE with the identity column and then transfer the data to the new table with an INSERT operation.

Q139620: FIX: @@IDENTITY in Nested Stored Procedure Returns NULL

SYMPTOMS

The @@identity global variable will be NULL under the following conditions:

1. If it is queried in a stored procedure after a nested stored procedure call and insert.

2. The nested stored procedure performs an insert and queries the @@identity global variable as well.

WORKAROUND

Do not query the @@identity global variable from more than one stored procedure nesting level.

For example, modify the "add_test1" stored procedure (from example in the following MORE INFORMATION section) to the following code and it works properly:

   CREATE PROCEDURE add_test1 AS
   EXECUTE add_test2
   EXECUTE add_test2
   RETURN
   GO

Q140265: FIX: CREATE INDEX Aborts with Error 1509 or 632

SYMPTOMS

When you attempt to create an index on a table, the command may abort with one of the following two possible errors:

   Msg 1509, Level 20, State 3:
   Row compare failure

   Error: 632, Severity: 20, State: 1
   Memmove() was called with a length of %d - maximum allowed length is
   2048.

WORKAROUND

Increase the "sort pages" configuration parameter from the default of 64 to a higher number using the sp_configure system stored procedure. In most cases, increasing this parameter from the default of 64 to 128 will allow the index to be created; however, it can be increased to a maximum of 511 if necessary.

NOTE: Before modifying the number of sort pages, you need to issue the command:

   sp_configure 'allow updates', 1

   sp_configure 'allow updates', 0

Q140332: FIX: Remote Backups and xp_loginconfig Fail With Access Denied

SYMPTOMS

Database dumps to remote devices and the xp_loginconfig extended stored procedure can fail with access denied errors if Microsoft SQL Server is using Per Seat licensing mode.

For database dumps, the client will get a 3201 error and the Windows NT Application log of the computer SQL Server is running on will contain the following event:

   Event ID: 17055
   Type: Error
   Source: MSSQLServer
   Category: Kernel
   User: could be any user connected to SQL Server
   Description: Mesg 18204: dbswritecheck: Backup device '<device path
   and file>' failed to open, operating system error=5(Access is
   denied.)

   Unable to query SQL Server security information.

WORKAROUND

Switch the SQL Server security mode to mixed or integrated security.

Q148324: FIX: DECLARE CURSOR on View with DISTINCT Causes AV

SYMPTOMS

When you declare a cursor on a view with DISTINCT, the operation will cause a handled access violation (AV) resulting in the client process being terminated. The same behavior can occur in both Transact-SQL and in applications written to use server cursors. The behavior only occurs if the table has a unique index.

WORKAROUND

Rewrite the query so that you are using DISTINCT in the cursor declaration rather than in the view declaration. Another possibility is to drop the unique index on the table if it is not being used for integrity constraints. DB-Library and ODBC applications can also use their respective cursor libraries.

Q146637: FIX: DATALENGTH Function Returns 0 on 256 Byte Text Parameter

SYMPTOMS

If a text value of length 256 bytes is passed to a stored procedure as a parameter and in the stored procedure the DATALENGTH function is used to check the length of the parameter, it will return 0 instead of 256. If the text parameter's length is either greater than 256 or less than 256, the DATALENGTH function will return the correct length.

Q147177: FIX: Complex T-SQL Batch May Hang During Query Plan Generation

SYMPTOMS

The complex transact-SQL batch hangs when executed in SET NOEXEC ON mode. The transact-SQL query batch has two inserts with a co-related update statement. Execution with SET NOEXEC ON causes the connection to be stranded with CPU utilization at 100 percent. Execution with SET NOEXEC OFF causes the connection and locks to be stranded with CPU utilization at 100 percent.

WORKAROUND

Break the transact-SQL batch into smaller components. You can do this by adding "Go" between the transact-SQL statements.

Q147414: FIX: Unhandled Exception When Running DBCC Checkdb

SYMPTOMS

When running DBCC CHECKDB or CHECKTABLE, SQL Server may encounter an unhandled exception.

The last message in the errorlog prior to the exception will be the following:

   96/01/25 15:34:31.63 kernel
   upinit: warning, unable to raise priority of dbcc-worker thread

   96/01/25 15:34:31.72 kernel
   upinit(dbcc-worker) failed:  error code: 0x5
   (5(Access is denied.))

MORE INFORMATION

This problem will only occur if SQL Server is running in Per Seat licensing mode and is configured to use standard security, and even under these conditions, it is rare.

WORKAROUND

You can work around this problem by doing one of the following:

• Switch the SQL Server security mode to mixed or integrated security.
• Use SQL Server trace flag 2508 to disable parallel non-clustered index checking for DBCC CHECKTABLE. This has little performance impact on most servers, because to benefit from parallel checktable you must have buffer cache larger than the size of the table being checked.

Q149934: FIX: AV on FETCH From Updatable CURSOR Using VIEW

SYMPTOMS

Fetching from multiple updatable cursors using VIEWs of the same underlying table can get a handled exception.

WORKAROUND

Use cursors against the direct tables instead of views.

Q152627: FIX: SELECT from Multiple Unjoined Tables Can Cause AV

SYMPTOMS

A SELECT from multiple base tables with no join clauses may cause a handled Access Violation in Microsoft SQL Server 6.0

WORKAROUND

Simplifying the SELECT statement or restricting the number of rows or tables in the query will avoid the Access Violation.

Q148219: FIX: Inconsistent Behavior with Global Temporary Tables

SYMPTOMS

Global temporary tables exhibit inconsistent IF EXISTS behavior from multiple connections. The conditional check for table (with IF EXISTS) may return FALSE even if the global temp table exists. Create table statements executed after this will return the following error message:

   Msg 2714, Level 16, State 1
   There is already an object named '##tablename' in the database.

Q148267: FIX: Correlated Subquery Nested 3+ Levels May Give Error 403

SYMPTOMS

A correlated subquery nested to three levels or more may give the following error message:

   Msg 403, Level 16, State 1
   Invalid operator for datatype op: UNKNOWN TOKEN type: char

WORKAROUND

Split the query so that the nesting level is less than three.

Q150955: FIX: Client Terminated on Repeated Unsuccessful Obj Resolution

SYMPTOMS

During object resolution, the server terminates the client process if the object resolution is not successful for five consecutive attempts.

This applies only to version 6.0 Service Pack 2. The problem characteristics are completely different on SQL Server version 6.0 without any Service Packs installed. Please see the following Knowledge Base article for details:

   ARTICLE-ID: Q134659
   TITLE: FIX: Unexpected Behavior During Object Resolution

WORKAROUND

Ensure that all the views have proper base tables. Alternately, drop and recreate the views whenever the base tables are dropped.

Q150988: FIX: Extraneous Locks During UPDATE on Table w/ > 15 Indexes

SYMPTOMS

Running an UPDATE on a table results in more EX_PAGE locks when the table contains more than 15 indexes, even when the column being updated is not a part of any index. The same set of statements uses a smaller number of locks if there are 15 or fewer indexes on the updated table.

Q152046: FIX: DBCC Traceon(3604,4031) Causes Unhandled Access Violation

SYMPTOMS

Using the combination of trace flags 3604 (Sends trace output to the client) and 4031 (Prints both a byte and ASCII representation of the send buffers) can crash the server with an unhandled access violation.

For example:

dbcc traceon(3604) go dbcc traceon(4031) go

WORKAROUND

Use send trace output to the Errorlog file:

dbcc traceon(3605) go dbcc traceon(4031) go

Q149174: FIX: Correlated Subquery on Datetime Col May Cause Client AV

SYMPTOMS

The client thread may access violate if it has the datetime column in correlation, and multiple conditions in the WHERE clause of the correlated subquery and the main outer query.

WORKAROUND

Split the query into multiple queries by using intermediate temporary tables.

Q149917: FIX: 16933 Error on View Updated Through Server Cursor

SYMPTOMS

Applications attempting to update a view through SQL Server server cursors will receive the following error even if the operation observes the SQL Server requirement that updates through views only update a single base table:

   Msg 16933, Level 16, State 2
   The cursor does not include the table being modified.

WORKAROUND

Open the cursor with a select against the base tables instead of the view. Alternatively, open a second connection and issue an SQL update command based on key values through the second connection.

Q150396: FIX: Stored Proc Can Fail w/ Msg 1203 Using READ UNCOMMITTED

SYMPTOMS

If a stored procedure is executed after setting the TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED, the procedure can fail with the following error:

   Error : 1203, Severity: 20, State: 2
   Caller of lock manager is incorrectly trying to unlock an unlocked
   object.
   spid=<n> locktype=4 dbid=<n>  lockid=<n>

MORE INFORMATION

This problem occurs if a stored procedure plan is reused in cache that was compiled when the user was not using READ UNCOMMITTED. If a new plan is used to execute the procedure with READ UNCOMMITTED, the problem does not occur.

WORKAROUND

Use the locking hint keyword NOLOCK in the SELECT statement instead of SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

- OR -

Recompile the stored procedure so it does not use the existing plan in cache that was not executed with READ UNCOMMITTED. This can be accomplished in several different ways, including EXEC WITH RECOMPILE, drop/recreate the stored procedure, or restarting SQL Server.

Just retrying the operation will not avoid the problem unless the existing plan is flushed from cache. This can be determined by using DBCC MEMUSAGE.

Q151590: FIX: Error 4409 Generated When Using Multiple Database Views

SYMPTOMS

Under certain conditions, you may receive 4409 errors.

MORE INFORMATION

The problem arises when the first view in the chain of views can be executed but subsequent views are unavailable for use. For example:

If you have viewA and viewC in the master database and viewB in the pubs database, where viewA selects * from viewB and viewB selects * from viewC and viewC selects * from sysdatabases. And, SQL Server has been stopped while a client continues to try to execute a select * from viewA. When SQL Server is restarted, the master database is always recovered first and then the subsequent databases. As soon as master is recovered, the client attempts to execute the select and receives the 4409 error because pubs has not yet been recovered.

The same behavior can occur when you take a database on and offline, or you try to drop and create viewC in the above scenario while someone is trying to access it.

WORKAROUND

Drop and add the views when you are sure no one is accessing them.

Q152414: FIX: Compatible Tape Drives May Not Read SQL Server Dumps

SYMPTOMS

A SQL Server tape dump may not be usable on a similar tape drive if the two tape drives do not support the same tape marking mechanisms.

MORE INFORMATION

By default, SQL Server checks for tape marks using the following algorithm:

1. Does the tape drive support Setmarks? If so, use Setmarks.

2. Else does the tape drive support Filemarks? If so, use Filemarks.

WORKAROUND

Starting with Service Pack 3, SQL Server now contains the trace flag 3206 that will reverse the SQL Server tape mark algorithm to the following:

1. Does the tape drive support Filemarks? If so, use Filemarks.

2. Else does the tape drive support Setmarks? If so, use Setmarks.

SYMPTOMS

The system stored procedure sp_processmail can fail to reply to a message sent to the server if the sender uses an ambiguous e-mail address.

WORKAROUND

Make sure that there are no ambiguous e-mail addresses.

MORE INFORMATION

Here is an example of this problem:

Suppose there are the following two email addresses, "SQL" and "SQLSERVER."

If e-mail user "SQL" sends a message to a SQL Server that has sp_processmail running, SQL Server will receive the message, but SQL Server fails in sending a reply because the intended recipient's address is ambiguous. The address is ambiguous because e-mail address resolution says that both "SQL" and "SQLSERVER" are possibilities for the address "SQL."

Here is the updated documentation of xp_readmail from the SQL Server 6.5 documentation:

   Syntax
   xp_readmail ([@msg_id = msg_id] [, @type = type [OUTPUT]] [, @peek =
   {'true' | 'false'}]
   [, @suppress_attach = {'true' | 'false'}]
   [, @originator = @sender OUTPUT]
   [, @subject = @subject_line OUTPUT]
   [, @message = @body_of_message OUTPUT]
   [, @recipients = @recipient_list OUTPUT]
   [, @cc_list = @cc_list OUTPUT]
   [, @bcc_list = @bcc_list OUTPUT]
   [, @date_received = @date OUTPUT]
   [, @unread = {'true' | 'false'} OUTPUT]
   [, @attachments = @temp_file_paths OUTPUT])
   [, @skip_bytes = @bytes_to_skip OUTPUT]
   [, @msg_length = @length_in_bytes OUTPUT]
   [, @originator_address = @originator_address OUTPUT])
   where
   @originator_address
   Specifies the resolved mail address of the originator of the mail
   message. The @originator_address variable must be varchar(255).

For additional syntax information for xp_readmail see the Microsoft SQL Server Transact-SQL Reference.

Q149711: FIX: Insert View with DEFAULT VALUES Causes AV

SYMPTOMS

An insert performed on a view with the option DEFAULT VALUES can result in a thread level Access Violation. The following provides an example where the Access Violation would occur:

   CREATE TABLE ta (col1 CHAR(1) NULL DEFAULT 'a')
   GO
   CREATE VIEW vi AS SELECT * FROM ta
   GO
   INSERT INTO vi DEFAULT VALUES
   GO

WORKAROUND

Perform the insert with the option DEFAULT VALUES on the base table rather than through the view.

Q149932: FIX: 3307 Lock Error on INSERT SELECT UNION Abort w/ Dup Key

SYMPTOMS

An INSERT into a table with a UNIQUE CLUSTERED INDEX can fail with fatal error 3307:

   Process X was expected to hold logical lock on page Y. Error while
   undoing log row in database 'pubs'. Rid pageid is 0xZ and row num is
   0xZZ. The SQL Server is terminating this process.

MORE INFORMATION

If the insert is from UNIONed SELECT statements and there are duplicate keys (but not rows) generated such that the INSERT should fail, the 3307 error can be raised if the level of data reaches a certain threshold, somewhere around 1000 rows depending on row size.

WORKAROUND

The INSERT would otherwise fail with a non-fatal 2601 "Attempt to insert duplicate keys" error. The INDEX can be changed to use the IGNORE_DUP_KEY option so that the 2601 error is not raised.

Q149938: FIX: AV on INSERT of UNIONed SELECTs to Table With DEFAULTs

SYMPTOMS

INSERTing to a table from a UNION of two or more SELECT statements can result in a handled Access Violation.

MORE INFORMATION

At a certain threshold of data, a work table needs to be created to resolve the UNION correctly. If the target table has DEFAULTs, these are not applied to intermediate work tables but other attributes of the target table, such as NOT NULL, are applied, and this would cause the query to fail with a different error if there was not the AV.

WORKAROUND

Implement the INSERT as distinct INSERT SELECTs for each table in the UNION. If there is a UNIQUE INDEX on the target table, use the IGNORE_DUP_KEY option on that INDEX to filter out duplicate rows that would previously have been filtered out by the UNION. In many cases, this method can be much faster anyway because it can eliminate one or more intermedate work tables.

Q150882: FIX: Possible Handled AV w/ Sub-query Using NOT IN and '>'

SYMPTOMS

It is possible to experience a handled access violation (AV) when using an IN condition with a sub-query.

WORKAROUND

There are two possible means to workaround this problem. The first is to drop the current index and create either a unique clustered or non- clustered index. Or you can use the convert() function to step-up the column referenced in the sub-query to the same size as the column referenced on the left of the IN condition. An example of this 'step-up' would be to change the query to the following:

select a from test1 where a not in (select convert(char(2),c) from test2 where b = '1') and a <= '1'

Q151408: FIX: Query w/ Group by All Can Take Too Long to Complete

SYMPTOMS

The time a query takes to produce output can be too long when using join(s) between two or more tables and a GROUP BY ALL. The scan count and physical IO count are extremely high. The tables involved in the join have lots of duplicate data in the column involved and does not have any indexes.

Testing has shown that the situation under which this problem has been encountered is narrow in scope, and the problem can be reproduced only with the provided replication data.

WORKAROUND

Reduce the duplicates and create appropriate index(s) to reduce the amount of time to produce the output.

Q151605: FIX: Grant or Revoke Statements Fail When Used in an SP

SYMPTOMS

A GRANT or REVOKE statement can report the following error when used within the context of a stored procedure. The error is encountered when you attempt to create an object and then grant permission within the same procedure.

   Msg 267, Level 16, State 1
   Object 'xxx' cannot be found.

WORKAROUND

Use the EXECUTE statement to complete the operation.

For example:

create procedure dbo.spTest @strNewName varchar(8) as

   select * into pubs.dbo.tblTest from master..sysusers
   grant select on pubs.dbo.tblTest to PUBLIC

can be changed to:

create procedure dbo.spTest @strNewName varchar(8) as

   select * into pubs.dbo.tblTest from master..sysusers
   execute('grant select on pubs.dbo.tblTest to PUBLIC')

Q152214: FIX: UPDATE Using Aggregate and Arithmetic Operator Causes AV

SYMPTOMS

In an UPDATE statement within a stored procedure, if a subquery is used to set the value of a column and includes one or more aggregate functions with arithmetic operations, and the arithmetic operation references a column, then a handled thread level access violation may occur.

WORKAROUND

One workaround is to run the UPDATE statement outside of a stored procedure. Otherwise, break up the query and assign the result of the subselect to a variable, and then use the variable in the UPDATE statement.

Q152264: FIX: Five-way Table Join w/OR May Cause Handled Exception

SYMPTOMS

A five-way (or more) table join with a search clause introduced by an OR clause may cause handled exception which terminates the client thread.

WORKAROUND

Rewrite the query such that the rows not in the join qualify for the query because of the OR clause.

Q152615: FIX: Syntax Error in SELECT List May Cause Handled AV

SYMPTOMS

The absence of a comma between columns in the SELECT list may cause a handled AV under some narrow circumstances.

WORKAROUND

Add the comma to the SELECT clause.

Q149243: FIX: Insert/Select/(NOLOCK) w/ Rollback May Cause Error 3307

SYMPTOMS

If 'Insert table select * from table (NOLOCK) is called within a transaction, and this transaction is rolled back afterwards, error 3307 might be seen on both the client side and the SQL Server errorlog:

   Process %d was expected to hold logical lock on page %d. Error while
   undoing log row in database '%s'.  Rid pageid is %d and row number is
   %d. The SQL Server is terminating this process'

MORE INFORMATION

Limited testing has shown that the situation under which this problem has been encountered is narrow in scope, and the following conditions must be met:

1. Select statement has to use (NOLOCK) option.

The page number contained in error 3307 is always the leaf level page of the nonclustered index.

WORKAROUND

1. Do not use (NOLOCK) in the select statement.

Q151583: FIX: Select Using 'or' From View Defined w/ '>=' Can Cause AV

SYMPTOMS

A select using 'or' in the where clause on a view that is defined using a

'>=' (greater than or equal to) comparison operator in the where clause can

WORKAROUND

Changing the information available prior to SQL Server's evaluation of the query cost can be used to workaround this problem. Removing 'or' from the where clause as defined in the view, changing the type of comparison operator used in the view where clause, changing the index on the base table to 'clustered not unique', changing the columns being referenced by select on the view, and/or removing the 'or' found in the select where clause can all affect the occurrence of this problem.

Q151681: FIX: SELECT with CASE Can Cause Parser Error 403

SYMPTOMS

A SELECT statement against multiple tables that includes a CASE expression can result in parser error 403:

   Invalid operator for datatype op: UNKNOWN TOKEN type: char

WORKAROUND

Avoid using the CASE expression by rewriting the query using the IF..ELSE conditional Transact-SQL statement.

Q152263: FIX: Query with Join in Subselect May Cause Handled Exception

SYMPTOMS

A query with a subselect that contains a join may cause a handled exception that terminates the client connection.

WORKAROUND

Rewrite the query, possibly using a normal join instead of the subselect.

Q152800: FIX: GROUP BY Clause without Index Executes More Slowly

SYMPTOMS

If a Transact-SQL query contains a GROUP BY clause and an index is not defined on the column involved in GROUP BY, the query exhibits better performance when executed on the SQL server with no service packs or service pack 1, as opposed to SQL Servers with service pack 2 installed. This performance anomaly is exhibited only on the Alpha platform.

WORKAROUND

Sort the columns in the GROUP BY clause. Alternately, define an index encompassing the columns involved in the GROUP BY clause and make sure the index is used when grouping the columns in the query.

ADDENDUM: LIST OF PROBLEMS CORRECTED IN SERVICE PACK 1

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

• FIX: Memory Leak in NTWDBLIB.DLL on Failed dbopen()
• FIX: ODBC 6.0 Driver Sometimes Cannot Get Results w/ dbmsspx3
• FIX: Updating a Text Column Results in a Table Scan
• FIX: Cannot Insert BLOB Data Larger Than 128k w/ SQLSetParam
• FIX: Repl Synch Fails with Table Names That Have Ext Chars
• FIX: EXECUTE Command w/ a Local Variable Can Cause Thread AV
• FIX: DUMP DATABASE w/ Variable Device Name in SP May Cause AV
• FIX: IN Condition with More Than 15 Values May Fail
• FIX: Unexpected Behavior During Object Resolution
• FIX: DBCC NEWALLOC Enhanced to use RA for Performance
• FIX: Query Plan Inconsistent If Search Value > Last Step
• FIX: Optimizer Hint for Index Name Fails in View
• FIX: Open Cursor Statement in SP Sets Variables to NULL
• FIX: Lvl 16 Error on Temp Tbl From SP w/ > 64 Pgs May Cause AV
• FIX: SELECT INTO w/ Correlated Subqueries May Cause AV
• FIX: DUMPs May Halt Queries With Triggers
• FIX: Create Worktable for Oversized Table Can Stop the Server
• FIX: ALTER TABLE Allows Nulls in PRIMARY KEY
• FIX: AV When Creating Reformat Plan of SP Containing Join
• FIX: Updating a Record w/ NULL Text Field Can Cause 2574 Error
• FIX: Add Constraint Causes Identity-Column Inserts to Fail
• FIX: Cannot Create a Qualified Table With a Check Constraint
• FIX: SET ARITHABORT ON Inadvertently Causes an Insert to Fail
• FIX: Cursor Declaration In a Stored Procedure Causes Client AV

ADDENDUM: LIST OF PROBLEMS CORRECTED IN SERVICE PACK 2

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

• FIX: SELECT During UPDATE STATISTICS Can Terminate w/ Msg 814
• FIX: DBCC NEWALLOC May Not Detect TEXT Extent Inconsistencies
• FIX: INSERT Into w/ IDENTITY Col Inside Trigger Causes AV
• FIX: SELECT INTO From Table w/ IDENTITY Col Causes Handled AV
• FIX: Update of Primary Key Causes Repl Custom St. Proc to Fail
• FIX: Multiple ORDER BY DESC May Cause 614 Errors
• FIX: DBCC SQLPERF(THREADS) Fails w/ AV on 6.0 SP 1
• FIX: SELECT w/ IN Clause Causes AV w/ Subquery SELECT NULL
• FIX: Procedure for Repl. Can Fail When Referencing NCI
• FIX: Repl w/ Ext Chars on Non-ANSI Char Sets May Fail
• FIX: RPC w/ Text/Image Parameters May Cause Handled AV
• FIX: Syntax Error Reporting Disabled w/ St Proc >68 Pages
• FIX: Drop Procedure Gives 3702 Error if Cursor Not Deallocated
• FIX: Users Blocking With No Locks Displayed in sp_lock
• FIX: Invalid Column in Subquery May Result in Handled AV
• FIX: Milliseconds Are Not Replicated w/ DATETIME Fields
• FIX: Cursor w/ Union May Cause Errors if Not First in Batch
• FIX: Handled AV If Many Users Dropping/Creating Tables
• FIX: BEGIN TRAN After OPEN CURSOR May Not Commit
• FIX: Explicit Insert of Identity Value in SP Can Cause AV
• FIX: Error 3307 Running Update Stats After 1105 Error
• FIX: LogReader Can Fail On sp_repldone After Unsubscribing
• FIX: LogReader Can Fail On sp_repldone Using Partitioning
• FIX: Timestamp Col of Temp Table Set to 0 When Inserting Row in SP
• FIX: AV or Error 632 on Update w/ Select Count(distinct) Subqu
• FIX: Cursor Using Stored Proc After LOAD DB May Get 225 Error
• FIX: Inserts on SMP Computer Can Cause Error Message 1203
• FIX: Stored Procedures Can Corrupt Themselves in Small Caches
• FIX: A Cursor in a St. Procedure Can Cause an Access Violation
• FIX: A View With an Outer Join Can Cause An Access Violation
• FIX: SELECT TEXT Columns with FOR BROWSE Holds sh_page Locks
• FIX: dbopen Memory Leak of 32K On Failed Login Attempt
• FIX: SELECT From View w/ Data Length > 1962 Bytes May Hang Svr