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

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

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

Using an ANSI JOIN together with a correlated subquery inside an EXISTS clause returns the parser error 403: 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:

1. Non-SA user runs stored procedure A.
2. Stored procedure A calls procedure B.
3. 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: 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:

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.

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

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: 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: 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: A 'create table' also fails, with the message:

FIX: Error 15224 Renaming Column to 1 Character with Sp_rename

You receive the following parameter error when you attempt to use sp_rename to rename a column to a single character: violates a basic restriction ().

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

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.

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

1. Run the procedure.
2. Perform sp_recompile on an underlying permanent table referenced in the procedure.
3. 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.

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

   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
				

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