FIX: SQL Server 6.0 Service Pack 1 Fixlist

ID: Q136575

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

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 1

DB-LIBRARY COMPONENTS NETWORK-LIBRARY COMPONENTS ODBC COMPONENTS REPLICATION COMPONENTS SERVER COMPONENTS

FIX: Memory Leak in NTWDBLIB.DLL on Failed dbopen()

BUG# NT: 10955 (6.00)

SYMPTOMS

A Windows 32-bit (Win32) application that uses the Microsoft SQL Server version 6.0 NTWDBLIB.DLL file will have a memory leak of a few hundred bytes for each failed dbopen() call.

If the Microsoft ODBC 2.50.0121 Win16 SQL Server driver is running against a SQL Server version 6.0 database whose sp_configure network packet size is greater than 512 bytes, the following error occurs:

szSqlState="01000", pfNativeError = 253,
szErrorMsg="[Microsoft][ODBC SQL Server driver][DBMSSPX3]
ConnectionRead(SPXListenForSequencedpacket())."

The default for the sp_configure packet size on installation is 4096, so new SQL Server version 6.0 sites may see this problem the first time they attempt to access the server from a data source configured to use the Win16 driver and DBMSSPX3.

The problem does not occur with the Win32 ODBC driver. It does not occur if the application is accessing a version 4.21a server since the 4.21a servers always run with a 512 packet size which is not configurable. The problem does not occur with dblib clients, such as the SQL Server utilities.

The key to determining if this is the specific error encountered is to confirm that the pfNative error returned by the driver is 253. A Novell 253 error on a SPXListenForSequencedpacket call indicates that an overflow has occurred on a network packet buffer. Other problems, such as network configuration problems, may generate the same SqlState and ErrorMsg values, but they will generally have different pfNative values.

When you use the SQL Server ODBC driver version 2.50.0121 to update a text column, the update may take a very long time.

When you connect to SQL Server version 4.21a using the 2.50.0121 SQL Server ODBC driver and use the SQLSetParam function to insert image or text data where the total size (such as the cbcoldef argument for SQLSetParam) is larger than 128K, you will receive the following error message from the SQL Server driver:
[Microsoft][ODBC SQL Server Driver][SQL Server] Memory request failed
because more than 64 pages are required to run the query in its
present form. The query should be broken up into shorter queries if
possible[#703].

Replication fails when a table name contains extended characters and the subscriber is an ANSI server.

Invalid syntax when you build an EXECUTE statement from a local variable can cause a thread access violation (AV).

The access violation can be generated when Microsoft SQL Server version 6.0 attempts to resolve a stored procedure name that was built in a declared variable. SQL Server generates a thread access violation and aborts the connection.

DUMP DATABASE to a device that uses the format 

   dump_device = @dump_device_namevar

causes a handled access violation if used within a stored procedure.

If the IN conditional expression of the WHERE clause contains more then 15 constants and is being tested against an INT NULL column, then it will return no rows even if there are qualifying rows.

During object resolution, Microsoft SQL Server version 6.0 may not successfully resolve the dependency, which can cause a thread access violation.

There are several variations of the behavior depending on whether you are using a view or a stored procedure, and, more specifically, user created objects. User created objects may induce errors with stored procedure resolution.

In general, two situations that may lead to this are:
  1. The table is created, a view or stored procedure is created to reference the table, and then the table is dropped. This would normally result in an error.


  2. The table is created, a view or stored procedure is created to reference the table, and then the table is dropped and recreated.



The Microsoft SQL Server version 6.0 query optimizer may sometimes choose to use an index and sometimes choose to do a table scan when the query WHERE clause contains a search value that is greater than the largest distribution step for a given index, but less than the largest value in the table for the indexed column.

When you create a view using an optimizer hint to force an index and the index is specified by the index name, a query on that view fails with the following error:
Msg 308, Level 16, State 1
Index '%s' on table '%s' (specified in the FROM clause) does not exist.

When you open a cursor in a stored procedure, it can set all local variables inside the stored procedure to NULL if the DECLARE cursor statement is the first statement in the stored procedure.

If you execute a stored procedure that makes reference to objects in TempDB with a query plan size of 64 pages or more after you get a Level 16 Error, a client access violation (AV) may occur.

In Microsoft SQL Server version 6.0, a SELECT INTO statement that includes a correlated subquery using the aggregate functions SUM or AVG may cause a client access violation (AV).

When you dump the transaction log or the database while users are running queries which fire triggers that reference the INSERTED and DELETED tables, this may cause the users' queries to be blocked for the duration of the DUMP TRAN or DUMP DATABASE command.

When you attempt to create a worktable for storing intermediate results on a query involving an ORDER BY or GROUP BY clause for an oversized table, it can cause a server access violation.

When you create a table with a row size more than 1962 bytes, SQL Server generates a warning that the row is oversized, yet still creates the table. Inserting data into the table will work fine, as long as the actual data being inserted is less than 1962 bytes.

When you SELECT the data out without an ORDER BY or GROUP BY clause, the rows are retrieved normally, since there is no worktable involved. If a worktable is involved for ORDER BY or GROUP BY, then the server stops; it generates an unhandled exception, resulting in a Dr. Watson log.

The ALTER TABLE command allows the inclusion of nullable columns if used to create a PRIMARY KEY. This should not be allowed.

A thread-level access violation (AV) can occur during creation of the access plan of a stored procedure if the query involves three or more tables, two of which are temporary tables.

If you insert a NULL into a text field and then update the record that the text field belongs to and leave the text field NULL, a DBCC TEXTALLOC will show error 2574:
Index page number %ld is empty. Status = 0x%x.

If you add a constraint to a table, it resets the identity, causing it to insert zeros in the identity column for all subsequent table inserts.

When you attempt to create a table with the username.tablename format and a check constraint, you can get message 15009:
The object 'username.tablename' does not exist'.

Cursor declaration inside a stored procedure with aggregates and a variable may cause a client access violation (AV) during the execution of the stored procedure.


MORE INFORMATION

A DB-Library (DB-Lib) program that makes a call to dbopen() using the version of NTWDBLIB.DLL that ships with SQL Server 6.0 will leak a few hundred bytes every time the function call fails. When viewed with the SQL client configuration utility, WINDBVER, the version of the problem DLL is 6.00.121.

FIX: ODBC 6.0 Driver Sometimes Cannot Get Results w/ dbmsspx3

BUG# NT: 11051, 11037 (6.00)

Before you connect a SQL Server Driver version 2.50.121 or later to SQL Server version 4.21a, make sure that the INSTCAT.SQL file that ships with the driver has been executed on the server. The INSTCAT.SQL script upgrades the catalog stored procedures used by the driver.

FIX: Repl Synch Fails with Table Names That Have Ext Chars

BUG# NT: 11063 (6.00)

The INSERTED and DELETED tables inside a trigger are virtual tables created from their log entries. During a DUMP TRANSACTION or a DUMP DATABASE command, triggers that use these tables may be blocked until the DUMP TRANSACTION or DUMP DATABASE command is completed.

FIX: Create Worktable for Oversized Table Can Stop the Server

BUG# NT: 10083 (6.00)

Primary keys should not allow any nullable columns; this is enforced if the key is created using CREATE TABLE. However, nullable columns were being allowed when ALTER TABLE was used to create the PRIMARY KEY, and this should not have been the case.

FIX: AV When Creating Reformat Plan of SP Containing Join

BUG# NT: 10300 (6.00)


CAUSE

The Microsoft ODBC 2.50.0121 Win16 SQL Server driver cannot retrieve results from a SQL Server version 6.0 system if it is working with DBMSSPX3 network library and the server is configured to have a network packet size larger that 512 bytes.

The driver may perform a table scan while accomplishing the update.

FIX: Cannot Insert BLOB Data Larger Than 128k w/ SQLSetParam

BUG# NT: 10224 (6.00)

This problem only occurs when the SQLSetParam function is used.

The subscriber is expecting an ANSI code page, but the table name is stored for the replication service as an OEM code page, and no conversion is occuring.

FIX: EXECUTE Command w/ a Local Variable Can Cause Thread AV

BUG# NT: 10191 (6.00)

The error occurs when the EXECUTE statement treats an oversized buffer (more then 30 characters) as a stored procedure name instead of as a specific Transact-SQL command due to the use of invalid syntax for the EXECUTE statement itself.

For example: 

   use pubs
   go
   declare @strCommand  char(128)
   select @strCommand = 'select * from authors'
   execute @strCommand
   go

The behavior can be altered by changing the variable declarations. 

DECLARATION      BEHAVIOR
-----------      ---------
varchar(xxx)     Successful completion of command, or appropriate error
                 returned:
                 'Unable to find stored procedure 'select * from authors'

char(30)         Successful completion of command, or appropriate error
                 returned:
                 'Unable to find stored procedure 'select * from authors'

char(128)        Thread access violation.

The behavior does not change if you use a valid stored procedure name, such as sp_who; char(128)continues to cause an access violation, and char(30) continues to execute successfully. This is because char(30) is a valid length for an object identifier.

The query optimizer incorrectly calculates the cost of using an index. But even if the above conditions are met, the problem is still unlikely to occur with most queries. If you suspect this problem is occurring on a slow-running query, you should take other troubleshooting steps first, including running the UPDATE STATISTICS statement, examining the query's showplan output, and examining the table/index design as described in Knowledge Base article Q110352 - "INF: Optimizing SQL Server Performance."

Microsoft SQL Server version 6.0 incorrectly interprets the optimizer hint as an index name within a view.

All of the following conditions must exist for the client to AV:
  1. The query plan size of the stored procedure must be greater than 64 pages.

    You can find the size of the stored procedure by compiling the stored procedure and running the DBCC MEMUSAGE command.


  2. A Level 16 or greater error message inside or before executing the stored procedure.

    An example of a Level 16 Error message is a SELECT statement that tries to access a non-existent table.


  3. The stored procedure must make a reference to an object in TempDB.


The specific conditions under which this problem occurs are:
This problem does not always happen, but only with certain statistical distributions of data which cause the optimizer to build a reformat access plan.

If the empty text page is the first page in the text chain, DBCC TEXTALLOC will erroneously report a 2574 error.

This problem only occurs if the identity field was added by using the ALTER TABLE statement.

The full qualified table name is not being passed to the CHECK routine, and therefore the columns being tested do not have the base table information.


WORKAROUND

Set the SQL Server network packet size to 512 using either the SQL Enterprise Manager, or by issuing the following command in ISQL: 


   sp_configure 'network packet size', 512
   go
   reconfigure
   go

You should then stop and restart SQL Server. Other options are to use the Win32 ODBC driver, or to use the DBNMP3 network library instead of DBMSSPX3.

FIX: Updating a Text Column Results in a Table Scan

BUG# NT: 10959 (6.00)

Use the ODBC version 2.0 function SQLBindParameter instead of SQLSetParam.

FIX: DUMP DATABASE w/ Variable Device Name in SP May Cause AV

BUG# NT: 10328 (6.00)

When used outside of a stored procedure the commands work fine. Hard coding a device file path also works correctly.

FIX: IN Condition with More Than 15 Values May Fail

BUG# NT: 10951 (6.00)

Use an INT NOT NULL column to do these qualifications.

FIX: Unexpected Behavior During Object Resolution

Bug# NT: 10252 (6.00)

Drop and recreate the object(s) which rely on the missing or changed dependency object.

FIX: DBCC NEWALLOC Enhanced to use RA for Performance

BUG# NT: 10330 (6.00)

You can run the DBCC CHECKALLOC statement instead of DBCC NEWALLOC, which does use the RA and provides similar functionality.

FIX: Query Plan Inconsistent If Search Value > Last Step

BUG# NT: 10264 (6.00)

Use optimizer hints to force a given query plan.

FIX: Optimizer Hint for Index Name Fails in View



BUG# NT: 10266 (6.00)

In the optimizer hint, specify the index using the index id instead of the index name when you create the view. The index id can be obtained by querying the sysindexes system table.

FIX: Open Cursor Statement in SP Sets Variables to NULL

BUG# NT: 11170 (6.00)

Either of the following methods can be used to work around this problem:

Do not use the DECLARE cursor statement as the first statement in the stored procedure.

-OR-

Issue the DECLARE cursor statement inside an EXECUTE statement as follows: 

   EXEC ("DECLARE mycursor cursor for SELECT au_lname from authors")

FIX: Lvl 16 Error on Temp Tbl From SP w/ > 64 Pgs May Cause AV

BUG# NT: 10213 (6.00)

Reduce the size of the stored procedures such that their individual sizes are less than 64 pages.

EXAMPLE:

Assume that the following stored procedure has a size greater than 64 pages. 

   Create Procedure Get_Account_Information <Parameters> as
   Begin
     <Several Data Manipulation Statements say 1 through 10>
     <Several Data Manipulation Statements say 11 through 20>
   End

You can reduce the size of the above stored procedure by creating two stored procedures each having a size less than 64 pages. Keep the name of this stored procedure the same as the original one to avoid code changes to the scripts that call this stored procedure. 

   Create Procedure Get_Account_Information <Parameters> as
   Begin
      <Data Manipulation Statements 1 through 10>
      exec Get_Account_Information_Part2 <Parameters>
   /**  Keep these parameters exactly the same as the exec
   statement that called Get_Account_Information     **/ 
   End
   Create Procedure Get_Account_Information_Part2 <Parameters> as
   Begin
      <Data Manipulation Statements 11 through 20>
   End

If the original stored procedure has an OUTPUT parameter, return that parameter back from Get_Account_Information_Part2 to Get_Account_Information and then back to the caller of Get_Account_Information.

FIX: SELECT INTO w/ Correlated Subqueries May Cause AV

BUG# NT: 11058 (6.00)

The correlated subquery works when MAX or MIN is the aggregate function. However, when the AVG or SUM functions are used, then you should not use the correlated query, but split the query and use GROUP BY in the first query, get those results, and join with the tables in the second query to achieve the same results as in the correlated subquery.

FIX: DUMPs May Halt Queries That Have Triggers

BUG# NT: 11085 (6.00)

Ensure that the base table is not created to be larger than the maximum size of a row. Alternately, do not use ORDER BY or GROUP BY in SELECT statements that operate on oversized tables.

FIX: ALTER TABLE Allows Nulls in PRIMARY KEY

BUG# NT: 10968 (6.00)

There are three possible ways to avoid this problem.
  1. Create the temporary tables within the stored procedure.


  2. Use permanent tables instead of temporary tables.


  3. Use an optimizer hint on the temporary tables to force a table scan. See the SQL Server version 6.0 documentation for details on using optimizer hints.


FIX: Updating a Record w/ NULL Text Field Can Cause 2574 Error

BUG# NT: 9691 (6.00)

The error message is an erroneous error message and therefore is no cause for concern. To clear the message, you can move the date using bcp, transfer manager, or a SELECT INTO. You can also update the record and include valid, not null data for the text field.

FIX: Add Constraint Causes Identity-Column Inserts to Fail

BUG# NT: 11057 (6.00)

Move the data into a holding place with a SELECT INTO. Drop and recreate the table with the CONSTRAINT or IDENTITY in place from the beginning. Or, you can use a trigger temporarily to perform the check function.

FIX: Cannot Create a Qualified Table With a Check Constraint

BUG# NT: 9781 (6.00)

Where possible, refrain from using the qualified table name or use a trigger instead of the check constraint. If the qualified table name is necessary, use the SETUSER function to impersonate the table owner.

FIX: SET ARITHABORT ON Inadvertently Causes an Insert to Fail

BUG# NT: 9952 (6.00)

Perform an initial SELECT with the entire WHERE clause and INSERT into a temporary table, such as: 

   insert <temp storage>  select <cols>
   from <table> where <clauses>

followed by DECLARE CURSOR from the temporary table: 

   declare cursor for select col1 = sum(...)
   from <temp storage>


SUMMARY

The Read Ahead Manager (RA) is a mechanism unique to SQL Server version 6.0 which independently pre-fetches pages from disk to the buffer cache anticipating a query thread's request for additional pages. The DBCC NEWALLOC command has been enhanced to use this feature, resulting in improved performance.

You can use the ARITHABORT option to terminate a query when an overflow or divide-by-zero error occurs during query execution. With the new decimal and numeric datatypes, the ARITHABORT does not allow you to abort the loss of the most significant digit and ignores the loss of the least significant digit. Thus, the ARITHABORT option may raise a message 8115:

Arithmetic overflow error converting numeric to type numeric.

A new SET option NUMERIC_ROUNDABORT has been added to abort the truncation of numeric values. This option specifies the behavior following a loss of scale for an exact numeric type during conversion. By default this option is OFF allowing SQL Server to round the numeric result and continue processing. When this option is ON, SQL Server aborts the statement/query that caused the error.

FIX: Cursor Declaration In a Stored Procedure May Cause Client AV

BUG# NT: 11169 (6.00)

Additional query words: buglist patch service pack 


Keywords          : SSrvGen 
Version           : 6.0
Platform          : WINDOWS 
Issue type        :

Last Reviewed: April 14, 1999