Updating a text field in SQL Server 2000

[Michael Walton]

I am trying to write some code that inserts or updates a text field in my
SQL Server 2000 database. The SQL statement is created using a submit from
a form, and the text is coming from a <textarea>, and therefore being placed
into the SQL statement via a Request("field"). However, due to limitations
in SQL Server 2000 and text fields, I can not use a simple Update or Insert
command with text over 8000 bytes.

Does anybody have any sample statements that will help me update that field
with larger amounts of data? I have tried using the WRITETEXT function and
UPDATETEXT function with little or no success. Help!!

[Manohar Kamath [MVP]]

Something I found on the net (thanks, google!)

http://www.pstruh.cz/help/scptutl/sa259.htm

You basically convert your text into it's hex equivalent, and use the
Updatetext function.

--
Manohar Kamath
Editor, .netBooks
www.dotnetbooks.com
"Michael Walton" <mw*****@thomasassociates.cc> wrote in message
news:uF**************@TK2MSFTNGP11.phx.gbl...

I am trying to write some code that inserts or updates a text field in my
SQL Server 2000 database. The SQL statement is created using a submit from a form, and the text is coming from a <textarea>, and therefore being placed into the SQL statement via a Request("field"). However, due to limitations in SQL Server 2000 and text fields, I can not use a simple Update or Insert command with text over 8000 bytes.

Does anybody have any sample statements that will help me update that field with larger amounts of data? I have tried using the WRITETEXT function and UPDATETEXT function with little or no success. Help!!

[Michael Walton]

Thanks for that link. There are some ideas in there that I've seen before.
The problem is that they are uploading a large file into a text field (which
actually might be better off in an image field). However, what I am doing
is entering a large amount of text into a form, and submitting the form to
an ASP page. The ASP page then creates a SQL Statement and tries to store
the parameter into a text field. I have tried Updatetext and Writetext with
little success. It appears that it works the first time, but I can't get it
to update something later. :-( I am stuck.
"Manohar Kamath [MVP]" <mk*****@TAKETHISOUTkamath.com> wrote in message
news:%2***************@TK2MSFTNGP11.phx.gbl...

Something I found on the net (thanks, google!)

http://www.pstruh.cz/help/scptutl/sa259.htm

You basically convert your text into it's hex equivalent, and use the
Updatetext function.

--
Manohar Kamath
Editor, .netBooks
www.dotnetbooks.com
"Michael Walton" <mw*****@thomasassociates.cc> wrote in message
news:uF**************@TK2MSFTNGP11.phx.gbl...

I am trying to write some code that inserts or updates a text field in my SQL Server 2000 database. The SQL statement is created using a submit

from

a form, and the text is coming from a <textarea>, and therefore being

placed

into the SQL statement via a Request("field"). However, due to

limitations

in SQL Server 2000 and text fields, I can not use a simple Update or

Insert

command with text over 8000 bytes.

Does anybody have any sample statements that will help me update that

field

with larger amounts of data? I have tried using the WRITETEXT function

and

UPDATETEXT function with little or no success. Help!!

[Foo Man Chew]

However, what I am doing

is entering a large amount of text into a form, and submitting the form to
an ASP page. The ASP page then creates a SQL Statement and tries to store
the parameter into a text field.

Maybe you could show what you are trying and we could suggest an
alternative.

For example, you can't say "UPDATE table SET textColumn = textColumn +
'foo'"

However, it should be no problem to change the entire value at one time that
way.

[Foo Man Chew]

> Maybe you could show what you are trying and we could suggest an

alternative.

And by that I mean the result of response.write (sqlstatement) and the error
you get when you conn.execute (sqlstatement).

[Michael Walton]

I guess my original post didn't specify this well enough, but a simple
UPDATE doesn't work for text fields over 8K. That hurdle I've already
passed. UPDATE, SELECT, and INSERT do not work for text, ntext, or image
fields over 8K in size.

Here is a better explanation, and some code:

strSQL = "EXEC sp_dboption 'cbr-dev', 'select into/bulkcopy', 'true' DECLARE
@ptrval binary(16) SELECT @ptrval = TEXTPTR(content_desc) FROM content WHERE
content_id = " & Request("cid") & " WRITETEXT content.content_desc @ptrval
'" & Replace(Request("content"),"'","''") & "' EXEC sp_dboption 'cbr-dev',
'select into/bulkcopy', 'false'"

Set rsDo = dcnDB.Execute(strSQL)
Request("content") is the field that I am reading from the form that was
submitted. This appears to work for any field lengths under 100K, but I get
an exception error when the text gets larger than that. I believe this
might be a limitation on the size of the parameter from a <textarea> form
field. Can anybody confirm that? If so, any work arounds?

Thanks for your help, in advance.
"Foo Man Chew" <fo*@man.chew> wrote in message
news:OU**************@TK2MSFTNGP10.phx.gbl...

However, what I am doing

is entering a large amount of text into a form, and submitting the form to an ASP page. The ASP page then creates a SQL Statement and tries to store the parameter into a text field.
Maybe you could show what you are trying and we could suggest an
alternative.

For example, you can't say "UPDATE table SET textColumn = textColumn +
'foo'"

However, it should be no problem to change the entire value at one time

that way.

[Foo Man Chew]

> strSQL = "EXEC sp_dboption 'cbr-dev', 'select into/bulkcopy', 'true'
DECLARE

@ptrval binary(16) SELECT @ptrval = TEXTPTR(content_desc) FROM content WHERE content_id = " & Request("cid") & " WRITETEXT content.content_desc @ptrval
'" & Replace(Request("content"),"'","''") & "' EXEC sp_dboption 'cbr-dev',
'select into/bulkcopy', 'false'"

Are you trying to append data to an existing TEXT column, or replace the
entire column? If you are writing the whole value, I'm not sure why you
think you need WRITETEXT.

content = replace(request.form("content", "'", "''"))
sql = "UPDATE content SET content_desc = '" & content & "' WHERE id=" &
clng(request.form("cid"))

It might be that the driver you are using is limiting your SQL statements to
a 64kb batch size. If you are hitting that limitation, try to switch from
using ODBC/DSN to an OLEDB connection string. And an even better
enhancement would be to pass the text to a TEXT input param of a stored
procedure, instead of passing the whole ad hoc query through all the
tiers...

And finally, why are you setting this result to a recordset object? Just:

dcnDB.execute(sql)

[Michael Walton]

Well, once again....SQL Server 2000 does not allow you to UPDATE, INSERT, or
SELECT more than 8K from a text, ntext, or image field. This is straight
from their documentation.

I see what you mean about the recordset...just an oversight on my part.

This was supposed to be a quick project, so I didn't see the need for stored
procedures, but it has gotten more complicated. :-(

Thanks for your help.

"Foo Man Chew" <fo*@man.chew> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

strSQL = "EXEC sp_dboption 'cbr-dev', 'select into/bulkcopy', 'true' DECLARE

@ptrval binary(16) SELECT @ptrval = TEXTPTR(content_desc) FROM content

WHERE

content_id = " & Request("cid") & " WRITETEXT content.content_desc @ptrval '" & Replace(Request("content"),"'","''") & "' EXEC sp_dboption 'cbr-dev', 'select into/bulkcopy', 'false'"

Are you trying to append data to an existing TEXT column, or replace the
entire column? If you are writing the whole value, I'm not sure why you
think you need WRITETEXT.

content = replace(request.form("content", "'", "''"))
sql = "UPDATE content SET content_desc = '" & content & "' WHERE id=" &
clng(request.form("cid"))

It might be that the driver you are using is limiting your SQL statements

to a 64kb batch size. If you are hitting that limitation, try to switch from
using ODBC/DSN to an OLEDB connection string. And an even better
enhancement would be to pass the text to a TEXT input param of a stored
procedure, instead of passing the whole ad hoc query through all the
tiers...

And finally, why are you setting this result to a recordset object? Just:

dcnDB.execute(sql)

[Foo Man Chew]

> Well, once again....SQL Server 2000 does not allow you to UPDATE, INSERT,
or

SELECT more than 8K from a text, ntext, or image field. This is straight
from their documentation.
That's ridiculous. I have admin sections of web sites that spit out 60-70kb
to textareas, and save them, and all works fine.

Query Analyzer shows a maximum of 8,192 characters, and Enterprise Manager
only allows you to edit 1,024 characters. But these are limitations in the
client tools, not SQL Server. Programmatically is a different issue
altogether.

If SQL Server couldn't manage more than 8k, why would they bother including
datatypes that hold that much?
This was supposed to be a quick project, so I didn't see the need for stored procedures, but it has gotten more complicated. :-(

In our company, we are not allowed to use ad hoc queries against the
database. Period. And it has nothing to do with complexity... is your
SELECT or UPDATE statement going to be any more complex just because it
lives in the database rather than the application? Of course not. But it
will be far easier to manage... not to mention more efficient.

[Chris Barber]

If you retrieve a recordset then you can read and write directly to the
field.

However, I have a working site that reads and writes 80-100K of text
(caching XSLT transformed to HTML).

I'll post the code I use a bit later this afternoon.

Chris.

"Foo Man Chew" <fo*@man.chew> wrote in message
news:O4**************@TK2MSFTNGP11.phx.gbl...

Well, once again....SQL Server 2000 does not allow you to UPDATE, INSERT, or SELECT more than 8K from a text, ntext, or image field. This is straight
from their documentation.
That's ridiculous. I have admin sections of web sites that spit out 60-70kb
to textareas, and save them, and all works fine.

Query Analyzer shows a maximum of 8,192 characters, and Enterprise Manager
only allows you to edit 1,024 characters. But these are limitations in the
client tools, not SQL Server. Programmatically is a different issue
altogether.

If SQL Server couldn't manage more than 8k, why would they bother including
datatypes that hold that much?
This was supposed to be a quick project, so I didn't see the need for stored procedures, but it has gotten more complicated. :-(

In our company, we are not allowed to use ad hoc queries against the
database. Period. And it has nothing to do with complexity... is your
SELECT or UPDATE statement going to be any more complex just because it
lives in the database rather than the application? Of course not. But it
will be far easier to manage... not to mention more efficient.

[Michael Walton]

Well, I agree with you completely, and I have switched back to the UPDATE
and it is working. See, sometimes these newsgroups have terrible things in
them. :-) I still get an exception when I go over 100K, though. Is that a
limitation of the textarea, ASP, or the UPDATE function? The error I get
is:

Error Type:
(0x80020009)
Exception occurred.

I love those errors, too. They give you SO much information.
"Foo Man Chew" <fo*@man.chew> wrote in message
news:O4**************@TK2MSFTNGP11.phx.gbl...

Well, once again....SQL Server 2000 does not allow you to UPDATE, INSERT,

or

SELECT more than 8K from a text, ntext, or image field. This is

straight from their documentation.

That's ridiculous. I have admin sections of web sites that spit out

60-70kb to textareas, and save them, and all works fine.

Query Analyzer shows a maximum of 8,192 characters, and Enterprise Manager
only allows you to edit 1,024 characters. But these are limitations in the client tools, not SQL Server. Programmatically is a different issue
altogether.

If SQL Server couldn't manage more than 8k, why would they bother including datatypes that hold that much?

This was supposed to be a quick project, so I didn't see the need for

stored

procedures, but it has gotten more complicated. :-(

In our company, we are not allowed to use ad hoc queries against the
database. Period. And it has nothing to do with complexity... is your
SELECT or UPDATE statement going to be any more complex just because it
lives in the database rather than the application? Of course not. But it
will be far easier to manage... not to mention more efficient.

[Bob Barrows]

http://groups.google.com/groups?hl=e...3DN%26tab%3Dwg

Bob Barrows

Michael Walton wrote:

Well, I agree with you completely, and I have switched back to the
UPDATE and it is working. See, sometimes these newsgroups have
terrible things in them. :-) I still get an exception when I go
over 100K, though. Is that a limitation of the textarea, ASP, or the
UPDATE function? The error I get is:

Error Type:
(0x80020009)
Exception occurred.

I love those errors, too. They give you SO much information.
"Foo Man Chew" <fo*@man.chew> wrote in message
news:O4**************@TK2MSFTNGP11.phx.gbl...

Well, once again....SQL Server 2000 does not allow you to UPDATE,
INSERT, or SELECT more than 8K from a text, ntext, or image field.
This is straight from their documentation.

That's ridiculous. I have admin sections of web sites that spit out
60-70kb to textareas, and save them, and all works fine.

Query Analyzer shows a maximum of 8,192 characters, and Enterprise
Manager only allows you to edit 1,024 characters. But these are
limitations in the client tools, not SQL Server. Programmatically
is a different issue altogether.

If SQL Server couldn't manage more than 8k, why would they bother
including datatypes that hold that much?

This was supposed to be a quick project, so I didn't see the need
for stored procedures, but it has gotten more complicated. :-(

In our company, we are not allowed to use ad hoc queries against the
database. Period. And it has nothing to do with complexity... is
your SELECT or UPDATE statement going to be any more complex just
because it lives in the database rather than the application? Of
course not. But it will be far easier to manage... not to mention
more efficient.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Michael Walton]

Thanks, Bob. That's perfect. I'm going to try an keep my content under
100K, which is probably a good rule for what I'm creating anyway. Thanks
for the help.

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:uk*************@TK2MSFTNGP11.phx.gbl...

http://groups.google.com/groups?hl=e...3DN%26tab%3Dwg
Bob Barrows

Michael Walton wrote:

Well, I agree with you completely, and I have switched back to the
UPDATE and it is working. See, sometimes these newsgroups have
terrible things in them. :-) I still get an exception when I go
over 100K, though. Is that a limitation of the textarea, ASP, or the
UPDATE function? The error I get is:

Error Type:
(0x80020009)
Exception occurred.

I love those errors, too. They give you SO much information.
"Foo Man Chew" <fo*@man.chew> wrote in message
news:O4**************@TK2MSFTNGP11.phx.gbl...

Well, once again....SQL Server 2000 does not allow you to UPDATE,
INSERT, or SELECT more than 8K from a text, ntext, or image field.
This is straight from their documentation.

That's ridiculous. I have admin sections of web sites that spit out
60-70kb to textareas, and save them, and all works fine.

Query Analyzer shows a maximum of 8,192 characters, and Enterprise
Manager only allows you to edit 1,024 characters. But these are
limitations in the client tools, not SQL Server. Programmatically
is a different issue altogether.

If SQL Server couldn't manage more than 8k, why would they bother
including datatypes that hold that much?

This was supposed to be a quick project, so I didn't see the need
for stored procedures, but it has gotten more complicated. :-(

In our company, we are not allowed to use ad hoc queries against the
database. Period. And it has nothing to do with complexity... is
your SELECT or UPDATE statement going to be any more complex just
because it lives in the database rather than the application? Of
course not. But it will be far easier to manage... not to mention
more efficient.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Google User]

Hi,

We used several mbs of data to update the text/image column in ASP. If
you don't want to use the stored procedure then you can use the
recordset object to update the image/text column. Here is the
highlevel syntax:

rs.open "Select mytextcol from tbl where id=2"
if Not (rs.eof and rs.bof)
rs("mytextcol")=request("content")
rs.update
end if
rs.close

if the above does not work then try
len1=length(request("content"))
loop for each 8000 characters
rs("mytextcol").AppendChunk(var8000)
end loop

I hope this helps.
"Michael Walton" <mw*****@thomasassociates.cc> wrote in message news:<u3**************@TK2MSFTNGP11.phx.gbl>...

I guess my original post didn't specify this well enough, but a simple
UPDATE doesn't work for text fields over 8K. That hurdle I've already
passed. UPDATE, SELECT, and INSERT do not work for text, ntext, or image
fields over 8K in size.

Here is a better explanation, and some code:

strSQL = "EXEC sp_dboption 'cbr-dev', 'select into/bulkcopy', 'true' DECLARE
@ptrval binary(16) SELECT @ptrval = TEXTPTR(content_desc) FROM content WHERE
content_id = " & Request("cid") & " WRITETEXT content.content_desc @ptrval
'" & Replace(Request("content"),"'","''") & "' EXEC sp_dboption 'cbr-dev',
'select into/bulkcopy', 'false'"

Set rsDo = dcnDB.Execute(strSQL)
Request("content") is the field that I am reading from the form that was
submitted. This appears to work for any field lengths under 100K, but I get
an exception error when the text gets larger than that. I believe this
might be a limitation on the size of the parameter from a <textarea> form
field. Can anybody confirm that? If so, any work arounds?

Thanks for your help, in advance.
"Foo Man Chew" <fo*@man.chew> wrote in message
news:OU**************@TK2MSFTNGP10.phx.gbl...

However, what I am doing

is entering a large amount of text into a form, and submitting the form to an ASP page. The ASP page then creates a SQL Statement and tries to store the parameter into a text field.

Maybe you could show what you are trying and we could suggest an
alternative.

For example, you can't say "UPDATE table SET textColumn = textColumn +
'foo'"

However, it should be no problem to change the entire value at one time

that

way.

[Foo Man Chew]

> If you retrieve a recordset then you can read and write directly to the

field.

You can, but shouldn't. Writing back to a recordset will require a very
expensive cursor/lock type.

[Koutetsu]

"Foo Man Chew" <fo*@man.chew> wrote in message news:<O4**************@TK2MSFTNGP11.phx.gbl>...

Well, once again....SQL Server 2000 does not allow you to UPDATE, INSERT,

or

SELECT more than 8K from a text, ntext, or image field. This is straight
from their documentation.

That's ridiculous. I have admin sections of web sites that spit out 60-70kb
to textareas, and save them, and all works fine.

Query Analyzer shows a maximum of 8,192 characters, and Enterprise Manager
only allows you to edit 1,024 characters. But these are limitations in the
client tools, not SQL Server. Programmatically is a different issue
altogether.

If SQL Server couldn't manage more than 8k, why would they bother including
datatypes that hold that much?

I don't know a whole lot about the internals of SQL Server, but I'd
guess that it has something to do with the high cost of indexing large
fields, particularly of nonuniform length. (This, possibly among
other reasons, is why CHAR fields are preferable to VARCHARs whenever
possible--they're cheaper to seek and index.) You'll note that when
you create a TEXT, NTEXT or IMAGE field with the Design Table tool in
Enterprise Manager, the length of the fields is always 16 bytes, which
is just enough to store a (huge) pointer to the real data. To use a
programming analogy, most fields are stored by value, but BLOB fields
are stored by reference--the actual data is kept elsewhere, presumably
in an area that doesn't need to be regularly indexed or searched
through.

The ability to insert and update BLOB fields with regular queries is
most definitely due to a hack performed by the development team at MS.
There are still problems, however. Depending on the limitations of
the API and database driver used to connect to the server, large
fields might not even arrive at the database intact. Not to mention
possible limitations of the T-SQL interpreter. Even ADO, which is
designed to be about as easy and foolproof as you can get, heavily
encourages incremental writing of BLOB fields. You need to use the
Field.ReadChunk and Field.AppendChunk methods to manipulate BLOB data.
I'm not sure if Field.Value even returns anything for BLOBs.

Just my two cents. Unless anything I've said is actually useful; then
make it three cents.

Koutetsu

[Aaron Bertrand - MVP]

> encourages incremental writing of BLOB fields. You need to use the

Field.ReadChunk and Field.AppendChunk methods to manipulate BLOB data.

I've never had to use these methods for reading/writing TEXT/NTEXT data.

A