"Don Miller" <nospam@nospam.com> wrote in message
news:OvB%23GSztGHA.2160@TK2MSFTNGP04.phx.gbl...
>I guess I'm missing the trick to append characters (varchar) to an existing
> text column.
>
> I want to do something like this:
>
> UPDATE Notes
> SET NoteText = NoteText + '******' + @varchar
> WHERE NoteID = 99999
>
> And I keep getting "Invalid operator for data type. Operator equals add,
> type equals text."
>
> So, how can you append more text to an existing column? Thanks.
>
>

"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:%23J9uHfztGHA.324@TK2MSFTNGP06.phx.gbl...
> How is the NoteText column defined?
> How is the @varchar variable defined (and you really didn't call it
> 'varchar', did you?)
> What version are you using?
>
> You cannot update a column of type text using concatenation. You can only
> use UPDATE to change a NULL text column to one with a value in it. To
modify
> the contents of a text column, use UPDATETEXT.
>
> Do you really need text or would varchar work for you?
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
>
> "Don Miller" <nospam@nospam.com> wrote in message
> news:OvB%23GSztGHA.2160@TK2MSFTNGP04.phx.gbl...
> >I guess I'm missing the trick to append characters (varchar) to an
existing
> > text column.
> >
> > I want to do something like this:
> >
> > UPDATE Notes
> > SET NoteText = NoteText + '******' + @varchar
> > WHERE NoteID = 99999
> >
> > And I keep getting "Invalid operator for data type. Operator equals add,
> > type equals text."
> >
> > So, how can you append more text to an existing column? Thanks.
> >
> >
>
>

"Don Miller" <nospam@nospam.com> wrote in message
news:OTiIvxztGHA.4848@TK2MSFTNGP02.phx.gbl...
> NoteText is 'text' datatype, the parameter @varchar was just a name to
> imply
> it was some varchar parameter.
>
> Using SQL Server 2000 SP4.
>
> I am using 'text' because notes could be a few hundred characters to
> several
> thousand over 4-5 pages. I never know what people put in and I don't want
> to
> set a limit of any kind.
>
> I found UPDATETEXT and came up with something like this: (that seems to
> work, although seemed comparatively slow)
>
> CREATE PROCEDURE dbo.wr_NotesMarkAsRead
> @noteid int,
> @label varchar(50),
> @datetime varchar(50)
> AS
> BEGIN
> DECLARE @ptrval binary(16), @datalength int
> SELECT @ptrval = TEXTPTR(NoteText), @datalength = DATALENGTH(NoteText)
> FROM Notes
> WHERE NoteID = @noteid
>
> DECLARE @newText varchar(300)
> SET @newText =
>  '--------------------------------------------------------------' +
>  '   The text above was read on ' + @datetime +
>  '   by ' + @label +
>  '--------------------------------------------------------------'
>
> UPDATETEXT Notes.NoteText @ptrval @datalength 0 @newText
> END
> GO
>
>
>
> "Kalen Delaney" <replies@public_newsgroups.com> wrote in message
> news:%23J9uHfztGHA.324@TK2MSFTNGP06.phx.gbl...
>> How is the NoteText column defined?
>> How is the @varchar variable defined (and you really didn't call it
>> 'varchar', did you?)
>> What version are you using?
>>
>> You cannot update a column of type text using concatenation. You can only
>> use UPDATE to change a NULL text column to one with a value in it. To
> modify
>> the contents of a text column, use UPDATETEXT.
>>
>> Do you really need text or would varchar work for you?
>>
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>>
>> "Don Miller" <nospam@nospam.com> wrote in message
>> news:OvB%23GSztGHA.2160@TK2MSFTNGP04.phx.gbl...
>> >I guess I'm missing the trick to append characters (varchar) to an
> existing
>> > text column.
>> >
>> > I want to do something like this:
>> >
>> > UPDATE Notes
>> > SET NoteText = NoteText + '******' + @varchar
>> > WHERE NoteID = 99999
>> >
>> > And I keep getting "Invalid operator for data type. Operator equals
>> > add,
>> > type equals text."
>> >
>> > So, how can you append more text to an existing column? Thanks.
>> >
>> >
>>
>>
>
>

"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:uRc6s2ztGHA.5056@TK2MSFTNGP06.phx.gbl...
> Hi Don
>
> Yes, working with text data is slower than working with varchar.
> You could try the 'text in row' table option, which might make these
updates
> a bit faster for data that is on the short side.
>
> In SQL 2005, you'll have varchar(MAX) and you'll be able to use
> concatenation for all sizes of data.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
>
> "Don Miller" <nospam@nospam.com> wrote in message
> news:OTiIvxztGHA.4848@TK2MSFTNGP02.phx.gbl...
> > NoteText is 'text' datatype, the parameter @varchar was just a name to
> > imply
> > it was some varchar parameter.
> >
> > Using SQL Server 2000 SP4.
> >
> > I am using 'text' because notes could be a few hundred characters to
> > several
> > thousand over 4-5 pages. I never know what people put in and I don't
want
> > to
> > set a limit of any kind.
> >
> > I found UPDATETEXT and came up with something like this: (that seems to
> > work, although seemed comparatively slow)
> >
> > CREATE PROCEDURE dbo.wr_NotesMarkAsRead
> > @noteid int,
> > @label varchar(50),
> > @datetime varchar(50)
> > AS
> > BEGIN
> > DECLARE @ptrval binary(16), @datalength int
> > SELECT @ptrval = TEXTPTR(NoteText), @datalength = DATALENGTH(NoteText)
> > FROM Notes
> > WHERE NoteID = @noteid
> >
> > DECLARE @newText varchar(300)
> > SET @newText =
> >  '--------------------------------------------------------------' +
> >  '   The text above was read on ' + @datetime +
> >  '   by ' + @label +
> >  '--------------------------------------------------------------'
> >
> > UPDATETEXT Notes.NoteText @ptrval @datalength 0 @newText
> > END
> > GO
> >
> >
> >
> > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message
> > news:%23J9uHfztGHA.324@TK2MSFTNGP06.phx.gbl...
> >> How is the NoteText column defined?
> >> How is the @varchar variable defined (and you really didn't call it
> >> 'varchar', did you?)
> >> What version are you using?
> >>
> >> You cannot update a column of type text using concatenation. You can
only
> >> use UPDATE to change a NULL text column to one with a value in it. To
> > modify
> >> the contents of a text column, use UPDATETEXT.
> >>
> >> Do you really need text or would varchar work for you?
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>
> >>
> >> "Don Miller" <nospam@nospam.com> wrote in message
> >> news:OvB%23GSztGHA.2160@TK2MSFTNGP04.phx.gbl...
> >> >I guess I'm missing the trick to append characters (varchar) to an
> > existing
> >> > text column.
> >> >
> >> > I want to do something like this:
> >> >
> >> > UPDATE Notes
> >> > SET NoteText = NoteText + '******' + @varchar
> >> > WHERE NoteID = 99999
> >> >
> >> > And I keep getting "Invalid operator for data type. Operator equals
> >> > add,
> >> > type equals text."
> >> >
> >> > So, how can you append more text to an existing column? Thanks.
> >> >
> >> >
> >>
> >>
> >
> >
>
>