BUG: Cursor Open 533 Error on UNION if MAX or MIN in Subquery

ID: Q147674

The information in this article applies to:
BUG# NT: 13504 (6.00)

SYMPTOMS

Errors can occur when you open a server cursor on a UNION query where the unioned SELECTs contain correlated subqueries which contain MIN() or MAX(). Db-library clients see the following errors:

DB-Library error 10008: Possible network error: Bad token from SQL
Server: Datastream processing out of sync.
Msg 533, Level 20, State 4
Can't find a range table entry for range 4.
DB-Library Process Dead - Connection Broken

ODBC clients see the following single error if they call SQLError():
szSqlState = "S1000", *pfNativeError = 0,
szErrorMsg="[Microsoft][ODBC SQL Server Driver]
Unknown token received from SQL Server"

The problem happens on all types of server cursors (Dynamic, Keyset, Static, Forward Only).


STATUS

Microsoft has confirmed this to be a problem in the Microsoft SQL Server version 6.00. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

This problem no longer occurs in version 6.50.


WORKAROUND

Do not use a server cursor to process the command. Alternatively, perform the two UNIONed SELECTs into temporary tables and then perform the union between the temporary tables. For example, take: 


   select distinct t1.type
   from titles t1
   where t1.type in ( select max(t2.type)
                                  from titles t2
                                  where t1.pub_id != t2.pub_id )
   union
   select distinct t1.type
   from titles t1
   where t1.type in ( select max(t2.type)
                                  from titles t2
                                  where t1.pub_id != t2.pub_id )
and change it to: 

   select distinct t1.type into #temp1
   from titles t1
   where t1.type in ( select max(t2.type)
                                  from titles t2
                                  where t1.pub_id != t2.pub_id )
   go
   select distinct t1.type into #temp2
   from titles t1
   where t1.type in ( select max(t2.type)
                                  from titles t2
                                  where t1.pub_id != t2.pub_id )
   go
   select * from #temp1
   union
   select * from #temp2

Additional query words: sql6 cursor tsql 


Keywords          : kbprg SSrvProg SSrvTran_SQL kbbug6.00 
Version           : 6.0
Platform          : WINDOWS 
Issue type        :

Last Reviewed: March 24, 1999