Microsoft KB Archive/161171

= BUG: Sp_Cursoropen Returns Error for a UNION Query =

Article ID: 161171

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q161171



BUG #: 14576 (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.

Additional query words: cursors

Keywords: kbbug kbusage KB161171

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.