how do I edit a < long text > field????

[cheezebeetle]

I have created a db, and now need to modify some data. I tried to access it through a view and directly. Both times the cell reads < long text >. This is because the text is over 900 characters. Well, simply put there is a mispelled name and some bad text. How do I access the text to make the change? Is the only way to write a sql statement to do an update? I would really much rather be able to view the changes I make, since the changes will affect the website it is running.

I see nothing about this in the book I have and nothing about this in the help. It only says you can edit the text by doing blah, blah, blah UNLESS it says < long text >.

UNLESS WHAT? So it does say that, so then what are my options. Help?!

Thanks ahead of time.

 

[DvDvDvDv]

I have the same problem. If anyone knows how to do it, please tell me

Dv

 

[denright]

I am having the same problem. I'm new to SQL Server and can't seem to find an answer for this one. If anyone can help with information about how to edit the content in these fields, I would be most appreciative.

 

[JamesLean]

What version of SQL Server are you using?
What data type is the column you want to edit?

Please give details about your specific situation.

--James

 

[denright]

I'm using SQL Server 2000. The data type is ntext, size 16. I've been modifying data in other fields through the Enterprise Manager interface. However, all the fields with a large amount of text are showing only <long text>. I can't even find the data I'm looking for using a query. I've had to export the data to Excel to find the records I want to edit, but I can't figure out how to edit it within SQL Server.

I'm sure there must be an manageable way to go about this but since I'm new to SQL Server, I'm at a bit of a loss.

One other note on the background of my situation... I am taking over a project that someone else developed and the database is in a sorry state - duplicate records, duplicate tables, no documentation - a real headache. I've been busy trying to identify all the problems and am now at the stage where I really need to get the data cleaned up in order to move forward on cleaning up the database structure.

I really appreciate your assistance.

 

[JamesLean]

Write SQL queries in Query Analyzer rather than using Ent Mgr. For short data you can just use normal UDPATE commands. You may need to use WRITETEXT and UPDATETEXT - look these up in BOL for details.

Remember you can store up to 8000 characters in a varchar field. If you don't need to store more than this then use this data type - it is much easier to work with than text data.

--James

 

[denright]

Thanks, James.

I'll give it a try.

 

[DvDvDvDv]

Hi.

That was my solution:

with queries on data analyzer you can do everything you want for this kind of field.

Here an example:

using a database called Legislacao, I can modify the field Texto from the table tbl_txt:

EXEC sp_dboption 'Legislacao', 'select into/bulkcopy', 'true'
GO
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(Texto)
FROM Tbl_Txt
WHERE Codigo = 52
WRITETEXT Tbl_Txt.Texto @ptrval 'write the new content of the field here'
GO
EXEC sp_dboption 'Legislacao', 'select into/bulkcopy', 'false'
GO

If you want to check what is there, use for instance:

DECLARE @val varbinary(16)
SELECT @val = textptr(Texto) FROM Tbl_Txt
WHERE Codigo = 52
READTEXT Sumario.Conclusoes @val 100 6


Hope it helps

Dv