How to append data to ntext field?

Hi,
Can anybody tell me how to append data to ntext field?
JoydeepOn the client side if that is one of your choices!

If you absolutely MUST do it within SQL Server, the first step is to re-evalute the requirement ;) If you are convinced that the pain is worth it, then you need to pursue UPDATETEXT (http://msdn2.microsoft.com/en-us/library/ms189466.aspx).

-PatP|||On the client side if that is one of your choices!

If you absolutely MUST do it within SQL Server, the first step is to re-evalute the requirement ;) If you are convinced that the pain is worth it, then you need to pursue UPDATETEXT (http://msdn2.microsoft.com/en-us/library/ms189466.aspx).

-PatP
Hi PatP,
I already tried the above one,but can't make out thats why I put it in the forum.I am trying it in Sql Server.
The Updatetext syntax required the insert_off parameter.How can I find out the length of a ntext field?
Be sure that I want to append text so I need the total no of characters after which this piece of text will be inserted.
Plz comment.
Joydeep|||Check out the datalength() function in BOL. I forget if you have to divide by 2 for ntext, and am too lazy to look it up, now.|||Check out the datalength() function in BOL. I forget if you have to divide by 2 for ntext, and am too lazy to look it up, now.
Sorry,Nothing found like dividing like /2,just
http://msdn2.microsoft.com/en-us/library(d=robot)/ms176068.aspx

I was testing this one..

use test
declare
@.pp binary(16),
@.txt numeric

set @.txt=(select datalength(info) from table1)

set @.pp=(select textptr(info) from table1)

updatetext table1.info @.pp @.txt 0 'asljdklsadjKLKLSDJLJAdlkdjlKAJKLSJDKLAS
asjDHjkhkjlJHSDJKASHDJKASHDJKhkjasdhKJHsfajdfkljas dkljflksdjflkj
asdfjkdlaskjfklajsdfkljsdafkljsldkfjsdkljfklj
sdfjal;dasjkfkljalsdkfjldksjfklsdjflkjlkdsjfkjfkls djfklsdfjlskfjskladf JOYDEEP'

getting an error as ...

Server: Msg 7116, Level 16, State 4, Line 10
Offset 1091077014 is not in the range of available text, ntext, or image data.
The statement has been terminated.

??:(
Thanks anyway..
Joydeep|||Sorry,Nothing found like dividing like /2,just
http://msdn2.microsoft.com/en-us/library(d=robot)/ms176068.aspx

I was testing this one..

getting an error as ...

??:(
Thanks anyway..
Joydeep

Again if I change the datatype to Text this is working fine

use test
create table tom
(info text)
insert into tom values('Starting Text')

use test
declare
@.pp binary(16),
@.txt int
select @.pp=textptr(info) from tom
select @.txt=datalength(info) from tom
updatetext tom.info @.pp @.txt 0 'joydeep'
select * from tom

Result..

info Starting Textjoydeep

(1 row(s) affected)

Plz comment...
Joydeep|||Consider this code:

use pubs
go
create table test1
(col1 text, col2 ntext)

insert into test1
values ('hello', 'hello')

select datalength(col1), datalength(col2)
from test1

drop table test1

The answer should jump at you...|||Consider this code:

use pubs
go
create table test1
(col1 text, col2 ntext)

insert into test1
values ('hello', 'hello')

select datalength(col1), datalength(col2)
from test1

drop table test1

The answer should jump at you...

Well,You are absolutely right,but same problem with the statement.I am getting the same error..

use test

create table table1(info ntext)
insert into table1 values('First string')
declare
@.pp binary(16),
@.txt numeric

set @.txt=(select datalength(info) from table1)
set @.txt=@.txt/2
print @.txt
set @.pp=(select textptr(info) from table1)

updatetext table1.info @.pp @.txt 0 'JOYDEEP'
go
select * from table1
drop table table1


error ...

(1 row(s) affected)

12
Server: Msg 7116, Level 16, State 4, Line 14
Offset 1087931286 is not in the range of available text, ntext, or image data.
The statement has been terminated.
info
----------------------------------------------------------------------------
First string

(1 row(s) affected)

Thanks
Joydeep|||Well,well,well at last I got it.....:D :D

use test
create table gogo(col1 ntext)
insert into gogo values('first string')

Declare @.pointer binary(16),@.txtlen int
select @.pointer=textptr(col1) from gogo
select @.txtlen=datalength(col1) from gogo
set @.txtlen=@.txtlen/2
updatetext gogo.col1 @.pointer @.txtlen 0 'JoydeepDattagfhgfhfghgfdhgdfhdfhfggfhgfhgfhgfhcxn bnvn
fghgfhdfdfh892739281837`2jihqwjkhdkhjh$&^%&^%*%^*&%^*&%^&^%jghjkhakdhaskdhsk'

go
select * from gogo
go
drop table gogo

;) testing is the best solution
Thanks!!
Joydeep|||Well,well,well at last I got it.....:D :D

;) testing is the best solution
Thanks!!
Joydeep

I just changed the datatype of the variable from numeric to int.That solved the problem.I mean the variable that was keeping the length.:D :p