BUG: Cannot Convert Between TEXT and NTEXT by Using UPDATETEXT

ID: Q196935


The information in this article applies to:

BUG #: 52161 (SQLBUG_70)

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:

Server: Msg 518, Level 16, State 1, Line 3
Cannot convert data type text to ntext (or ntext to text)
The statement has been terminated.


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 this to be a problem in SQL Server version 7.0.


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:
Server: Msg 518, Level 16, State 1, Line 3
Cannot convert data type text to ntext.
The statement has been terminated.

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:
Server: Msg 518, Level 16, State 1, Line 3
Cannot convert data type ntext to text.
The statement has been terminated.

Additional query words: BLOB CAST datatype datatypes


Keywords          : SSrvTran_SQL kbbug7.00 
Version           : WINNT:7.0
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: April 20, 1999