Microsoft KB Archive/897886

= BUG: The return data type depends on the location of a UNION operation where char and varchar data types are combined in SQL Server 2000 =

Article ID: 897886

Article Last Modified on 11/2/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Desktop Engine (Windows)
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Personal Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Enterprise Edition 64-bit
 * Microsoft SQL Server 2000 Developer Edition

-



BUG #: 474025 (SQL Server 8.0)



SYMPTOMS
Consider the following scenario. In Microsoft SQL Server 2000, char and varchar data types are combined in a UNION operation. If the UNION operation is included in a view or in a subquery, the return data type of the combined column is char. If the UNION operation is a stand-alone query, the return data type of the combined column is varchar.



WORKAROUND
To work around this problem, manually convert the data types from char to varchar by using the cast function or the convert function. For example, use the following SQL statement instead of the SQL statement that is used in the &quot;More Information&quot; section: create view View12 as select cast ([data] as varchar (10)) as data from [Table1] union select [data] from [Table2]



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the &quot;Applies to&quot; section.



Steps to reproduce the behavior
  Create table1 and table2 by using the following SQL statement: CREATE TABLE [Table1] ( [id] [int] NOT NULL, [data] [char] (10))

CREATE TABLE [Table2] ( [id] [int] NOT NULL, [data] [varchar] (10))   Create a view that is called view12 by using the following SQL statement: create view View12 as select [data] from [Table1] union select [data] from [Table2]   Run the following stored procedure: sp_help View12 Notice that the data type of the data column is char. 

Keywords: kbtshoot kbbug kbdatabase kbsql KB897886

-

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

© Microsoft Corporation. All rights reserved.