MS SQL Server - SQL Server 2000 and textareas

Author

SQL Server 2000 and textareas

govus

2006-12-12, 7:12 pm

Hi all-
I have an issue where I'm trying to save the value of a textarea from a
web page into an MS SQL 2000 database using Java and the JDBC driver
1.1. I get the following exception:

com.microsoft.jdbc.base. BaseBatchUpdateExcep
tion: [Microsoft][SQLServer
2000 Driver for JDBC]& #91;SQLServer]Cannot
create a row of size 8111 which
is greater than the allowable maximum of 8060.

So naturally, I pulled back the max character length of the field from
8,000, but I still have the same problem. Every HTTP request/response
is encoded using UTF-8, so I tried switching from varchar to nvarchar,
but still no dice. I have tried nearly every combination of field
character length, column size, column type, etc. that I can think of,
but with no luck.

The only thing that seems to work is if I set the character length
limit (on the textarea w/ JavaScript) way below the max column size,
but even then if the textarea includes a lot of line breaks the problem
still crops up (maybe still an encoding issue?) Any thoughts? I'm
stumped...

Thanks in advance to anyone who answers....

Tech stuff:
Servlet container: Tomcat 5.5.20
SQL Server: SQL 2000 sp4 (not exactly sure what Windows OS)
JDBC driver: Microsoft SQL Server 2005 JDBC Driver 1.1
Java: J2SE 5.0_09/J2EE 1.4
Am I forgetting anything?
P.S. - I've already seen http://support.microsoft.com/kb/232580; that
was my first stop and I really don't want to implement option 2 unless
I have to...

John Bell

2006-12-13, 5:18 am

Hi

This is not an issue with the column on it's own, but the total size of all
the columns in the row. The maximum number of bytes per row is 8060, see
http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx therefore if
you have a single varchar(8000) column, the size of the other columns can
only be 60 bytes in total. To overcome this you may want to split the table
into two, and use a view so that you don't need to change your existing code.
Look at using an INSTEAD OF trigger if you have problems with DML.

You may want to post the DDL see http://www.aspfaq.com/etiquette.asp?id=5006
A different approach may be to use a TEXT/NTEXT column.

John

"govus" wrote:

> Hi all-
> I have an issue where I'm trying to save the value of a textarea from a
> web page into an MS SQL 2000 database using Java and the JDBC driver
> 1.1. I get the following exception:
>
> com.microsoft.jdbc.base. BaseBatchUpdateExcep
tion: [Microsoft][SQLServer
> 2000 Driver for JDBC]& #91;SQLServer]Cannot
create a row of size 8111 which
> is greater than the allowable maximum of 8060.
>
> So naturally, I pulled back the max character length of the field from
> 8,000, but I still have the same problem. Every HTTP request/response
> is encoded using UTF-8, so I tried switching from varchar to nvarchar,
> but still no dice. I have tried nearly every combination of field
> character length, column size, column type, etc. that I can think of,
> but with no luck.
>
> The only thing that seems to work is if I set the character length
> limit (on the textarea w/ JavaScript) way below the max column size,
> but even then if the textarea includes a lot of line breaks the problem
> still crops up (maybe still an encoding issue?) Any thoughts? I'm
> stumped...
>
> Thanks in advance to anyone who answers....
>
> Tech stuff:
> Servlet container: Tomcat 5.5.20
> SQL Server: SQL 2000 sp4 (not exactly sure what Windows OS)
> JDBC driver: Microsoft SQL Server 2005 JDBC Driver 1.1
> Java: J2SE 5.0_09/J2EE 1.4
> Am I forgetting anything?
> P.S. - I've already seen http://support.microsoft.com/kb/232580; that
> was my first stop and I really don't want to implement option 2 unless
> I have to...
>
>

govus

2006-12-13, 7:12 pm

That's what I was looking for, thanks!

Sponsored Links