FIX: SQL Server 6.5 Service Pack 1 Fixlist (153096)

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

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

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

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
				

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

   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)
				

delete t1
while @@trancount > 0
begin
   commit tran
end
truncate table t1
				

   select rows from sysindexes where name like 'My_Table_name'
				

   insert Destination_Table select * from Source_Table
				

use master
go
if exists (select * from sysobjects where sysstat & 0xf = 4 and name =
'sp_droppublisher')
   drop procedure sp_droppublisher
go
create 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)
go
				

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

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

MORE INFORMATION

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)
				

This results in the following statement being sent to the server on the call to SQLNumResultCols:

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

Hence, causing SQL Server to report "Incorrect syntax near the Keyword 'SET'."

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

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.

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
END
CREATE TABLE   #t1 (c1         int NOT NULL DEFAULT -1)
cleanup:
end
go
declare @rc int
EXEC @rc = sp_test1
SELECT @rc
go
				

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.

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 the ODBC SQL Server Driver Setup dialog box is checked.
2. SQL_USE_PROCEDURE_FOR_PREPARE option in the SQLSetConnectOption function is set to SQL_UP_ON.

and a SELECT INTO statement is prepared and executed.

FIX: SELECTs from SYSPROCESSES Result in Access Violation

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 is an invalid syntax.

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: SQL Server Stops w/ Temporary Procedure in a Transaction

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

FIX: Filtered Stored Procedures Cannot Reference Multiple Tables

FIX: Deadlock During Cursor Update Causes CPU Spin and Spinloop

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

After this error occurs, the process can become unkillable, and its status in sysprocesses is marked as "spinloop." At this point SQL Server becomes very unresponsive and will often stop responding entirely, allowing no one to log on or shut it down. Processes that were accessing the tables become blocked.

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

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

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

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

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 fails to end the transaction with 'commit tran' or 'rollback tran' before exiting, it could cause 3307 "Process %ld was expected to hold logical lock on page %ld.", OS error 6, 602 "Could not find row in Sysindexes for dbid '%d', object '%Id',index '%d'." and an unhandled server level access violation (AV).

MORE INFORMATION

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

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'.

After a user exits on a multi-processor computer, sp_who shows the spid is still valid and the status shows 'spinloop', which does not allow you to kill the spid. DBCC CHECKDB or other queries will either hang, or become extremely slow. There will also be many bufwait() errors in the errorlog. Stopping SQL Server would either cause an AV or cause SQl Server to stop responding.

FIX: Checkpoint Process Deadlock Results in Errors 603, 3314

SYMPTOMS

A stored procedure that performs the following actions:

1. creates a temporary table
2. inserts data into the table
3. creates a cursor for read-only access that is then used to update the contents of the temporary table
4. drops the cursor
5. drops 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.

The message will be followed by the following errors:

A thread-level Access Violation will also be displayed.

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

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.

Or Error 706:

These are then followed by handled access violations. The access violations can also occur without the 707 or 706 errors. After the access violations, SQL Server will often lock up and become unusable as it goes into a 100 percent CPU spin.

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

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:

The problem will only occur after SQL Server has been shut down and restarted after the initial creation of the stored procedure. If the stored procedure is then dropped and re-created, it will work again until SQL Server is shut down and restarted.

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

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:

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

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

SYMPTOMS

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

This action also results in an unhandled exception, essentially terminating the server.

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

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.