Article ID: 196935
Article Last Modified on 3/14/2005
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 2000 Standard Edition
This article was previously published under Q196935
BUG #: 52161 (SQLBUG_70)
BUG #: 52161 (SHILOH_BUG)
SYMPTOMS
If you attempt to use an UPDATETEXT statement to convert TEXT to NTEXT (the UNICODE equivalent to TEXT) or vise versa, you will receive the following error:
WORKAROUND
To work around this problem, perform an indirect conversion of TEXT, NTEXT or IMAGE data, as in the following example:
-- this example assumes that the table t1 has been created and populated USE pubs GO SELECT CAST( CAST( c2 AS VARCHAR(10) ) AS text ) FROM t1 GO
Results: ------------------------------ test_text (1 row(s) affected)
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
MORE INFORMATION
The conversion of TEXT, NTEXT, or IMAGE data types to another data type of these is not supported, implicitly or explicitly. You can explicitly convert TEXT data to CHAR or VARCHAR, and IMAGE data to BINARY or VARBINARY. You can explicitly convert NTEXT data to NCHAR or NVARCHAR; however, the maximum length is 4,000 characters. Implicit conversion is not supported. For more information, see the remarks and conversion table in the "CAST and CONVERT (T-SQL)" topic in the SQL Server 7.0 Books Online. The following SQL code demonstrates this problem:
use pubs go sp_dboption 'pubs', 'select into/bulkcopy', 'true' go if object_id('t1','U') is not null drop table t1 go create table t1(c1 ntext,c2 text) go insert t1 values('test_ntext','test_text') go select * from t1 go declare @p1 binary(16),@p2 binary(16) select @p1 = textptr(c1), @p2 = textptr(c2) from t1 updatetext t1.c1 @p1 2 0 t1.c2 @p2 go
The preceding code results in the following error message:
Or if you use the following:
declare @p1 binary(16),@p2 binary(16) select @p1 = textptr(c1), @p2 = textptr(c2) from t1 updatetext t1.c2 @p2 2 0 t1.c1 @p1 go
You get the following error message:
Additional query words: BLOB CAST datatype datatypes
Keywords: kbbug kbpending KB196935