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 1DB-LIBRARY 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.
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:
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
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:
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:
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:
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:
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/ dbmsspx3BUG# 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 CharsBUG# 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 ServerBUG# 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 JoinBUG# 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.
FIX: Cannot Insert BLOB Data Larger Than 128k w/ SQLSetParamBUG# 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 AVBUG# 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:
The behavior can be altered by changing the variable declarations.
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:
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:
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 ScanBUG# 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 AVBUG# 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 FailBUG# NT: 10951 (6.00)Use an INT NOT NULL column to do these qualifications. FIX: Unexpected Behavior During Object ResolutionBug# 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 PerformanceBUG# 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 StepBUG# NT: 10264 (6.00)Use optimizer hints to force a given query plan. FIX: Optimizer Hint for Index Name Fails in ViewBUG# 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 NULLBUG# 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:
FIX: Lvl 16 Error on Temp Tbl From SP w/ > 64 Pgs May Cause AVBUG# 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.
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.
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 AVBUG# 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 TriggersBUG# 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 KEYBUG# NT: 10968 (6.00)There are three possible ways to avoid this problem.
FIX: Updating a Record w/ NULL Text Field Can Cause 2574 ErrorBUG# 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 FailBUG# 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 ConstraintBUG# 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 FailBUG# NT: 9952 (6.00)Perform an initial SELECT with the entire WHERE clause and INSERT into a temporary table, such as:
followed by DECLARE CURSOR from the temporary table:
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.
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 AVBUG# NT: 11169 (6.00)Additional query words: buglist patch service pack
Keywords : SSrvGen |
Last Reviewed: April 14, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |