nText in Triggers

17 Feb 2006 7:06 PM

Steph

I want to do something like this

Select @MyText = LongDescription
From MyTable
Where id = inserted.[ID]

I get an error message telling me that nText is invalid for a local variable
how can I do this?

17 Feb 2006 7:24 PM

Aaron Bertrand [SQL Server MVP]

You can't use nText in a local variable.

You ask "how can I do this?"

Well, how can you do what? Can you describe exactly what you are trying to
do (and I mean, beyond, trying to store nText in a local variable).

Show quote

"Steph" <microsoft.public.access.formscoding> wrote in message
news:uCXEHV$MGHA.2628@TK2MSFTNGP15.phx.gbl...
>I want to do something like this
>
> Select @MyText = LongDescription
> From MyTable
> Where id = inserted.[ID]
>
> I get an error message telling me that nText is invalid for a local
> variable
> how can I do this?
>
>

17 Feb 2006 7:33 PM

Steph

Table1.Descr1 = nText
Table2.Descr2 = nText

When an insert is done in Table1
I want the trigger to insert a new record in Table2.

So @NewText = 'some text... depending of several IF '
@NewText = @NewText + char(13) + char(13) + Table1.Descr1

This is what I want to do :
Insert into Table2 value @NewText which can be bigger than 255 char

Show quote

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> a écrit dans le
message de news:eNTZMd$MGHA.720@TK2MSFTNGP14.phx.gbl...
> You can't use nText in a local variable.
>
> You ask "how can I do this?"
>
> Well, how can you do what? Can you describe exactly what you are trying
to
> do (and I mean, beyond, trying to store nText in a local variable).
>
>
>
>
> "Steph" <microsoft.public.access.formscoding> wrote in message
> news:uCXEHV$MGHA.2628@TK2MSFTNGP15.phx.gbl...
> >I want to do something like this
> >
> > Select @MyText = LongDescription
> > From MyTable
> > Where id = inserted.[ID]
> >
> > I get an error message telling me that nText is invalid for a local
> > variable
> > how can I do this?
> >
> >
>
>

19 Feb 2006 4:03 PM

Erland Sommarskog

Steph (microsoft.public.access.formscoding) writes:

> Table1.Descr1 = nText
> Table2.Descr2 = nText
>
> When an insert is done in Table1
> I want the trigger to insert a new record in Table2.
>
> So @NewText = 'some text... depending of several IF '
> @NewText = @NewText + char(13) + char(13) + Table1.Descr1
>
> This is what I want to do :
> Insert into Table2 value @NewText which can be bigger than 255 char

255? That was the upper limit for carchar in SQL 6.5. Since you say
ntext you are obviously on a later version, and an nvarchar can be
4000 characters long.

If you are on SQL 2005, use nvarchar(MAX) ratrher than ntext, there are
no limitations with nvarchar(MAX).

If you are on SQL 7/2000 and your data is more than 4000 chars long,
you will have to use WRITETEXT and UPDATETEXT. It will not be particularly
funny.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx