FIX: SQL Server 6.5 Service Pack 1 Fixlist

• Microsoft SQL Server, version 6.5 Service Pack 1

The following is a list of fixes and other various improvements that have been made in the Microsoft SQL Server version 6.5 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

Q151301: FIX: dbcursorfetchex() Can Cause Blocking in DB-Library

ODBC COMPONENTS

Q153694: FIX: SQLNumResultCols Fails with Incorrect Syntax Near 'SET' Q153908: FIX: 16-bit Driver Times Out on Async Calls to SQLTables

SERVER COMPONENTS

Q149938: FIX: AV on INSERT of UNIONed SELECTs to Table With DEFAULTs Q149939: FIX: ROLLBACK of TRUNCATE TABLE May Cause Error 3301, AV Q150775: FIX: CREATE TABLE in Stored Procedure May Fail With Error 1750 Q150894: FIX: LOAD TRAN May Fail With Handled AV Q150896: FIX: SH_PAGE Locks May Be Held on Inner Tables of JOINs Q150897: FIX: LOAD TRAN May Fail With Error 1511 Q150900: FIX: UPDATE May Fail With Error 107 Q151111: FIX: DECLARE CURSOR on Temp Table with FOR UPDATE Causes AV Q151590: FIX: Error 4409 Generated When Using Multiple Database Views Q151591: FIX: DBCC Checkident Always Reports the Seed Value as Current Q151693: FIX: Err Msg "Unknown Token Received from SQL Server" Q151765: FIX: SELECT INTO Inside a Temp Procedure Causes Client to Hang Q151985: FIX: SELECTs from SYSPROCESSES Result in Access Violation Q151988: FIX: 1108 Errors with Heavy Tempdb Activity Q152353: FIX: Select with CASE Statement Inside View Can Cause Client AV Q152416: FIX: sp_processmail Will Only Process One Query per Execution Q152615: FIX: Syntax Error in SELECT List May Cause Handled AV Q152690: FIX: Count(*) May Not Return Result Set Q152709: FIX: Update of BLOB Data w/SQL Server 2.65.0201 Driver Stops Q152800: FIX: GROUP BY Clause without Index Executes More Slowly Q153006: FIX: Dump or Load w/Tape Devices Results in Errors, AVs Q153079: FIX: SQL Server Stops w/ Temporary Procedure in a Transaction Q153186: FIX: Filtered Stored Procedures Cannot Reference Multiple Tables Q153455: FIX: Multiple "LEFT OUTER JOIN" in FROM Clause May Cause Error Q153780: FIX: sp_droppublisher Does Not Clear 'pub' Server Option Q153802: FIX: Deadlock During Cursor Update Causes CPU Spin and Spinloop Q153803: FIX: GRANT ALL Fails to Give Permissions on Stored Procedures Q153836: FIX: SQLGetData Fails on Multiple Active Statement Handles Q153851: FIX: AV if 4 or More Correlated Subqueries with Self-Join Q153855: FIX: IRL can cause 3307, OS error 6, unhandled server level AV Q153954: FIX: Checkpoint Process Deadlock Results in Errors 603, 3314 Q153961: FIX: Full Memory and Cursors Cause AVs, 707, 706, and Spins Q153987: FIX: AV Error Using Temp Table and Cursor in Stored Procedure Q153992: FIX: Cannot Rename a Column with a Quoted Identifier Q154018: FIX: AV in Update Mode When Script Is Run Twice Q154047: FIX: SQL Terminates on Delete Table with 15 Self-References

FIX: dbcursorfetchex() Can Cause Blocking in DB-Library

ARTICLE-ID: Q151301 BUG #: NT 15039 (6.50)

SYMPTOMS

If a DB-library application calls dbcursorfetch() or dbcursorfetchex() successfully in one thread, subsequent calls to dbcursoropen() or dbclose() from other threads using the same DBPROCESS would be blocked. Sp_who and sp_lock show no blockage on the server side, and the DB-library application would appear to hang.

WORKAROUND

Use a separate DBPROCESS for each thread.

FIX: SQLNumResultCols Fails with Incorrect Syntax Near 'SET'

ARTICLE-ID: Q153694 BUG #: NT: 15314 (6.50)

SYMPTOMS

Calling SQLNumResultCols after SQLPrepare of a SELECT statement that contains a subquery in the select list will fail with:

   szSqlState = "37000", pfNativeError = 156
   szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
               Incorrect syntax near the keyword 'SET'."

When the SQL Server ODBC driver constructs the T-SQL statement that will be sent to the server to resolve the number of columns that will be returned in the resultset, it incorrectly parses the initial query and excludes the final table name. For example:

   SQLPrepare(hstmt, "SELECT t1.c1, (SELECT t2.c1 FROM t2)
                      FROM t1", 44)
   SQLNumResultCols(hstmt, pcol1)

   SET FMTONLY ON SELECT t1.c1, (SELECT t2.c1 FROM t2)
   FROM SET FMTONLY OFF

'SET'."

WORKAROUND

Where possible convert the offending query to a SQL Server VIEW.

FIX: 16-bit Driver Times Out on Async Calls to SQLTables

ARTICLE-ID: Q153908 BUG #: NT: 15563 (2.65.0201)

SYMPTOMS

When a 16-bit ODBC application calls SQLTables() using the Microsoft SQL Server ODBC Driver version 2.65.0201 set in asynchronous mode, a "Timeout expired" error message appears immediately after the second async retry.

This problem occurs with any network library configuration and Windows NT local pipes. The Timeout error message also appears regardless of other login or query timeouts you configure on the client computer.

WORKAROUND

To work around this problem, do not call the ODBC driver in asynchronous mode. This can be done with different applications and they apply as follows:

• For ODBC programs written directly to the ODBC API, call SQLSetStmtOption() with SQL_ASYNC_ENABLE (set to SQL_ASYNC_ENABLE_OFF).
• For Visual Basic 3.0 and Access 2.0 applications, set DisableAsync to 1 in the [ODBC] section of in the Vb.ini, App.ini or Msacc20.ini respectively.
• For Visual Basic 4.0, set the INIPATH property of DBENGINE to point to Vb.ini or App.ini that has DisableAsync set to 1.

   ARTICLE-ID: Q139044
   TITLE     : INF: How to Add Former MSACC20.INI ODBC Section to Registry

   jet database engine connectivity neil black

MORE INFORMATION

Microsoft client/server database applications, such as Access 2.0, Visual Basic 3.0 and Visual Basic 4.0 (16-bit), are designed on top of the Jet Database engine. Therefore, they attempt to make calls to SQLTables() during several ODBC operations, which include attaching to a SQL Server 6.5 table. These applications utilize ODBC in asynchronous mode, by default. Unless the default asynchronous behavior is turned off, the "Timeout Expired" error message will occur with the driver version 2.65.0201.

NOTE: Microsoft Excel and Microsoft Query are not designed on top of the Jet Database engine, thus the problem does not occur.

For more information about the Jet Database Engine, please refer to the Microsoft Jet Database Engine Programmer's Guide published by Microsoft Press. The ISBN number is 1-55615-877-7. You can order guide by calling (800) MSP-RESS.

FIX: AV on INSERT of UNIONed SELECTs to Table With DEFAULTs

ARTICLE-ID: Q149938 BUG #: NT: 14833 (6.00 and 6.50)

SYMPTOMS

INSERTing to a table from a UNION of two or more SELECT statements can result in a handled Access Violation.

MORE INFORMATION

At a certain threshold of data, a work table needs to be created to resolve the UNION correctly. If the target table has DEFAULTs, these are not applied to intermediate work tables but other attributes of the target table, such as NOT NULL, are applied, and this would cause the query to fail with a different error if there was not the AV.

WORKAROUND

Implement the INSERT as distinct INSERT SELECTs for each table in the UNION. If there is a UNIQUE INDEX on the target table, use the IGNORE_DUP_KEY option on that INDEX to filter out duplicate rows that would previously have been filtered out by the UNION. In many cases, this method can be much faster anyway because it can eliminate one or more intermedate work tables.

FIX: ROLLBACK of TRUNCATE TABLE May Cause Error 3301, AV

ARTICLE-ID: Q149939 BUG #: NT: 14849 (6.50)

SYMPTOMS

A TRUNCATE TABLE command that is aborted or rolled back may get a 3301 error "Invalid log record found in Syslogs (logop 42)" and an access violation.

If the truncated table has an IDENTITY column and the TRUNCATE aborts, or is inside a user transaction which later does a ROLLBACK, this problem can occur.

WORKAROUND

If the purpose of the TRUNCATE TABLE is just to remove all the rows, use DELETE with no WHERE clause. If the purpose is also to reset the IDENTITY value, use DELETE with no WHERE clause then TRUNCATE TABLE, and make sure there is no user defined transaction at the time of the TRUNCATE TABLE via logic such as

delete t1 while @@trancount > 0 begin

   commit tran

FIX: CREATE TABLE in Stored Procedure May Fail With Error 1750

ARTICLE-ID: Q150775 BUG #: NT: 14884 (6.50)

SYMPTOMS

EXECuting a stored procedure that creates a temp or permanent table with defaults may fail with the error:

   [INTERNAL ERROR] unable to locate original param in voidptr list.
   Msg 1750, Level 16, State 0
   Unable to create constraint. See previous errors.

If there is some activity in the stored procedure before the CREATE TABLE, this error can occur. In the simplest case, executing the following stored procedure will fail:

create proc sp_test1 as begin IF ( @@error <> 0 ) BEGIN

   GOTO cleanup

CREATE TABLE   #t1 (c1         int NOT NULL DEFAULT -1)

WORKAROUND

Use some other method than a CONSTRAINT, such as ISNULL, to get the default values you want into the table.

FIX: LOAD TRAN May Fail With Handled AV

ARTICLE-ID: Q150894 BUG #: 14952 (6.50)

SYMPTOMS

LOAD TRANSACTION may fail with the errors:

  udwritem: Operating system error 6(The handle is invalid.) on device ....
  ex_testhandle: stack overflow, top=0x17a7f10, end=0x17a7f10
  EXCEPTION_ACCESS_VIOLATION

WORKAROUND

DUMP the database and re-sync the transaction logs.

FIX: SH_PAGE Locks May Be Held on Inner Tables of JOINs

ARTICLE-ID: Q150896 BUG #: NT: 15329 (6.50)

SYMPTOMS

A join of tables may hold the shared page locks on the inner tables of the join(s) for the duration of the SELECT.

MORE INFORMATION

The page locks on the inner table(s) of joins where a unique index matches the join on the inner table are not released until the end of the SELECT. On earlier versions, SQL Server would obtain and release the locks one at a time as the page chain is traversed. The outer table's page locks are not held. If the tables are large, this can be a significant concurrency impact. This did not happen in prior versions of SQL Server.

WORKAROUND

Use non-unique indexes instead of unique.

FIX: LOAD TRAN May Fail With Error 1511

ARTICLE-ID: Q150897 BUG #: NT: 15114 (6.50)

SYMPTOMS

If the transaction log being LOADed contains the log records for a CREATE CLUSTERED INDEX on a large table (> 150 MB), then the transaction may fail to load with the 1511 error.

Error : 1511, Severity: 20, State: 8

   Sort cannot be reconciled with transaction log

WORKAROUND

DUMP the whole DATABASE after a CREATE CLUSTERED INDEX on a large table.

FIX: UPDATE May Fail With Error 107

ARTICLE-ID: Q150900 BUG #: NT: 14984 (6.50)

SYMPTOMS

If using the 204 trace flag for backwards compatibility, UPDATEs using JOINs may fail with an error 107.

Msg 107, Level 15, State 1

   The column prefix 'so' does not match with a table name or alias name
   used in the query

WORKAROUND

Turn off the 204 trace flag or rewrite the JOIN as a subquery.

FIX:DECLARE CURSOR on Temp Table with FOR UPDATE Causes AV

ARTICLE-ID: Q151111 BUG #: NT: 15086 (6.00)

SYMPTOMS

A DECLARE CURSOR statement that has a FOR UPDATE clause with multiple columns in the column list can generate a handled access violation if the table in the SELECT clause is a temporary table.

WORKAROUND

Do not use the FOR UPDATE clause for more than one column in case of a temporary table. Use a permanent table if more than one column is absolutely necessary in the FOR UPDATE clause.

FIX: Error 4409 Generated When Using Multiple Database Views

ARTICLE-ID: Q151590 BUG #: NT: 14645 (6.00)

SYMPTOMS

Under certain conditions, you may receive 4409 errors. The problem arises when the first view in the chain of views can be executed but subsequent views are unavailable for use. For example:

If you have viewA and viewC in the master database and viewB in the pubs database, where viewA selects * from viewB and viewB selects * from viewC and viewC selects * from sysdatabases. And, SQL Server has been stopped while a client continues to try to execute a select * from viewA. When SQL Server is restarted, the master database is always recovered first and then the subsequent databases. As soon as master is recovered, the client attempts to execute the select and receives the 4409 error because pubs has not yet been recovered.

The same behavior can occur when you take a database on and offline, or you try to drop and create viewC in the above scenario while someone is trying to access it.

WORKAROUND

Drop and add the views when you are sure no one is accessing them.

FIX: DBCC Checkident Always Reports the Seed Value as Current

ARTICLE-ID: Q151591 BUG #: 14759 (6.50)

SYMPTOMS

When running dbcc checkident, the current identity value will always be reported as the original seed value.

   Checking identity information: current identity value '1', maximum
   column value '14'. DBCC execution completed. If DBCC printed error
   messages, see your System Administrator.

FIX: Err Msg "Unknown Token Received from SQL Server"

ARTICLE-ID: Q151693 BUG #: NT: 15056 (6.50)

SYMPTOMS

When you set the statement options to use a server-side cursor and prepare a select statement on a SQL Server system table, the first execution of the select creates the cursor successfully. After you close this cursor, if you execute the prepared statement again, the following error message appears:

   unknown token received from SQL Server.

WORKAROUND

Use a forward-only cursor instead of a static, keyset, or dynamic cursor on the system tables. Note that the problem does not occur if a server-side cursor is created on a user-defined table or view.

If a forward-only cursor is unacceptable, prepare the select statement again on the system table and execute it to create a server-side cursor. Note that the first execution works fine and applications typically do not need to create a cursor on a system table repeatedly.

FIX: SELECT INTO Inside a Temp Proc Causes Client to Hang

ARTICLE-ID: Q151765 BUG #: NT: 15113 (6.50)

SYMPTOMS

In SQL Server version 6.5, executing a temporary stored procedure containing a SELECT INTO statement causes the client to stop responding. The server never finishes the execution and the control is not returned to the client. CPU utilization on the server computer goes above 95 percent and persists, with the result that the server slows down drastically. This does not prevent other clients from connecting to SQL Server, but the queries executed from these clients will be slow.

WORKAROUND

Use permanent stored procedure in place of temporary stored procedure. For ODBC clients, clear the Generate Stored Procedures for Prepared Statements option check box in the ODBC SQL Server Driver Setup dialog box, or set the SQL_USE_PROCEDURE_FOR_PREPARE option in the SQLSetConnectOption function to SQL_UP_OFF.

MORE INFORMATION

This behavior is also seen in Microsoft SQL Server 6.0.

With ODBC clients the same behavior is seen if:

1. Generate Stored Procedures for Prepared Statements option checkbox in

1. SQL_USE_PROCEDURE_FOR_PREPARE option in the SQLSetConnectOption function

and a SELECT INTO statement is prepared and executed.

FIX: SELECTs from SYSPROCESSES Result in Access Violation

ARTICLE-ID: Q151985 BUG #: NT: 15280 (6.00 and 6.50)

SYMPTOMS

On busy symmetric multiprocessing (SMP) computers, queries against the virtual table sysprocesses may infrequently get a handled access violation, causing the client connection to be terminated. Other clients are unaffected. The call stack of the access violation will look similar to the following:

findwaitfor+0x2f ins_sysproc+0x7fc make_fake+0xe8 s_setuptables+0x2bc s_execute+0x7d8 sequencer+0x23f execproc+0xfb7 s_execute+0xb8c sequencer+0x23f language_exec+0x65e

FIX: 1108 Errors with Heavy Tempdb Activity

ARTICLE-ID: Q151988 BUG #: NT: 15209 (6.50)

SYMPTOMS

SQL Servers with lots of tempdb activity, sorts, and deadlocks can get 1108 errors in the error log and at the client. The following error message appears:

   Error : 1108, Severity: 21, State: 1
   Cannot deallocate extent 944, database 2. Object id 0, index id 0,
   status 0 in extent does not match object id -49604, index id 0, status 0
   in object being deallocated.  Run DBCC CHECKALLOC

FIX: Select with CASE Statement Inside View Can Cause Client AV

ARTICLE-ID: Q152353 BUG #: WINDOWS: 15383 (6.00 and 6.50)

SYMPTOMS

A View that has a Select statement with a CASE construct that does a sub- select with an IN clause may cause the client to access violate (AV) during a Select from the View.

The following sample is the problem View definition:

   create view MyView as
   Select   Column1, Column2=
         Case
           When SomeColumn In
            (Select  SomeOtherColumn
              From   SomeOtherTable)
             Then SomeValue
           Else Null
         End
   From  MyTable

WORKAROUND

Try to avoid the CASE construct inside a View Definition or perform some pre-processing for the CASE logic before the View Definition by using the IF-ELSE construct and Temporary Tables, and then define the View on the Temporary Table.

FIX: sp_processmail Will Only Process One Query per Execution

ARTICLE-ID: Q152416 BUG#: 15475

SYMPTOMS

The stored procedure sp_processmail will only process one query sent via e- mail each time it is run if SQLMail is running with Microsoft Exchange Client software.

WORKAROUND

If you are scheduling sp_processmail as a task, you have the following options:

1. Change the task to run every minute. Note that this will work as long as there is not more than one query per minute sent to SQL Server via e-mail.

   provides a workaround for option 1 above.

   responds to by commenting the xp_deletemail call.

   instead do several iterations.

FIX: Syntax Error in SELECT List May Cause Handled AV

ARTICLE-ID: Q152615 BUG#: 15027 (6.0)

SYMPTOMS

The absence of a comma between columns in the SELECT list may cause a handled AV under some narrow circumstances.

The parser incorrectly perceives that an alias is intended. A reference to the object later in the SELECT query, usually in a GROUP BY or ORDER BY clause triggers the parser confusion.

WORKAROUND

Add the comma to the SELECT clause.

FIX: Count(*) May Not Return Result Set

ARTICLE-ID: Q152690 BUG#: 15418 (6.5)

SYMPTOMS

Count(*) may not return a result set. This problem occurs on tables with 34 or more columns. When a query is executed, it may return the following message:

   This command did not return data, and it did not return any rows

When you select from a view which has a definition like select * from another view you may not return results. The base view definition would be on a table with 34 columns or more.

WORKAROUND

You can use the following query instead of the Count(*) on the table

   select rows from sysindexes where name like 'My_Table_name'

   insert Destination_Table select * from Source_Table

FIX: Update of BLOB Data w/SQL Server 2.65.0201 Driver Stops

ARTICLE-ID: Q152709

SYMPTOMS

Update of a BLOB data causes SQL Server 2.65.0201 driver to stop with a syntax error. When an update statement is sent to the driver, it generates an invalid statement that results in the syntax error. This happens during an update of a large BLOB data (larger than 64K).

      The syntax error generated is
       Incorrect syntax near the keyword 'UPDATE'.
       State:37000, Native:156, Origin:[Microsoft][ODBC SQL Server
       Driver][SQL Server]

       Line 1"Incorrect syntax near '='
       State:37000, Native:170, Origin:[Microsoft][ODBC SQL Server
       Driver][SQL Server]

MORE INFORMATION

If the SQL Server 4030 trace is turned on , the 2.65.0201 SQL server driver generates the following syntax when an update statement of BLOB data is issued:

   SELECT TEXTPTR(imagecol) FROM imagetest where intcol=update imagetest
   set imagecol=0x00

This does not happen in 2.50.0121 (6.0 drivers).They generate:

   SELECT TEXTPTR(imagecol) FROM imagetest where imagecol LIKE
   0x000000

   WRITETEXT BULK imagetest.imagecol 0xa

FIX: GROUP BY Clause without Index Executes More Slowly

ARTICLE-ID: Q152800 BUG#: 14825

SYMPTOMS

If a Transact-SQL query contains a GROUP BY clause and an index is not defined on the column involved in GROUP BY, the query requires many more i/o’s than expected, esulting in slower performance. This problem occurs only only on the Alpha platform.

WORKAROUND

Sort the columns in the GROUP BY clause. Alternately, define an index encompassing the columns involved in the GROUP BY clause and make sure the index is used when grouping the columns in the query.

FIX: Dump or Load w/Tape Devices Results in Errors, AVs

ARTICLE-ID: Q153006 BUG #: NT: 15499 (6.50)

SYMPTOMS

Attempts to dump or load with tape devices may generate errors 3201, tbswritecheck, or access violations (AVs). Msg 3201 and the tbswritecheck errors are seen when attempting to dump; the AV may occur during the load.

WORKAROUND

Do not dump to a tape device; dump and load using a different type of dump device, such as disk. If dumping to disk, use another backup package, such as Windows NT Backup, to archive the dump file to tape.

MORE INFORMATION

The behavior and error messages may vary from computer to computer. Some computers may report a Msg 3201 while the same tape drive and software may work correctly on a different computer, or the load process may generate an access violation (AV).

The 3201 and tbswritecheck errors may occur on any of the DUMP commands (DATABASE, TRANSACTION), and the AV may occur on any of the LOAD commands (DATABASE, TRANSACTION, TABLE).

For a robust backup and restore strategy, Microsoft recommends occasional verification that dumps can be loaded, along with additional testing when a device or software is changed. Certain database errors may also prevent a successful load, so it is important to run the recommended DBCC commands at the time of each backup.

FIX: SQL Server Stops w/ Temporary Procedure in a Transaction

ARTICLE-ID: Q153079 BUG# 15570 (6.50)

SYMPTOMS

When a temporary stored procedure is created and executed within a user- defined transaction and the client (DBLIB or ODBC) disconnects without either committing or rolling back the transaction, SQL Server stops running. The SQL Service manager shows a red light.

An attempt to kill the process that initiated the transaction also causes SQL Server to stop running.

WORKAROUND

Use a permanent stored procedure instead of a temporary stored procedure within a transaction.

You can also commit or roll back the transaction before the client disconnects.

MORE INFORMATION

The SQL Server errorlog (under SQL..\LOG directory on the server) would look like:

Error : 631, Severity: 21, State: 1

spid12   The length of 116 passed to delete row routine for the row at

kernel   WARNING: Process being freed while holding Dataserver semaphore

kernel   udread: Operating system error 6(The handle is invalid.) on device
'C:\SQL60\DATA\MASTER.DAT' (virtpage 0x000002fb).

kernel   mirrorproc: i/o error on primary device 'C:\SQL60\DATA\MASTER.DAT'

FIX: Filtered Stored Procedures Cannot Reference Multiple Tables

ARTICLE-ID: Q153186 BUG #: NT: 15451 (6.50)

SYMPTOMS

The Filtered Stored procedures used to facilitate horizontal partitioning in SQL Server 6.50 replication may cause the Logreader task to keep retrying with the following message:

   ConnectionTransact (GetOverLappedResult()).Possible network error:
   Write to SQL Server. Failed. Connection broken.

WORKAROUND

To avoid encountering the bug you can:

• Publish the entire table instead of using horizontal partitioning.

• Create a permanent table with the data from the original tables selectively siphoned out (using triggers with the partition condition) and then publish the new table.

FIX: Multiple "LEFT OUTER JOIN" in FROM Clause May Cause Error

ARTICLE-ID: Q153455 BUG #: NT: 15565 (6.50)

SYMPTOMS

Multiple LEFT OUTER JOIN tables in the FROM clause of a SELECT query may cause SQL Server error 803. For example, the following query:

   select authors.au_id
   from
   (((( authors
   left outer join titleauthor on authors.au_id=titleauthor.au_id)
   left outer join titles on titleauthor.title_id=titles.title_id)
   left outer join authors a1 on a1.au_id=titleauthor.au_id)
   left outer join roysched on titles.title_id=roysched.title_id)
   where (roysched.royalty = 20)

      Msg 803, Level 20, State 2
      Unable to place buffer 0x0 holding logical page 424 in sdes for
          object
      'titleauthor' - either there is no room in sdes or buffer already in
      requested slot.
      The SQL Server is terminating this process.

FIX: sp_droppublisher Does Not Clear 'pub' Server Option

ARTICLE-ID: Q153780 BUG #: NT: 15184 (6.50)

SYMPTOMS

The sp_droppublisher stored procedure, used to drop a publication server, does not properly clear the 'pub' server option from sysservers. This will prevent users from properly disabling a publication server on the screen "Enable Subscribing from these Servers."

WORKAROUND

In the master database of any server participating in replication, please replace the sp_droppublisher procedure by running the following script:

use master go if exists (select * from sysobjects where sysstat & 0xf = 4 and name =

'sp_droppublisher')
   drop procedure sp_droppublisher

   @publisher varchar (30),        /* publisher server name */
   @type varchar (5) = null     /* null or 'dist' */
        ) as

    declare @distaccount varchar(127)
    declare @proc varchar (255)
    declare @retcode int
    declare @privilege varchar (30)

    /*
    ** parameter check:  @publisher.
    ** check to make sure that the publisher exists, that the name isn't
    ** null, and that the name conforms to the rules for identifiers.
    */

    if @publisher is null
        begin
            raiserror (14043, 16, -1, 'the publisher')
            return (1)
        end

    execute @retcode = sp_validname @publisher

    if @retcode <> 0
   return (1)

    /*
    ** perform special logic if dropping a publisher for a distribution
    ** server.
    */
    if lower(@type) = 'dist'
      begin
       if not exists (select *
      from master..sysservers
                where srvname = @publisher
                and srvstatus & 16 <> 0)

       begin
      raiserror (14080, 11, -1)
           return (1)
       end

       execute @retcode = sp_serveroption @publisher, 'dpub', false
       if @@error <> 0 or @retcode <> 0 return (1)

            if exists (select * from master..sysremotelogins
          where remoteserverid = (select srvid from master..sysservers
          where srvname = @publisher)
          and remoteusername = 'sa'
          and suid = 1)    /* 'sa' */
       begin
          execute @retcode = sp_dropremotelogin @publisher, sa, sa
          if @@error <> 0 or @retcode <> 0 return (1)
       end

            if exists (select * from master..sysremotelogins
          where remoteserverid = (select srvid from master..sysservers
          where srvname = @publisher)
          and remoteusername = 'probe'
          and suid = 10)   /* 'probe' */
       begin
          execute @retcode = sp_dropremotelogin @publisher, probe, probe
          if @@error <> 0 or @retcode <> 0 return (1)
       end

       return (0)
   end

    /*
    ** make sure the server is defined as a 'publisher'.
    */
    if not exists (select *
                     from master..sysservers
                    where srvname = @publisher
                      and srvstatus & 2 <> 0)

        begin
            raiserror (14080, 11, -1)
            return (1)
        end

    /*
    ** turn off the server option to indicate that this is a publisher.
    */
    execute @retcode = sp_serveroption @publisher, 'pub', false
    if @@error <> 0 or @retcode <> 0 return (1)

    /*
    ** fetch the publisher's distributor account.
    */

    select @proc = rtrim(@publisher) + '.master..sp_helpdistributor '
    exec @retcode = @proc @account = @distaccount output
    if @@error <> 0 or @retcode <> 0
        begin
            raiserror (14071, 16, -1)
            return (1)
        end

    /*
    ** if @distaccount = 'localsystem' assume 'admin' privilege
    */
    if @distaccount = 'localsystem'
       return (0)

    /*
    ** if @distaccount has 'admin' privilege, do not revoke
    */
    execute @retcode = master.dbo.xp_logininfo @distaccount, 'all',
       @privilege = @privilege output
    if @@error <> 0 or @retcode <> 0 return (1)

    if @privilege = 'admin'
       return  (0)

    /*
    ** revoke replication privilege to the distributor nt account.
    */
    exec @retcode = master.dbo.xp_revokelogin @distaccount
    if @@error <> 0 or @retcode <> 0 return (1)

To clear the problem before applying the procedure, you can manually disable the 'pub' server option by executing the following statement on the subscription server:

use master go sp_serveroption <publication server>, 'pub', false go

Example:

use master go sp_serveroption AIKMAN, 'pub', false go

FIX: Deadlock During Cursor Update Causes CPU Spin and Spinloop

ARTICLE-ID: Q153802 BUG #: 15422 (6.50)

SYMPTOMS

A group of updates through cursors that result in the cursor being a deadlock victim can cause the following error to appear in the error log:

   closetable:table already closed for sdes %d

WORKAROUND

When using cursors for updates make sure they will not get into a deadlock situation.

FIX: GRANT ALL Fails to Give Permissions on Stored Procedures

ARTICLE-ID: Q153803 BUG #: 15102 (6.50)

SYMPTOMS

When a user attempts to GRANT ALL to a stored procedure, the permissions can fail to be applied. No errors are reported; SQL Server simply does not give permission to the stored procedure. Once this happens to a stored procedure, it will always happen, and GRANT ALL will never work for that stored procedure.

WORKAROUND

To grant permissions to a stored procedure, use GRANT EXEC instead of GRANT ALL.

FIX: SQLGetData Fails on Multiple Active Statement Handles

ARTICLE-ID: Q153836 BUG #: 15053 (6.50)

SYMPTOMS

When there are two active statement handles, SQLGetData fails to fetch results from the two statement handles simultaneously. The following error is generated:

   szErrorMsg="[Microsoft][ODBC SQL Server Driver]Connection is busy
   with results for another hstmt"

WORKAROUND

Bind the result set columns using SQLBindCol before a fetch. This allows multiple active statement handles.

MORE INFORMATION

When you are using server-based cursors, the connection between the client and server does not remain busy between operations. This allows you to have multiple cursors statements active at the same time. However, with the SQL Server ODBC driver 2.65.0121v, an attempt to fetch data using SQLGetData between multiple statement handles fails. If SQLFetch is being done on a statement handle hstmt1 and if, before a result of SQL_NO_DATA_FOUND is returned, another statement hstmt2 is allocated and another fetch operation is done, and then a simultaneous SQLGetData is done on hstmt1, the result will be a "Connection is busy with results" error.

FIX: AV if 4 or More Correlated Subqueries with Self-Join

ARTICLE-ID: Q153851 BUG #: 14802 (6.50)

SYMPTOMS

If a complex query does a self-join and has four or more subqueries, all referencing the same table, it may get a thread-level access violation (AV) if the table has only a non-clustered index.

WORKAROUND

Convert the non-clustered index to a clustered index, or drop it.

FIX: IRL can cause 3307, OS error 6, unhandled server level AV

ARTICLE-ID: Q153855 BUG #: NT: 15569 (6.50)

SYMPTOMS

If a table is set to enable Insert - Row Locking (IRL) with "sp_tableoption

'table_name', 'Insert row lock', true"  within a transaction and the user

'%d', object '%Id',index '%d'." and an unhandled server level access

MORE INFORMATION

On a single processor computer, when the user exits without 'commit tran', it can cause the following error in the errorlog:

Error : 3307, Severity: 21, State: 1 Process 10 was expected to hold logical lock on page 336. WARNING: Process being freed while holding Dataserver semaphore udread: Operating system error 6(The handle is invalid.) on device

'C:\MSSQL\DATA\MASTER.DAT' (virtpage 0x000009a4).

'C:\MSSQL\DATA\MASTER.DAT' (virtpage 0x00000394).

'C:\MSSQL\DATA\MASTER.DAT' (virtpage 0x00000383).

SQL Server will terminate itself afterwards. When SQL Server is restarted and DBCC CHECKDB("pubs") is executed, the results sometimes report error 602: Could not find row in Sysindexes for dbid '%d', object '%Id',index

'%d'.

FIX: Checkpoint Process Deadlock Results in Errors 603, 3314

ARTICLE-ID: Q153954 BUG #: NT: 15307 (6.50)

SYMPTOMS

A stored procedure that performs the following actions:

1. creates a temporary table

   contents of the temporary table

can, if executed by multiple users, result in the Checkpoint Process being chosen as a deadlock victim (spid 3). In this case, the following message will come up three times:

   Your server command (process id 3) was deadlocked with another process
   and has been chosen as a deadlock victim. Re-run your command.

   603: There are not enough system session descriptors available to run
   this query. The maximum number available to a process is %d. Split query
   and rerun.

   3314: Error while undoing log row in database '%.*s'. %S_RID.

If you run sp_who prior to receiving 1105 errors in tempdb, the Checkpoint Process is not present, and if the client processes continue to run, the result is that tempdb eventually fills because the log is not being truncated.

FIX: Full Memory and Cursors Cause AVs, 707, 706, and Spins

ARTICLE-ID: Q153961 BUG #: NT: 14828 (6.50)

SYMPTOMS

A user application that uses engine side cursors in a stored procedure can cause a myriad of problems when available SQL Server system memory becomes full. Usually it starts with error 707:

   System error detected during attempt to free memory at address 0x%1x.
   Please consult the SQL Server error log for more details.

   Process %d tried to remove PROC_HDR 0x%lx that it does not hold in Pss.

MORE INFORMATION

When the SQL Server procedure cache needs to swap out a stored procedure it can incorrectly deallocate the procedure twice. This can cause a memory leak as well as the access violations. This will only occur if the stored procedure is using engine side cursors.

WORKAROUND

Increasing the amount of procedure cache available to SQL Server will reduce the chance of the problem occurring. This can be accomplished by giving SQL Server more memory to use, or by increasing the procedure cache using sp_configure.

FIX: AV Error Using Temp Table and Cursor in Stored Procedure

ARTICLE-ID: Q153987 BUG #: NT: 15510 (6.50)

SYMPTOMS

A stored procedure that performs selects against a cursor with a temporary table in the cursor will fail with a handled access violation or with the following error message:

   Msg 208 "Invalidobject name #temptable"

WORKAROUND

The problem only occurs if both the temporary table and the cursor are both created inside a stored procedure. Creating the temporary table outside of the stored procedure will allow the stored procedure to function correctly. An alternative workaround would be to convert the temporary table to a permanent table.

FIX: Cannot Rename a Column With a Quoted Identifier

ARTICLE-ID: Q153992 BUG #: NT: 15100 (6.50)

SYMPTOMS

If you try to rename a table's column to use a quoted identifier column name, sp_rename gives an error indicating that the column name is invalid. The errors are as folows:

   Msg 15006, Level 16, State 1
   '<column name>' is not a valid name since it contains invalid characters

   Msg 15224, Level 11, State 1
   Error, the value for parameter NewName contains invalid characters or
   violates a basic restriction

WORKAROUND

Export the data. Drop and re-create the table with quoted identifiers on necessary columns. Import the data back in.

FIX: AV in Update Mode When Script Is Run Twice

ARTICLE-ID: Q154018 BUG#: 14827 (6.50)

SYMPTOMS

This problem arises with simple scripts that use trace flag 323 to determine if UPDATE IN PLACE is occurring. When you run such a script twice, a handled access violation (AV) occurs.

WORKAROUND

To avoid this problem, do not use trace flag 323. Without the trace flag, the script will run successfully.

FIX: SQL Terminates on Delete Table with 15 Self-References

ARTICLE-ID: Q154047 BUG #: NT: 15629 (6.50)

SYMPTOMS

Trying to delete a table that contains 15 self-references can cause the system to return the following errors and warnings:

   Error : 1202, Severity: 20, State: 2
   Table_lock was called with illegal mode 0.

   WARNING: Process being freed while holding Dataserver semaphore

   udwritem: Operating system error 6(The handle is invalid.) on device
   'C:\MSSQL\DATA\MASTER.DAT' (virtpage 0x00002739).

   udread: Operating system error 6(The handle is invalid.) on device
   'C:\MSSQL\DATA\MASTER.DAT' (virtpage 0x00000394).

   Error: 5157, Severity: 20, State: 0
   Recursive error 1202 in ex_print

WORKAROUND

The workaround for this problem is to avoid having more than 14 self- references on a table. Establish another table that can contain the additional required references.

MORE INFORMATION

This problem is specific to 15 self-references and hence is extremely uncommon.

NOTE: If a default debugger is set up, it will generate a debug log (like Dr. Watson log).

FIX: SELECT INTO Locking Behavior

ARTICLE-ID: Q153441 BUG #: 14818 (DCR, 6.50)

SUMMARY

In SQL Server 6.5, SELECT INTO wraps within a transaction. Tables created by using SELECT INTO hold to the ACID (atomicity, consistency, isolation, durability) transaction properties. This also means that system resources, such as pages, extents, and locks, are held for the duration of the SELECT INTO statement. With larger system objects, this leads to the condition where many internal tasks can be blocked by other users performing SELECT INTO statements. For example, on high-activity servers, many users running the SQL Enterprise Manager tool to monitor system processes can block on each other, which leads to a condition where the SEM application appears to hang.

MORE INFORMATION

When you upgrade to SQL Server 6.5 Service Pack 1, SELECT INTO locking characteristics is a system settable feature. Wrapping the SELECT INTO with a transaction remains the default behavior. For users wishing not to hold system catalog locks on the activity, a trace flag has been added to allow for such operations. To apply the trace flag, start the server with the -T5302 command line parameter, or from within a query window, use the following commands:

> dbcc traceon (3604) > go > dbcc traceon (5302) > go

When the 5302 trace flag is applied and a SELECT INTO fails, the table is still created. Note that the locking behavior you select is applied for all databases within the server.