FIX: SQL Server 6.5 Service Pack 2 Fixlist (Part 3 of 3)

ID: Q164022

The information in this article applies to:

Microsoft SQL Server version 6.5 Service Pack 2 and later

SUMMARY

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

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

LIST OF PROBLEMS CORRECTED IN SERVICE PACK 2 (Part 3 of 3)

SERVER COMPONENTS

Q158462: FIX: Access Violation With Natural ANSI Join with View
Q158486: FIX: Error 701/Server Unresponsive on Cursor Queries w/UPDATE
Q158584: FIX: AV With Outer Join and Convert to Char Column
Q158685: FIX: ANSI Join in Exists Clause Causes Parser Error 403
Q158791: FIX: Using a Derived Table in the HAVING Clause May Cause AV
Q158792: FIX: Problems Using the KILL Command with a Stored Procedure
Q158793: FIX: Permission Denied Error Message 229 When Updating a Table
Q158806: FIX: A Derived Table in the Group By Clause Causes Handled AV
Q158807: FIX: Derived Table Generation May Cause Error 206
Q158808: FIX: Permission Denied Error 229 Updating Table With DBID>10
Q158856: FIX: Err 107 Creating VIEW with a UNION & Correlated Subquery
Q158892: FIX: DBCC Shrinkdb Fails with Error 7991
Q158893: FIX: Increasing the Size of Tempdb in RAM Fails
Q158998: FIX: Dump to Tape Causes AV Using Adaptec 154x SCSI Adapter
Q159339: FIX: Stack Overflow If a SELECT Statement Is Killed
Q159358: FIX: Access Violation During the Load of a Read-Only Database
Q159372: FIX: SET ANSI_WARNINGS ON Option Does Not Generate Warnings
Q159373: FIX: Dynamic Cursor Fails to Delete a Record
Q159444: FIX: A Join Returning a NULL in a Smallint Column May Cause AV
Q159445: FIX: Optimizer May Incorrectly Choose Reformat Strategy
Q159598: FIX: SQL Server Stops Responding After an INSERT/SELECT
Q159698: FIX: Unexpected Results Using OBJECT_ID() & Quoted Identifier
Q159699: FIX: Sp_cursorfetch: No Error Reported With Incorrect Datatype
Q159701: FIX: UNION in Views Maintains Duplicate Rows If Table Has Bit
Q159781: FIX: Sp_cursorfetch May Cause Errors 614 and 605
Q159782: FIX: Concurrent CREATE TABLE & ALTER TABLE May Cause Deadlock
Q159783: FIX: Stranded Tables After Deadlocks on System Tables
Q159816: FIX: Error 15224 Renaming Column to 1 Character with Sp_rename
Q159847: FIX: ANSI SQL JOIN May Fail With AV Joining VIEW With Table
Q159848: FIX: ALTER TABLE ADD PK May Take a Long Time Even With No Data
Q159849: FIX: Names May Collide with Creation of Temp Stored Procedures
Q159937: FIX: AV Creating View With ANSI JOINS
Q160541: FIX: Cross database insert fails with error 229 permission denied
Q161223: FIX: Msg. 116 Doing Correlated Updates in a Stored Procedure
Q162033: FIX: SQLTrace May Cause Error 17803 on a Server
Q162366: FIX: Using a WHERE IN Clause on a Selected UNION May Fail
Q167606: FIX: Err 511: Stored Proc w/ANSI_NULL_DFLT_ON Creates Temp Tbl
Q171879: FIX: Switching Order of Tables in FROM Clause Changes Optimizer

Below are excerpts from each of the articles listed above. For the full text of the articles, search for the article number in the Microsoft Knowledge Base.

FIX: Access Violation With Natural ANSI Join with View

Q158462 BUG #: 15823

SYMPTOMS

On a computer running Microsoft SQL Server 6.5 Service Pack 1, a natural join between a table and a view using the ANSI INNER JOIN syntax results in a handled access violation, if the view includes a Column1 = Column2 restriction clause.

FIX: Error 701/Server Unresponsive on Cursor Queries w/UPDATE

Q158486 BUG #: Windows NT: 15624 (6.5)

When you run cursor queries that do an UPDATE using the WHERE CURRENT OF <cursor> clause, the server runs out of memory, even though the cursors are closed properly. This leads to the following error message (701):

There is insufficient system memory to run this query.

After that, the server becomes very slow for normal connections.

A query performing an outer join (using either old or new ANSI-compatible join syntax) and including a CHAR column in the select list (which comes from doing a CONVERT) may generate a handled access violation (AV).

FIX: ANSI Join in Exists Clause Causes Parser Error 403

Q158685 BUG #: 15735 (6.5)

Using an ANSI JOIN together with a correlated subquery inside an EXISTS clause returns the parser error 403:

Invalid operator for datatype op: UNKNOWN TOKEN type: varchar

A SELECT statement using a derived table in the HAVING clause may cause a handled access violation (AV). In addition, the access violation's stack dump may go into a state where it continuously writes the stack to the errorlog. If this occurs, the errorlog will grow very quickly.

Using the KILL command to end a long-running stored procedure may cause the computer running SQL Server to stop responding or go into a 100 percent CPU spin. All existing connections cannot process, and any attempts to connect will time-out. SQL Server cannot be shut down, and you must either kill it or restart Windows NT. This problem should only occur if the stored procedure is processing through a large cursor set.

A non-system administrator (SA) user will receive a Permission Denied error message (number 229) when he or she tries to update a table through a stored procedure, even if the user has permission to run the stored procedure. If the SA or database owner (DBO) runs the stored procedure first, the user will then be able to use the stored procedure. This problem occurs when the following sequence of events occurs:

Non-SA user runs stored procedure A.

Stored procedure A calls procedure B.

Procedure B updates a table through a cursor.

A SELECT statement using a derived table in the Group By clause may cause a handled access violation (AV). In addition, the access violation's stack dump may go into a state where it continuously writes the stack to the errorlog. If this occurs, the errorlog will grow very quickly.

If a query used to create a derived table selects a user-defined datatype, selecting from the derived table may cause the following error message:

206 16 Operand type clash: %s is incompatible with %s

The following example shows a query that may cause this problem. In the example, au_id from the authors table is a user-defined datatype.


   use pubs
   go
   select * from (select a.au_id from authors a
   inner join titleauthor ta on a.au_id = ta.au_id
   and a.au_fname = "Albert") as a
   go

A non-system administrator (SA) user will receive a Permission Denied error message (number 229) when he or she tries to update a table through a stored procedure, even if the user has permission to run the stored procedure. If the SA or database owner (DBO) runs the stored procedure first, the user will then be able to use the stored procedure. This problem occurs when the following sequence of events occurs:

Non-SA user runs stored procedure A.

Stored procedure A calls procedure B.

Procedure B updates a table through a cursor in which the table is located in another database.

The other database must have a DBID greater then 10.

When you attempt to create a VIEW that contains a UNION clause and a correlated subquery, the creation of the VIEW fails with error 107.

If the default database size in the SQL Server Configuration Values is set to a value that is greater than the actual size of a database, you receive the following error when trying to shrink the database:

Error 7991:
Unable to shrink database '%.*s' as it contains %d pages. Minimum pages %d.

The database consistency checker (DBCC) command shrinkdb(<dbname>) works correctly and shows the size to which the database can be shrunk, but you will also receive error 7991, if the condition above is true. It makes no difference whether you use the user interface or the DBCC shrinkdb Transact- SQL command.

If tempdb is located in RAM and you have no default disk specified, it is not possible to alter the size of tempdb in RAM. No error message is provided. Only the config_value for the SQL Server configuration value "tempdb in RAM" is changed, but the run_value always remains the same (previous) value after restarting SQL Server.

When you use the 154x series Adaptec SCSI adapter on Windows NT 4.0, dumping any database to tape may cause a handled access violation (AV). The access violation seems to occur more consistently on 4 mm DAT tape drives, but has also been seen on 8 mm DAT tape drives.

If a process is killed while running a SELECT statement in which an aggregate function is used with a DISTINCT keyword, the following error message is displayed in the errorlog:

spid10 ex_testhandle: stack overflow, top=0x10a0ac8, end=0x10a0ac8

In addition, if the table from which the SELECT is run is a temporary table, the following messages are displayed when the process is killed:

kernel udread: Operating system error 6(The handle is invalid.) on device 'D:\MSSQL\DATA\MASTER.DAT' (virtpage 0x00000394).

kernel udwritem: Operating system error 6(The handle is invalid.) on device 'D:\MSSQL\DATA\MASTER.DAT' (virtpage 0x00000b80).

If the SELECT is placed in a temporary stored procedure and it is selecting from a temp table, then the following error is added to the errors above:

spid10 bufwait: timeout, BUF_IO, bp 0xca1420, pg 0x110, stat
0x1004/0x6, obj 0, bpss 0x109f598

This error message will continue to be entered in the errorlog until either Windows NT or SQL Server is shut down. You will not be able to shut down SQL Server using SQL Service Manager or by performing a "net stop mssqlserver". At this point, the behavior of the server is somewhat unpredictable. You may or may not be able to make new connections to the server, but the existing connections appear to be stable.

However, if tempdb only has its original size of 2 MB on the master device, when the process is killed, you will receive the following error instead:

Error: 1117, Severity: 21, State: 10
Extent chain for object -321 is not correctly linked.

An access violation (AV) may infrequently occur when issuing a LOAD DATABASE or LOAD TRANSACTION command on a database that is read-only.

The SET ANSI_WARNINGS ON option does not cause an error if an INSERT or UPDATE statement violates the maximum specified field length of the column it is inserted into. For references on the maximum lengths of each datatype, refer to your documentation.

FIX: Dynamic Cursor Fails to Delete a Record

Q159373 BUG #: 16127 (SQL 6.5)

If a dynamic cursor is opened with optccval for the optimistic concurrency control, and ROWSET_SIZE is equal to 1, it may generate the following error message when you attempt to delete a record:

Msg 16934, Level 16, State 2, Optimistic concurrency check failed, the row was modified outside of this cursor

An Open Database Connectivity (ODBC) application may receive this error message using SQLSetPos to delete a record with the dynamic server side cursor.

A join that returns a NULL in a smallint column may result in a thread level access violation (AV).

As stated in the SQL Server documentation, when joining tables, SQL Server may, use a reformatting strategy to join the tables and return the qualifying rows. This strategy is considered only as a last resort, when the tables are large and neither table in the join has a useful index.

However, this strategy, when chosen, may result in the query running slower than it would if either the join order of the tables in the query has been forced using the FORCEPLAN statement, or the underlying indexes on the joined tables were used.

Running an INSERT-SELECT statement without a FROM clause (such as 'INSERT INTO TABLE SELECT * WHERE 1=2') may cause SQL Server to stop responding. No more connections can be made, existing connections time out, and no error messages are presented in SQL Server the errorlog. SQL Server cannot be shut down from either Service Manager or Control Panel.

When you have SET QUOTED_IDENTIFIER ON, a table created with a period (.) is not correctly identified using the OBJECT_ID() function.

For example, the query:


   SELECT name, id, uid FROM SysObjects WHERE id = object_id('dbo.my
   table')

returns information pertaining to the table called "my table" owned by user "dbo" (if one exists).

If a non-integer variable is used as the fetch type parameter of sp_cursorfetch, a fetch type of NEXT is assumed. An error message is not returned.

Running a SELECT from a view that is defined as a UNION of select statements does not eliminate duplicate rows, if the underlying table contains a field of the bit type. However, duplicate rows are eliminated if the UNION statement is not within a view.

If DYNAMIC CURSOR was opened through sp_cursoropen, and sp_cursor was used to delete all rows from the table, subsequent calls to sp_cursorfetch with any fetch type will fail, generating error 614. After the error occurs, 'drop table' fails, and you will receive Msg 3702 until the server is restarted, even if the cursor has been closed. This problem may also cause transient error 605 on the table if CURSOR is used repeatedly under this condition. DBCC CHECKTABLE shows no corruption in the table. Regular Transact SQL statements, such as SELECT, UPDATE, or DELETE still work fine on the same table. The text of the error messages follows:

Error : 614, Severity: 21, State: 3
A row on page %ld was accessed that has an illegal length of 0 in database '%s'.

Getpage: bstat=0x1008/0, sstat=0x82000091, disk
pageno is/should be: objid is/should be:
0x342(834) 0x8(8)
0x342(834) 0x7053b3f3(1884533747)
... extent objid 0x7053b3f3, mask 0x3/0, next/prev=0x340/0x340
... retry bufget after purging bp 0xf26d40

Error : 605, Severity: 21, State: 1
Attempt to fetch logical page %ld in database '%ld' belongs to object 'syslogs', not to object '%s'.

Msg 3702, Level 16, State 1
Cannot drop the table '%s' because it is currently in use.

If you perform a CREATE TABLE and an ALTER TABLE ADD CONSTRAINT concurrently, you may receive deadlocks on system tables.

Concurrent CREATE TABLE and ALTER TABLE statements can cause deadlocks on system tables. If the process running CREATE TABLE is chosen as the deadlock victim, it is possible to see stranded tables with sysstat=195 in sysobjects. If you try to drop the table, it will fail with the following message:

Msg 3701, Level 11, State 1, Server bp71092, Line 1
Cannot drop the table '%s', because it doesn't exist in the system catalogs.

A 'create table' also fails, with the message:

Msg 2714, Level 16, State 1, Server bp71092, Line 1 There is already an object named '%' in the database.

FIX: Error 15224 Renaming Column to 1 Character with Sp_rename

Q159816 BUG #: 15750 (6.50: 0213)

You receive the following parameter error when you attempt to use sp_rename to rename a column to a single character:

Msg 15224, Level 11, State 15
Error, the value for parameter NewName contains invalid characters or

violates a basic restriction ().

FIX: ANSI SQL JOIN May Fail With AV Joining VIEW With Table

Q159847 BUG #: 16066 (WINDOWS: 6.5)

A SELECT statement may fail with an access violation (AV) if you use the new ANSI style SQL JOIN when joining a VIEW and a table. This problem will occur if either of the following are true:

The VIEW contains expressions in its SELECT statement

The VIEW contains the old style of JOIN operators.

If there are thousands of tables with hundreds of columns each, the ALTER TABLE ADD PK command may take a long time, even if there is no data in the table.

If an Open Database Connectivity (ODBC) driver creates stored procedures within a very short time of one another, the names of the stored procedures may collide.

When you create views containing ANSI standard joins, the DB-Library process may fail, and break the connection. A handled access violation (AV) is generated in the error log. This problem is most commonly seen with nested ANSI joins, where the inner join is a CROSS JOIN.

A stored procedure run by a user who has full execute permissions and who is a valid user in two databases may fail with the following error on a cross-database INSERT:

229 %s permission denied on object %.*s, database %.*s, owner %.*s

This problem occurs even if the owner of the underlying table is also the owner of the stored procedure. This problem occurs if the stored procedure needs to be re-resolved, such as in the following cases: loading from backup, dropping and recreating an underlying table, or shutting down and restarting SQL Server (if the stored procedure references a temporary table). If the stored procedure is dropped and re-created, it works properly until one of the re-resolutions mentioned above occurs.

Running a stored procedure that does a correlated UPDATE with trace flag 204 enabled may cause the following error:

Msg 116
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

The error only seems to occur consistently when running the procedure with trace flag 204 after the server is recycled (subsequent to creating the procedure).

The correlated UPDATE could be similar to the following:


   INSERT table1
   SELECT distinct x
   FROM table2
      UPDATE table1
      SET y = (SELECT SUM(z)
         FROM table2
         WHERE
   table1.x = table2.x
         )

SQL Server may generate the following error if you use SQLTrace and the application passes float parameters through Remote Procedure Calls (RPCs).

Msg 17803: Insufficient memory available.

This error may also be accompanied by other problems, including a previously active filter returning to a stopped state, or the inability to start new filters. Once in this state, it may be impossible to terminate the SQLTrace application. SELECT statements consisting of a UNION and a WHERE IN clause may fail.

For example, the following statement


SELECT type, name
FROM (SELECT * FROM master..sysobjects
UNION
SELECT * FROM pubs..sysobjects) as O
WHERE type IN ('S', 'U')

Produces (in part) the following results:


type name
---- ------------------------------
U    authors
P    byroyalty
C    CK__authors__au_id__02DC7882
C    CK__authors__zip__04C4C0F4
C    CK__jobs__max_lvl__2719D8F8
C    CK__jobs__min_lvl__2625B4BF
C    CK__publisher__pub_i__089551D8
C    CK_emp_id

A stored procedure that creates a temporary table without explicitly specifying column nullability and INSERTS into that table will fail with an error 511 if the procedure was created with ANSI_NULL_DFLT_ON set on, and is run with ANSI_NULL_DFLT_ON set off. This problem occurs if you do all of the following:

Run the procedure.

Perform sp_recompile on an underlying permanent table referenced in the procedure.

Run the procedure again.

The error 511 occurs on the second execution after the recompile. All ODBC connections set ANSI_DEFAULTS on, which in turn sets ANSI_NULL_DFLT_ON on. Therefore, any procedure created over ODBC is susceptible to this problem. Switching the order of tables in the FROM clause for some join queries may result in a non-optimal execution of the query plan. In one scenario for a two-table join, significant difference in the execution times was noted between two identical queries that had the same WHERE clause, but had the order of tables in the FROM clause switched.

WORKAROUND

To work around this problem, do either of the following:

Avoid using the UPDATE WHERE CURRENT OF <cursor> clause. Instead, you can use "positioned" updates (calling sp_cursor or using SQLSetPos in ODBC).

Identify and close the connection that caused the error 701 message. Normally, the client connection that caused this problem runs into the error 701, and can be identified easily. When this connection is closed, everything returns to normal.

FIX: AV With Outer Join and Convert to Char Column

Q158584 BUG #: 15789 (6.50, NT)

Use a join expression in a WHERE clause instead.

FIX: Using a Derived Table in the HAVING Clause May Cause AV

Q158791 BUG #: 16030 6.50

To work around this problem, do not use derived tables in the HAVING clause.

FIX: Problems Using the KILL Command with a Stored Procedure

Q158792 BUG #: 16064 (6.50)

Use the KILL command with discretion.

FIX: Permission Denied Error Message 229 When Updating a Table

Q158793 BUG #: 16107 (6.50)

To work around this problem, do one of the following:

Do not use cursors to update tables when the cursor is in a stored procedure that is called by another procedure.

Give the user permissions to the underlying table.

Have the DBO run all stored procedures affected by this problem every time SQL Server is restarted. This allows normal users to run the stored procedures.

FIX: A Derived Table in the Group By Clause Causes Handled AV

Q158806 BUG #: 15855 (6.50)

To work around this problem, do not use derived tables in the Group By clause.

FIX: Derived Table Generation May Cause Error 206

Q158807 BUG #: 15800 (6.50)

To work around this problem, do not use derived tables to select data from columns with user-defined data types.

FIX: Permission Denied Error 229 Updating Table With DBID>10

Q158808 BUG #: 16128 (6.50)

To work around this problem, do one of the following:

Do not use cursors to update tables when the cursor is in a stored procedure that is called by another procedure.

Give the user permissions to the underlying table.

Have the DBO run all stored procedures affected by this problem every time SQL Server is restarted. This allows normal users to run the stored procedures.

FIX: Error Creating a VIEW with a UNION & Correlated Subquery

Q158856 BUG #: 16081 (sqlbug_65 sql 6.5)

To work around this problem, change your VIEW definition, and avoid using both a UNION and a correlated subquery.

FIX: DBCC Shrinkdb Fails with Error 7991

Q158892 BUG #: 16024 (6.50)

To work around this problem, set the Database Size configuration value to a value that is less than the size of the database you want to shrink.

FIX: Increasing the Size of Tempdb in RAM Fails

Q158893 BUG #: 16020 (6.50)

To work around this problem, specify a default device with sp_diskdefault. You can then alter the size of tempdb without any problem.

FIX: Dump to Tape Causes AV Using Adaptec 154x SCSI Adapter

Q158998 BUG #: 16157 (6.50)

To work around this problem, do not dump to a tape device. Instead, dump and load to a different type of dump device, such as disk. If you dump to a disk but you wish to store the data on tape, you can use another backup package, such as Windows NT Backup, to archive the dump file to tape.

FIX: Stack Overflow If a SELECT Statement Is Killed

Q159339 BUG #: 15658 (sqlbug_65)

Avoid using the Kill command to terminate processes.

FIX: Access Violation During the Load of a Read-Only Database

Q159358 BUG #: 15568 (6.5)

Re-create the database and reload it using the same database dump file. In the case of an access violation during a LOAD TRANSACTION, reload the last database dump and the succeeding transaction log dumps.

FIX: SET ANSI_WARNINGS ON Option Does Not Generate Warnings

Q159372 BUG #: 15720 (Windows 6.50)

To work around this problem, use other types of cursors (for example, use the SCROLL cursor type). Use the DECLARE <cursor name> SCROLL CURSOR FOR <statement> syntax for cursors. This will change the cursor type from DYNAMIC to SCROLLABLE (keyset-driven).

An ODBC application can use either ODBC cursors or keyset-driven server side cursors.

FIX: A Join Returning a NULL in a Smallint Column May Cause AV

Q159444 BUG #: 15183 (Windows: 6.50)

To work around this problem, use CONVERT to change the datatype from a smallint value to an integer.

FIX: Optimizer May Incorrectly Choose Reformat Strategy

Q159445 BUG #: 15601 (NT 6.5)

To work around this problem, try using the SET FORCEPLAN ON statement before running the query.

FIX: SQL Server Stops Responding After an INSERT/SELECT

Q159598 BUG #: 15780 (6.50)

To work around this problem, add a FROM clause to the query.

FIX: Unexpected Results Using OBJECT_ID() & Quoted Identifier

Q159698 BUG #: WINDOWS: 15669 (6.5)

Refer to the table using the name field of SysObjects and, if necessary, the user id of the creator.

To continue the example above, you would use:


   SELECT name, id, uid FROM SysObjects WHERE name = 'dbo.my table' and
   uid = user_id('dbo')

FIX: Sp_cursorfetch: No Error Reported With Incorrect Datatype

Q159699 BUG #: 15605 (WINDOWS: 6.5)

Before running sp_cursorfetch, convert the fetch type to an integer datatype, using the CONVERT() function. An error will be reported if the conversion fails.

FIX: UNION in Views Maintains Duplicate Rows If Table Has Bit

Q159701 BUG #: 16130 (WINDOWS: 6.5)

If the bit datatype must be used, place the UNION statement outside a view, or use the DISTINCT keyword when selecting from the view. Otherwise, use tinyint, smallint, or int datatypes rather than the bit type.

FIX: Sp_cursorfetch May Cause Errors 614 and 605

Q159781 BUG #: 14588 (WINDOWS: 6.50)

To work around this problem, use the Keyset cursor instead.

FIX: Concurrent CREATE TABLE & ALTER TABLE May Cause Deadlock

Q159782 BUG #: 16016 (WINDOWS: 6.50)

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

FIX: Stranded Tables After Deadlocks on System Tables

Q159783 BUG #: 16012 (WINDOWS: 6.50)

To work around this problem, avoid using the old style of join operators in VIEWs if you are joining these VIEWs with new style ANSI SQL JOINs.

FIX: ALTER TABLE ADD PK May Take a Long Time Even With No Data

Q159848 BUG #: 15900 (WINDOWS: 6.5)

To work around this problem, do not use ANSI-Standard joins.

FIX: Cross-Database INSERTS May Fail with Error 229

Q160541 BUG #: 16276

To work around this problem, do either of the following:

Take the temporary table creation out of the stored procedure; create the temporary table before calling the stored procedure.

Drop and re-create the affected stored procedure after a re-resolution event has occurred.

FIX: Msg. 116 Doing Correlated Updates in a Stored Procedure

Q161223 BUG #: 15911

To work around this problem, either disable the 204 trace flag, or drop and re-create the stored procedure after each server restart.

FIX: SQLTrace May Cause Error 17803 on a Server

Q162033 BUG #: 16226 (NT: 6.50)

To work around this problem, deselect the option of tracing RPC events in the SQLTrace filter.

FIX: Using a WHERE IN Clause on a Selected UNION May Fail

Q162366 BUG #: 16400 (SQL6.5)

Embed SELECT statements into the WHERE IN clause, as shown by the following:


SELECT type, name
FROM (SELECT * FROM master..sysobjects
UNION
SELECT * FROM pubs..sysobjects) as O
WHERE type IN (SELECT 'S' UNION SELECT 'U')

FIX: Err 511: Stored Proc w/ANSI_NULL_DFLT_ON Creates Temp Tbl

Q167606 BUG #: 16814 (NT: 6.5)

To work around this problem, do either of the following:

Explicitly specify columns as NULL, or NOT NULL during the table definition in the stored procedure.

Specify the ANSI_NULL_DFLT_ON setting for clients before running the stored procedure.

FIX: Switching Order of Tables in FROM Clause Changes Optimizer

Q171879 BUG #: 15522 (Windows: 6.5)

For the Optimizer to use the optimal join order, compare the execution times and plans for the query and place the tables that result in better execution times in the FROM clause.

MORE INFORMATION

ALTER TABLE ADD PK searches syscolumns several times and scans the tables. With thousands of wide tables, it may take a full minute to add a PK to an empty table.

FIX: Names May Collide with Creation of Temp Stored Procedures

Q159849 BUG #: 15924 (WINDOWS: 6.5)

ODBC uses timestamp values in the naming convention of temporary stored procedures. It is possible for a very quick thread (or multiple threads) to use the same timestamp, and create additional stored procedures with the same name. The server would then not be able to resolve which of the stored procedures to run.

FIX: AV Creating View With ANSI JOINS

Q159937 BUG #: 15781 (Windows: 6.50 SP1)

Additional query words: SP2 database patch sp

Keywords : SSrvGen
Version : winnt:6.5 Service Pack 2 and later
Platform : winnt
Issue type :