473,216 Members | 1,937 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

Updating a text field in SQL Server 2000

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!!

Jul 19 '05 #1
16 22384
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!!

Jul 19 '05 #2
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!!


Jul 19 '05 #3
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.

Jul 19 '05 #4
> 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).
Jul 19 '05 #5
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.

Jul 19 '05 #6
> 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)
Jul 19 '05 #7
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)

Jul 19 '05 #8
> 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.
Jul 19 '05 #9
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.

Jul 19 '05 #10
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.

Jul 19 '05 #11
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.
Jul 19 '05 #12
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.

Jul 19 '05 #13
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.

Jul 19 '05 #14
> 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.
Jul 19 '05 #15
"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
Jul 19 '05 #16
> 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
Jul 19 '05 #17

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Scott Hoff | last post by:
I'm trying to do a simple update like I have done countless times before. However, when I update the empty fields in this table it places a -1 into the field rather than the enterred value. ...
6
by: Hennie de Nooijer | last post by:
Hi, Currently we're a building a metadatadriven datawarehouse in SQL Server 2000. We're investigating the possibility of the updating tables with enormeous number of updates and insert and the...
1
by: ChrisL | last post by:
I am trying to update text fields where the field only has the value "closed." So a field that has closed and anything else will not be updated. update dbo.Hist set dbo.Hist.Notes = '' FROM ...
8
by: threepins | last post by:
Hi, I have a website using a SQL Server database to store all of it's data. Retrieving data and writing basic data back is fine, however, when i go to update one table that has a text field in...
1
by: Chris Johnson | last post by:
Here is my dilema. I have a 120 GB database that I need to mask customer credit card numbers in. The field is a varchar (16). I need to update the field so that we only store the first 4 numbers...
1
by: Harry Devine | last post by:
I have a DataGrid that is configured to use the Edit/Update/Cancel concept correctly. My grid shows values from 5 database fields. I only need to update that last 4 fields. The last field is a...
14
by: el_sid | last post by:
Our developers have experienced a problem with updating Web References in Visual Studio.NET 2003. Normally, when a web service class (.asmx) is created, updating the Web Reference will...
0
by: Melanie | last post by:
Hi - my problem is as follows: I'm writing an ASP.Net 2.0 app with C#. From the app, I call a stored procedure that updates some data in the app's database (SQL Server 2000). This stored procedure...
2
by: =?Utf-8?B?TUNN?= | last post by:
I have an asp.net page that contains an update panel. Within the update panel, controls get added dynamically. During partial page post backs the controls within the panel will change. I have a...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.