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

• Microsoft SQL Server, version 6.5 Service Pack 2

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

ARTICLE-ID: 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

ARTICLE-ID: Q158486 BUG #: Windows NT: 15624 (6.5)

SYMPTOMS

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.

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

  -or-

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

ARTICLE-ID: Q158584 BUG #: 15789 (6.50, NT)

SYMPTOMS

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

ARTICLE-ID: Q158685 BUG #: 15735 (6.5)

SYMPTOMS

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

WORKAROUND

Use a join expression in a WHERE clause instead.

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

ARTICLE-ID: Q158791 BUG #: 16030 6.50

SYMPTOMS

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.

WORKAROUND

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

FIX: Problems Using the KILL Command with a Stored Procedure

ARTICLE-ID: Q158792 BUG #: 16064 (6.50)

SYMPTOMS

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.

WORKAROUND

Use the KILL command with discretion.

FIX: Permission Denied Error Message 229 When Updating a Table

ARTICLE-ID: Q158793 BUG #: 16107 (6.50)

SYMPTOMS

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:

1. Non-SA user runs stored procedure A.

2. Stored procedure A calls procedure B.

3. Procedure B updates a table through a cursor.

WORKAROUND

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.

ARTICLE-ID: Q158806 BUG #: 15855 (6.50)

SYMPTOMS

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.

WORKAROUND

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

FIX: Derived Table Generation May Cause Error 206

ARTICLE-ID: Q158807 BUG #: 15800 (6.50)

SYMPTOMS

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

   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

WORKAROUND

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

ARTICLE-ID: Q158808 BUG #: 16128 (6.50)

SYMPTOMS

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:

1. Non-SA user runs stored procedure A.

2. Stored procedure A calls procedure B.

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

4. The other database must have a DBID greater then 10.

WORKAROUND

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.

ARTICLE-ID: Q158856 BUG #: 16081 (sqlbug_65 sql 6.5)

SYMPTOMS

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.

WORKAROUND

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

ARTICLE-ID: Q158892 BUG #: 16024 (6.50)

SYMPTOMS

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.

WORKAROUND

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

ARTICLE-ID: Q158893 BUG #: 16020 (6.50)

SYMPTOMS

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.

WORKAROUND

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

ARTICLE-ID: Q158998 BUG #: 16157 (6.50)

SYMPTOMS

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.

WORKAROUND

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

ARTICLE-ID: Q159339 BUG #: 15658 (sqlbug_65)

SYMPTOMS

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

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

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

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.

WORKAROUND

Avoid using the Kill command to terminate processes.

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

ARTICLE-ID: Q159358 BUG #: 15568 (6.5)

SYMPTOMS

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

WORKAROUND

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

ARTICLE-ID: Q159372 BUG #: 15720 (Windows 6.50)

SYMPTOMS

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

ARTICLE-ID: Q159373 BUG #: 16127 (SQL 6.5)

SYMPTOMS

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

WORKAROUND

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

ARTICLE-ID: Q159444 BUG #: 15183 (Windows: 6.50)

SYMPTOMS

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

WORKAROUND

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

ARTICLE-ID: Q159445 BUG #: 15601 (NT 6.5)

SYMPTOMS

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.

WORKAROUND

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

ARTICLE-ID:Q159598 BUG #: 15780 (6.50)

SYMPTOMS

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.

WORKAROUND

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

FIX: Unexpected Results Using OBJECT_ID() & Quoted Identifier

ARTICLE-ID: Q159698 BUG #: WINDOWS: 15669 (6.5)

SYMPTOMS

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

WORKAROUND

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

ARTICLE-ID: Q159699 BUG #: 15605 (WINDOWS: 6.5)

SYMPTOMS

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.

WORKAROUND

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

ARTICLE-ID: Q159701 BUG #: 16130 (WINDOWS: 6.5)

SYMPTOMS

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.

WORKAROUND

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

ARTICLE-ID: Q159781 BUG #: 14588 (WINDOWS: 6.50)

SYMPTOMS

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

   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.

WORKAROUND

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

FIX: Concurrent CREATE TABLE & ALTER TABLE May Cause Deadlock

ARTICLE-ID: Q159782 BUG #: 16016 (WINDOWS: 6.50)

SYMPTOMS

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

WORKAROUND

To work around this problem, place the ALTER TABLE in a user transaction preceded by a SELECT (UPDLOCK) on syscolumns and sysindexes, as shown below:

   begin tran
   select count(*) from syscolumns(UPDLOCK) where id=object_id('TT1a')
   select count(*) from sysindexes(UPDLOCK) where id=object_id('TT1a')
   alter table TT1a add constraint TT1idx1a PRIMARY KEY (a, b, c, d, e, f,
      g, h)
   commit tran

ARTICLE-ID: Q159783 BUG #: 16012 (WINDOWS: 6.50)

SYMPTOMS

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.

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

ARTICLE-ID: Q159816 BUG #: 15750 (6.50: 0213)

SYMPTOMS

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

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

ARTICLE-ID:Q159847 BUG #: 16066 (WINDOWS: 6.5)

SYMPTOMS

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

  -or-

• The VIEW contains the old style of JOIN operators.

WORKAROUND

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

ARTICLE-ID: Q159848 BUG #: 15900 (WINDOWS: 6.5)

SYMPTOMS

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.

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

ARTICLE-ID: Q159849 BUG #: 15924 (WINDOWS: 6.5)

SYMPTOMS

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.

MORE INFORMATION

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

ARTICLE-ID: Q159937 BUG #: 15781 (Windows: 6.50 SP1)

SYMPTOMS

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.

WORKAROUND

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

FIX: Cross-Database INSERTS May Fail with Error 229

ARTICLE-ID: Q160541 BUG #: 16276

SYMPTOMS

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

WORKAROUND

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.

  -or-

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

ARTICLE-ID: Q161223 BUG #: 15911

SYMPTOMS

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

WORKAROUND

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

ARTICLE-ID: Q162033 BUG #: 16226 (NT: 6.50)

SYMPTOMS

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.

WORKAROUND

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

ARTICLE-ID: Q162366 BUG #: 16400 (SQL6.5)

SYMPTOMS

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

  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

WORKAROUND

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

ARTICLE-ID: Q167606 BUG #: 16814 (NT: 6.5)

SYMPTOMS

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:

1. Run the procedure.

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

3. Run the procedure again.

WORKAROUND

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.

  -or-

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

ARTICLE-ID: Q171879 BUG #: 15522 (Windows: 6.5)

SYMPTOMS

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

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.