From Support Wiki
13 June 1997
Microsoft SQL Server and the UNION Clause
Problem Description:
Errors can occur when a SELECT statement with a UNION clause is submitted to Microsoft SQL Server. These errors can be in one of two forms:
1. The error "Cannot open a cursor on a stored procedure that has anything other than a single select statement in it" occurs. Items numbered (1) throughout the rest of this article are specific to this issue.
2. If the statement contains bind variables, an error can occur indicating a problem with the data to be bound. The exact error depends on the data types involved. Items numbered (2) throughout the rest of this article are specific to this issue.
Problem Status:
Microsoft acknowledges both of these problems. Specifics follow.
1. When Centura Team Developer or SQLWindows submits a statement to Microsoft SQL Server 6.x and result set mode is enabled, the server is requested to open a cursor. Microsoft has acknowledged a bug (14576) in SQL Server that causes an error when opening a cursor if a UNION clause is included in the submitted SQL statement. This problem is reported as specific to version 6.5. Read Microsoft Knowledge Base article Q161171 for more details. Some suggested remedies are provided there.
2. ODBC provides a function, SQLDescribeParam(), that returns information about a bind variable (parameter marker) in a prepared SQL statement. The ODBC router provided by Centura uses this function to determine what data type to specify when calling the ODBC function SQLBindParameter(). When a UNION clause is part of the SQL statement, SQLDescribeParam() often returns the incorrect information. SQLBindParameter() is then called with the wrong information. For example, consider the SQL statement:
select * from sales where ord_date = :dfDate1 union select * from sales where ord_num = :nOrdid and ord_date = :dfDate2
For the parameter represented by :nOrdid, SQLDescribeParam() returns its data type as SQL_TIMESTAMP, which is incorrect. The ODBC router uses this information when it calls SQLBindParameter(). The actual data, of course, is not a timestamp, and so the server will return an "invalid timestamp" error when the statement is ultimately executed. As another example, SQLDescribeParam() might correctly identify a parameter as SQL_VARCHAR, but with a length that is incorrect. If that length is too short, the error would be "String data right truncation". Microsoft has stated they will not support SQLDescribeParam() when the SQL statement includes the UNION clause.
Recommendation:
Centura recommends that you monitor the Microsoft Knowledge Base for updates to these problems. For now, consider the following actions.
1. Microsoft Knowledge Base article Q161171 suggests several options. The simplest, clearing the "generate stored procedure" flag in the Data Source Name definition via the ODBC administrator, is suggested by Centura. This can have a somewhat negative effect on performance. The programmer has no direct influence over the use of the other suggestions. Optionally, the programmer can disable result set mode at least for the duration of the SQL statement containing the UNION clause.
2. Centura is tracking this issue as our defect 62704A. For now, the ODBC router relies on SQLDescribeParam(). The only way to avoid its use is to avoid bind variables. Instead, build the SQL statement as a string using SAL string functions and string operators. Wherever a bind variable would appear, substitute its value in the string. The end result would be a SQL statement acceptable to ISQL, with literals where the bind variables would have appeared. For example, instead of submitting the statement:
select * from sales where ord_date = :dfDate1 union select * from sales where ord_num = :nOrdid and ord_date = :dfDate2
submit the statement:
select * from sales where ord_date = '1/1/97' union select * from sales where ord_num = 100 and ord_date = '4/15/97'
If at all feasible, avoid the use of the UNION clause to avoid encountering these issues.
Larry Johnson
Centura Software