+ Reply to Thread
Results 1 to 4 of 4

UPDATETEXT question

  1. UPDATETEXT question

    I have a question on the UPDATETEXT function (SQL 2000)
    The below query works and only updates the record where p.pub_id =
    pr.pub_id.
    I just don't quite understand why only 1 record is updated when the
    UPDATETEXT statement
    does not specify anything except pointer value. Is it no possible for 2
    rows in a table with a text column to have the same pointer value?
    Does this query scan all pr_info in the pub_info table?

    USE pubs
    GO
    EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
    GO
    DECLARE @ptrval binary(16)
    SELECT @ptrval = TEXTPTR(pr_info)
    FROM pub_info pr, publishers p
    WHERE p.pub_id = pr.pub_id
    AND p.pub_name = 'New Moon Books'
    UPDATETEXT pub_info.pr_info @ptrval 88 1 'b'
    GO
    EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
    GO


    Also, what is the importance of 'select into/bulkcopy', 'true' ?

    Thanks




  2. Re: UPDATETEXT question

    Artie (artie2269@yahoo.com) writes:
    > I have a question on the UPDATETEXT function (SQL 2000)
    > The below query works and only updates the record where p.pub_id =
    > pr.pub_id.
    > I just don't quite understand why only 1 record is updated when the
    > UPDATETEXT statement
    > does not specify anything except pointer value. Is it no possible for 2
    > rows in a table with a text column to have the same pointer value?


    No, that is not possible. You get a text pointer for a specific row,
    and then you work with that row.

    In SQL 2005 there are new data types for blobs, varchar(MAX), nvarchar(MAX)
    and varbinary(MAX). You work with these just like you work with regular
    varchar, and no need for the klunky UPDATETEXT. That makes life a lot lot
    easier.



    --
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Books Online for SQL Server 2005 at
    http://www.microsoft.com/technet/pro...ads/books.mspx
    Books Online for SQL Server 2000 at
    http://www.microsoft.com/sql/prodinf...ons/books.mspx

  3. Re: UPDATETEXT question

    Thanks for the info. I am aware of how much easier this is in SQL 2005 but
    am stuck with 2000 in this case.
    One more if I may...In the example syntax from BOL:

    UPDATETEXT { table_name.dest_column_name dest_text_ptr }
    { NULL | insert_offset }
    { NULL | delete_length }
    [ WITH LOG ]
    [ inserted_data
    | { table_name.src_column_name src_text_ptr } ]

    I would like to take an extire TEXT column from a source table and append it
    to a TEXT column in a destination table.
    Do I need to get a start/end textptr from the source?



    "Erland Sommarskog" wrote in message
    news:Xns9A4CF93F5AYazorman@127.0.0.1...
    > Artie (artie2269@yahoo.com) writes:
    >> I have a question on the UPDATETEXT function (SQL 2000)
    >> The below query works and only updates the record where p.pub_id =
    >> pr.pub_id.
    >> I just don't quite understand why only 1 record is updated when the
    >> UPDATETEXT statement
    >> does not specify anything except pointer value. Is it no possible for 2
    >> rows in a table with a text column to have the same pointer value?

    >
    > No, that is not possible. You get a text pointer for a specific row,
    > and then you work with that row.
    >
    > In SQL 2005 there are new data types for blobs, varchar(MAX),
    > nvarchar(MAX)
    > and varbinary(MAX). You work with these just like you work with regular
    > varchar, and no need for the klunky UPDATETEXT. That makes life a lot lot
    > easier.
    >
    >
    >
    > --
    > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    >
    > Books Online for SQL Server 2005 at
    > http://www.microsoft.com/technet/pro...ads/books.mspx
    > Books Online for SQL Server 2000 at
    > http://www.microsoft.com/sql/prodinf...ons/books.mspx




  4. Re: UPDATETEXT question

    Artie (artie2269@yahoo.com) writes:
    > Thanks for the info. I am aware of how much easier this is in SQL 2005
    > but am stuck with 2000 in this case.
    > One more if I may...In the example syntax from BOL:
    >
    > UPDATETEXT { table_name.dest_column_name dest_text_ptr }
    > { NULL | insert_offset }
    > { NULL | delete_length }
    > [ WITH LOG ]
    > [ inserted_data
    > | { table_name.src_column_name src_text_ptr } ]
    >
    > I would like to take an extire TEXT column from a source table and
    > append it to a TEXT column in a destination table. Do I need to get a
    > start/end textptr from the source?


    Yes, you would need a text pointer both for the source and target columns.
    And you can only copy from one row to another at a time.

    --
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Books Online for SQL Server 2005 at
    http://www.microsoft.com/technet/pro...ads/books.mspx
    Books Online for SQL Server 2000 at
    http://www.microsoft.com/sql/prodinf...ons/books.mspx

+ Reply to Thread