FIX: SQL Server 6.0 Service Pack 3 Fixlist

ID: Q152616

The information in this article applies to:

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

BUG#: 15039

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.

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)

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.

The Task History for the distribution task will report "Failure" with no message explanation and the task will no longer be Active.

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

A SELECT INTO will fail with the following error message:

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

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

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

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

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.

NOTE: This problem only occurs under rare conditions, appears to be extremely data and hardware sensitive, and is not likely to occur with most CREATE INDEX statements.

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

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

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.)

When running xp_loginconfig, the query will return the message:

Unable to query SQL Server security information.

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

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

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

BUG# NT: 13132

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.

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

If you are running Service Pack 2, the above message will be followed by:

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

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

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

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

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

BUG# NT: 13721

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

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

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

Q134659 FIX: Unexpected Behavior During Object Resolution

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

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

BUG#: 13262

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

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

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

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

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>

Under certain conditions, you may receive 4409 errors.

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

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

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


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

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.

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

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

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

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

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

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

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

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

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

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

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'

A select using 'or' in the where clause on a view that is defined using a '>=' (greater than or equal to) comparison operator in the where clause can result in an a Thread Level Handled AV (Access Violation). The AV occurs during the process of determining the cost of a particular access plan that might be used to fulfill the requested select statement.

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

Invalid operator for datatype op: UNKNOWN TOKEN type: char

To reproduce this problem, the tables should have at least two columns of datatype CHAR, and three tables must be involved in the SELECT statement. Also, a join must be specified between two of these tables.

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

WORKAROUND

Use a separate DBPROCESS for each thread.

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

BUG# NT: 15078

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

BUG#: 13530

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

BUG# NT: 10972

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

BUG# NT: 11897

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

BUG# NT: 12183

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

along with a corresponding:

sp_configure 'allow updates', 0

at the end of the process.

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

BUG# NT: 12222

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

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

BUG# NT: 11550

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

BUG# NT: 13179

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

BUG# NT: 13125

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

BUG#: 12627

Use cursors against the direct tables instead of views.

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

BUG#: 13087

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

BUG# NT: 13490

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

Q150955: FIX: Client Terminated on Repeated Unsuccessful Obj Resolution

BUG#: 13393

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

BUG#: 13626

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

BUG#: 14267

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

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

BUG#: 13762

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

BUG#: 14238

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

BUG#: 14645

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

BUG#: 13749 | 13753

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:

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

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

Q152415: FIX: sp_processmail Gets Ambiguous Recipient Error

BUG#: 13309

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

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

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

BUG#: 14745

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

BUG#: 14833

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 '>'

BUG#: 12628

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

BUG# 12809

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

BUG#: 13040

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
go

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')
go

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

BUG#: 15062

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

BUG#: 15089

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

BUG#: 15027

Add the comma to the SELECT clause.

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

BUG#: 13733

Do not use (NOLOCK) in the select statement.

Use clustered instead of nonclustered index.

Do not put this insert statement in transaction.

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

BUG#: 13873

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

BUG#: 13817

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

BUG#: 13120

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

MORE INFORMATION

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

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.

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.

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

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

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

If you dump to a tape drive that does not support Setmarks and then attempt to load the dump on a drive that does support Setmarks, SQL Server will not be able to load the dump. The same is true for the reverse scenario.

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).

If the sp_processmail stored procedure is modified to perform the xp_sendmail back to the value of the @originator_address instead of the @originator, sp_processmail will not fail because of an ambiguous recipient.

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

Q149711: FIX: Insert View with DEFAULT VALUES Causes AV

BUG#: 12923

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.

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

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

Select statement has to use (NOLOCK) option.

Table has to have nonclustered index.

Transaction has to be rolled back.

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

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

BUG#: 14825

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

Additional query words: sp3 database patch service pack


Keywords          : SSrvGen 
Version           : 
Platform          : WINDOWS 
Issue type        :

Last Reviewed: July 15, 1999