writetext- how to use?

Question

Post reply

writetext- how to use?

Yuri55

SSCrazy Eights

Points: 8449
More actions
October 20, 2005 at 12:08 pm

#95456

Can somebody, please, explain me how to use Writetext with text data? I certainly know the syntax:from BOL: WRITETEXT { table.column text_ptr } [ WITH LOG ] { data }. But all samples I saw use data as varchar, i.e. length<=8000. How to test with text data, like 32K size? I can't select text from another table, can't assign to local variable. What is the reason for writetext in this case? Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Hello Yuri,

There was a good article here on this site at the end of September:

http://www.sqlservercentral.com/columnists/rVasant/manipulatingtextvariables.asp

Manipulating Text Columns

by Raj Vasant

I saved this article in my Virtual Briefcase, it explains a lot of things with examples

Regards,Yelena Varsha

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 2

Yelena, thanks, but in this article data is 'This is new text-value…', i.e. data is again varchar, not real text (I mean how we can put 32K string here?!). My question is mostly not how to use writetext but how to text data in it.

Anyhow, thanks again

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Yuri,

try DTS with text files.

I had to test-populate some text field, I used DTS with a couple of text files with a lot of text.

Why do you say you can not select 32 K from the text field? You can not select withing Query Analyzer. You can probably select to the front end. Like create a Multi-line text box control in VB.NET or ASP.NET see

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtsksettingwebcontrolpropertiesprogrammatically.asp

Come on, you don't need expensive tools, use Notepad and command-line compiler. Text property is string, should be OK for 32 K

OR use READTEXT with Start_Position parameter to select parts of the text 8K then next 8K etc.

Regards,Yelena Varsha

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 4

OK, make sense- you suggest simply copy from somewhere 32K string and insert into writetext data. Probably this works but frankly I thought about something more elegant like select from another table with text data in place. Thank you

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

Hi,

I had to do a text field update once from 2 text fileds from 2 tables into one text field in the third table. I used a simple update but at some point I received the following error:It said that it could not sort a row more then 8000. In my update query I had to join 2 tables and I used Where as well, so somewhere the sort was conducted by SQL Server, I did not have much time then to experiment with query plan and I new I did not have lots of long records, so I selected all records where combined datalength of both text fields was more then 7999 and it was only 6 records so I excluded them and later updated manually through the application.

I suggest you post your question under T-SQL forum here, you may get more responses

Regards,Yelena Varsha

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 6

Thanks, Yelena. I know how to use updatetext when using text data from another table:updatetext testText1.content1 @ptr1 0 null testText2.content2 @ptr2. But can't do something similar with writetext.

vadba SSChampion Points: 11133 More actions · Answer 7

WRITETEXT overwrites the target column with new data. If the source data is greather than 8000 bytes long, the only way you could use WRITETEXT wuold be to concatenate several expressions together. BOL states that the maximum data that can be written using WRITETEXT is about 120Kb.

DECLARE @tp varbinary(16)

DECLARE @a varchar(8000), @b-2 varchar(8000), @C varchar(8000)

-- Populate @a, @b-2, and @C somehow, and set @tp

...

WRITETEXT yourTable.textColumn @tp @a+@b+@c

Of course, the largest local variable you can declare is varchar(8000). You cannot use local variables of type text, ntext, or image.

If you are copying from another table, just use a regular INSERT or UPDATE statement.

If you are loading from a file, say using the sp_OA* stored procedures, you would use UPDATETEXT in a loop, although you could WRITETEXT to initialize the target column. In reality, its use is somewhat limited.

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 8

Yuri55

SSCrazy Eights

Points: 8449

October 22, 2005 at 9:50 am

#599365

Thanks, I'll try this way

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 9

Unfortunately, syntax you used is incorrect (@a + @b-2), i.e. this version doesn't work either. Thanks

vadba SSChampion Points: 11133 More actions · Answer 10

Yuri,

Sorry about that - you are correct, expressions are not allowed in WRITETEXT. It been quite a while since I've used WRITETEXT, I didn't test that first .

Anyway, that means that the maximum length you can write is 8000 bytes (using Query Analzer). Again, as I said previously, there are only a few uses for WRITETTEXT:

1. The data is guaranteed to be less than or equal to 8000 bytes.

2. You are initializing the column, or you want to erase the data:

WRITETEXT tText.td @tp NULL

3. Possibly used prior to a loop that employs UPDATETEXT.