BUG: Sp_Cursoropen Returns Error for a UNION Query

• Microsoft SQL Server version 6.5

SYMPTOMS

If you open an engine cursor using sp_cursoropen on a stored procedure that contains a UNION query, you will receive the following error:

   Msg 16937, Level 16, State 1
   "Cannot open a cursor on a stored procedure that
   has anything other than a single select statement in it"

WORKAROUND

To work around this problem, use the following steps:

1. Clear the "Generate Stored Procedure for Prepared statement" option in the ODBC DataSource setup.

2. Run the statement using SQLExecDirect, without preparing the statement.

3. Do not enclose the SQL statement that contains the UNION clause inside a stored procedure.

STATUS

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

MORE INFORMATION

A server cursor can only be created on a stored procedure that contains a single SELECT statement. However, a UNION always produces a consistent single result set, and should be allowed as a static cursor, as documented in the SQL Server ODBC driver Help. ODBC applications will have problems running SQL statements that contain the UNION clause, because the "Generate Stored Procedure for Prepared Statement" option in the Datasource setup is enabled by default. This results in the creation of temporary stored procedures for every prepared statement, causing an error on opening a server cursor.