Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Unanswered: How to append data to ntext field?

    Hi,
    Can anybody tell me how to append data to ntext field?
    Joydeep

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

    -PatP

  3. #3
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Pat Phelan
    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.

    -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

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  5. #5
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by MCrowley
    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/lib.../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

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Unhappy

    Quote Originally Posted by rudra
    Sorry,Nothing found like dividing like /2,just
    http://msdn2.microsoft.com/en-us/lib.../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

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Consider this code:
    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...

  8. #8
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by MCrowley
    Consider this code:
    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

  9. #9
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Well,well,well at last I got it......

    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

  10. #10
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by rudra
    Well,well,well at last I got it......


    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •