ID: Q164022
The information in this article applies to:
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.
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
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)
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.
To work around this problem, do either of the following:
-or-
ARTICLE-ID: Q158584 BUG #: 15789 (6.50, NT)
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)
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
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
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.
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)
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.
Use the KILL command with discretion.
FIX: Permission Denied Error Message 229 When Updating a Table
ARTICLE-ID: Q158793 BUG #: 16107 (6.50)
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.
To work around this problem, do one of the following:
ARTICLE-ID: Q158806 BUG #: 15855 (6.50)
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.
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)
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
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)
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.
To work around this problem, do one of the following:
ARTICLE-ID: Q158856 BUG #: 16081 (sqlbug_65 sql 6.5)
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.
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)
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.
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)
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.
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)
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.
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)
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.
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)
An access violation (AV) may infrequently occur when issuing a LOAD DATABASE or LOAD TRANSACTION command on a database that is read-only.
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)
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)
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
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)
A join that returns a NULL in a smallint column may result in a thread level access violation (AV).
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)
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.
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)
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)
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')
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)
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.
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)
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 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)
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.
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)
If you perform a CREATE TABLE and an ALTER TABLE ADD CONSTRAINT concurrently, you may receive deadlocks on system tables.
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)
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)
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)
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:
-or-
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)
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.
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)
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.
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)
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.
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
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
To work around this problem, do either of the following:
-or-
ARTICLE-ID: Q161223 BUG #: 15911
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
)
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)
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.
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)
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
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)
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.
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.
To work around this problem, do either of the following:
-or-
ARTICLE-ID: Q171879 BUG #: 15522 (Windows: 6.5)
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.
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.
Additional query words: SP2 database patch sp
Keywords : SSrvGen
Version : 6.5 SP2
Platform : WINDOWSLast Reviewed: November 8, 1997