nText update problem 1

[simonchristieis]

I want to update text in a temp table and concatenate a string:

declare	@tData	table
(
	iDataID		int	identity(1,1),
	cText	 	nText,
	cTotalText  nText
)

Insert into @tData(cText)Values('bob')

Update @tData set cTotalText = cText + 'harry'

I get this error:

The data types ntext and varchar are incompatible in the add operator.

I cant use varchar as the fields may get quite big, and can't use ntext as a local variable type

Can anyone help

Thanks in advance

 

[gmmastros]

Ff you are using sql2005, try using nvarchar(max) as the data type.

declare    @tData    table
(
    iDataID        int    identity(1,1),
    cText         [!]nvarchar(max)[/!],
    cTotalText  [!]nvarchar(max)[/!]
)

Insert into @tData(cText)Values('bob')

Update @tData set cTotalText = cText + 'harry'

-George

"the screen with the little boxes in the window." - Moron

 

[simonchristieis]

How many characters does the nvarchar(max) hold ?

I thought the limit was 8000.

 

[gmmastros]

nvarchar(max) is NOT the same as nText. It is a new data type added to SQL Server 2005. The limit for nvarchar(max) is 2 gigabytes of data.

-George

"the screen with the little boxes in the window." - Moron

 

[gmmastros]

Wait. Let me clarify. nvarchar is limited to 8000 bytes of data. Since it is unicode and stores 2 bytes per character, the most you can store is 4000 characters.

nvarchar(max) is very similar to ntext. Both can store up to 2 gigabytes of data, but nvarchar(max) will operate with all of the string functions whereas ntext is limited to just a few of them.



-George

"the screen with the little boxes in the window." - Moron

 

[simonchristieis]

That's 2 stars from me today - thanks