How to retrieve and edit text placed in ntext field in MSSQL ????????????? please help

How to retrieve and edit text placed in ntext field in MSSQL ?????????????  please help

I m using MSSQL 2000 SP3 and only what I can see in when i retrieve all data from table in SQL enterpirise manager is " <long text> how to retrive content from that field and change it...... PLease help me??????
dm98101Asked:
Who is Participating?
 
imran_fastConnect With a Mentor Commented:
don't wait use this code which aneesh suggested and it will work ommit the create table statement

DECLARE @txtPtr Varbinary(16)

SELECT @txtPtr = TEXTPTR(SampleData)
FROM log_sampladata
WHERE yourCriteria to fetch the record

--note the initial length
SELECT DATALENGTH(SampleData)
FROM log_sampladata
WHERE yourCriteria to fetch the record

UPDATETEXT log_sampladata.SampleData @txtPtr NULL 0 ' bbbb '

--check the length to c whether updated
SELECT DATALENGTH(SampleData)
FROM log_sampladata
WHEREyourCriteria to fetch the record

readtext  log_sampladata.SampleData @txtPtr 1 0
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
In order to read, use readtext

USE pubs
GO
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info)
   FROM pub_info pr INNER JOIN publishers p
      ON pr.pub_id = p.pub_id
      AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @ptrval 1 25
GO




Use WriteText or UPDATETEXT to Edit the text fields  refer BOL for more details





This example puts the text pointer into the local variable @ptrval, and then WRITETEXT places the new text string into the row pointed to by @ptrval.

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'
WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten publication. With the latest publication this makes NMB the hottest new publisher of the year!'
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
GO

0
 
dm98101Author Commented:
how to write that code on this:

table name is:                                                                         log_sampladata
text field name I want to edit and change data is named:             SampleData      
Type of field SampleData is:                                                     ntext  (16)



Please help me¨.......          
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Aneesh RetnakaranDatabase AdministratorCommented:
--drop table log_sampladata

CREATE TABLE log_sampladata (i int identity(1,1), SampleData text)
INSERT INTO log_sampladata SELECT REPLICATE('a',32)

DECLARE @txtPtr Varbinary(16)

SELECT @txtPtr = TEXTPTR(SampleData)
FROM log_sampladata
WHERE i = 1


--note the initial length
SELECT DATALENGTH(SampleData)
FROM log_sampladata
WHERE i =1

UPDATETEXT log_sampladata.SampleData @txtPtr NULL 0 ' bbbb '

--check the length to c whether updated
SELECT DATALENGTH(SampleData)
FROM log_sampladata
WHERE i =1

readtext  log_sampladata.SampleData @txtPtr 1 0

0
 
dm98101Author Commented:
If I drop it I ll loose data right?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
yes of course, That code is a sample , i want to check whether it is working or not, for that only i put it...
0
 
dm98101Author Commented:
ok i wait
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.