Row size increase....

Hi,
I have large test data. I have face problem with row size greater than 8060
then it give me warning/error. If i want to store in row greater than 8060
then how to do? Is it any possible way? I try with ntext and sp_tableoption
but it gives me upto 256.
And i read in theory BLOB and all but how to use that?
Thanks in advance.
MilindHi
You may want to read
http://msdn.microsoft.com/library/d...r />
_8orl.asp
http://msdn.microsoft.com/library/d...r />
_6zec.asp
There is alot of other information regarding ntext/image in Books online.
John
"Milind" wrote:

> Hi,
> I have large test data. I have face problem with row size greater than 806
0
> then it give me warning/error. If i want to store in row greater than 8060
> then how to do? Is it any possible way? I try with ntext and sp_tableoptio
n
> but it gives me upto 256.
> And i read in theory BLOB and all but how to use that?
> Thanks in advance.
> Milind|||"Milind" schrieb:
> Hi,
> I have large test data. I have face problem with row size greater than 806
0
> then it give me warning/error. If i want to store in row greater than 8060
> then how to do? Is it any possible way? I try with ntext and sp_tableoptio
n
> but it gives me upto 256.
> And i read in theory BLOB and all but how to use that?
> Thanks in advance.
> Milind
There is no way to store more than 8060 bytes in a record (you cannot exceed
the page size with a record). Split the data and spread them to several
tables, connected by a common primary key!
The only exception to that rule are the blobs (images and texts) as they are
stored in a different location and the record contains only a pointer to tha
t
location. A single text field can contain up to 2 GB of text, but requires
only 4 Bytes in your record. But beware: it is not as easy to handle these
fields later (indexing, searching etc.) as it is with 'normal' data fields.|||If your table has one or more varchar columns resulting in a record layout
with a maximum possible size > 8060, then you can ignore the warning so long
as you don't actually submit an insert or update that would store more than
that maximum.
"Milind" <Milind@.discussions.microsoft.com> wrote in message
news:86D1FA64-C1FB-4781-AE05-FB910464A2E2@.microsoft.com...
> Hi,
> I have large test data. I have face problem with row size greater than
8060
> then it give me warning/error. If i want to store in row greater than 8060
> then how to do? Is it any possible way? I try with ntext and
sp_tableoption
> but it gives me upto 256.
> And i read in theory BLOB and all but how to use that?
> Thanks in advance.
> Milind

Row size in SQL2K

I was trying to create the table below with Query Analyzer and got this warning:
Warning: The table 'PREPARACAO_ATIVIDADE' has been created but its maximum row size (16432) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Is there a way to increase the default maximum row size of 8060 in SQL server 2000?
Thanks for your help,
Andr=E9 Andraus Filho
CREATE TABLE [dbo].[PREPARACAO_ATIVIDADE] (
[COD_PREPARACAO] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[CODIGO_USUARIO] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[COD_TIP_OBJ] [decimal](18, 0) NOT NULL ,
[COD_UA] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[COD_FP] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[COD_ATIVIDADE] [decimal](5, 0) NOT NULL ,
[COD_SUBNIVEL_ATIV] [decimal](5, 0) NOT NULL ,
[COD_PLANO] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[DATINI_ABRAG] [datetime] NOT NULL ,
[DATFIM_ABRAG] [datetime] NOT NULL ,
[CUSTO_PREV_TRANSP] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[DATINI_PREP_ATIV] [datetime] NULL ,
[DATINI_PREVISAO] [datetime] NULL ,
[CUSTO_PREV_DIARIA] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[COMENTARIO_DIRETOR] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[DATFIM_PREVISAO] [datetime] NULL ,
[CENTRO_RESPONSAVEL] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[SIT_DIR_CENTRO] [decimal](1, 0) NULL ,
[JUSTIF_PONTO] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[SIT_DCI_G] [decimal](1, 0) NULL ,
[COD_CENTRO_RESP_ATIV] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[COD_CONSIG] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[OUTROS] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ) ON [PRIMARY]
GOYou can't make it larger than 8060 bytes but you can change some of the
varchar(8000)'t to Text datatypes.
--
Andrew J. Kelly
SQL Server MVP
"André Andraus Filho" <aafandraus@.fazenda.sp.gov.br> wrote in message
news:074f01c3b057$0dd5bae0$a001280a@.phx.gbl...
I was trying to create the table below with Query Analyzer
and got this warning:
Warning: The table 'PREPARACAO_ATIVIDADE' has been created
but its maximum row size (16432) exceeds the maximum
number of bytes per row (8060). INSERT or UPDATE of a row
in this table will fail if the resulting row length
exceeds 8060 bytes.
Is there a way to increase the default maximum row size
of 8060 in SQL server 2000?
Thanks for your help,
André Andraus Filho
CREATE TABLE [dbo].[PREPARACAO_ATIVIDADE] (
[COD_PREPARACAO] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[CODIGO_USUARIO] [varchar] (11) COLLATE
SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[COD_TIP_OBJ] [decimal](18, 0) NOT NULL ,
[COD_UA] [varchar] (6) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[COD_FP] [varchar] (17) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[COD_ATIVIDADE] [decimal](5, 0) NOT NULL ,
[COD_SUBNIVEL_ATIV] [decimal](5, 0) NOT NULL ,
[COD_PLANO] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[DATINI_ABRAG] [datetime] NOT NULL ,
[DATFIM_ABRAG] [datetime] NOT NULL ,
[CUSTO_PREV_TRANSP] [varchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[DATINI_PREP_ATIV] [datetime] NULL ,
[DATINI_PREVISAO] [datetime] NULL ,
[CUSTO_PREV_DIARIA] [varchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[COMENTARIO_DIRETOR] [varchar] (8000) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[DATFIM_PREVISAO] [datetime] NULL ,
[CENTRO_RESPONSAVEL] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[SIT_DIR_CENTRO] [decimal](1, 0) NULL ,
[JUSTIF_PONTO] [varchar] (8000) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[SIT_DCI_G] [decimal](1, 0) NULL ,
[COD_CENTRO_RESP_ATIV] [varchar] (6) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[COD_CONSIG] [varchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[OUTROS] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL
) ON [PRIMARY]
GO|||Telling you to change the datatype in order to avoid the error may be a
short term fix but not a complete answer. I would contact them again and
find out when they intend to provide a hot fix to really solve your problem.
--
Andrew J. Kelly
SQL Server MVP
"André A. Filho" <aafandraus@.fazenda.sp.gov.br> wrote in message
news:03e701c3b281$45c3efe0$a001280a@.phx.gbl...
I had already done the change of all varchar(8000) to text
datatype , but it doesn´t resolve , because with this , I
got an Access Violation error when trying to update a text
column inside a stored procedure.
I´ve contacted Microsoft and they told me to change the
datatype in order to solve this AV error.
The columns that are set to varchar(8000) are reports
entered by the user , about requests and contracts.
They are free text with at least 4200 bytes.
Do you have any idea on how to solve it ?
Thanks in advance,
André
>--Original Message--
>You can't make it larger than 8060 bytes but you can
change some of the
>varchar(8000)'t to Text datatypes.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"André Andraus Filho" <aafandraus@.fazenda.sp.gov.br>
wrote in message
>news:074f01c3b057$0dd5bae0$a001280a@.phx.gbl...
>I was trying to create the table below with Query Analyzer
>and got this warning:
>Warning: The table 'PREPARACAO_ATIVIDADE' has been created
>but its maximum row size (16432) exceeds the maximum
>number of bytes per row (8060). INSERT or UPDATE of a row
>in this table will fail if the resulting row length
>exceeds 8060 bytes.
>Is there a way to increase the default maximum row size
>of 8060 in SQL server 2000?
>Thanks for your help,
>André Andraus Filho
>
>CREATE TABLE [dbo].[PREPARACAO_ATIVIDADE] (
>[COD_PREPARACAO] [varchar] (9) COLLATE
>SQL_Latin1_General_CP1_CI_AI NOT NULL ,
>[CODIGO_USUARIO] [varchar] (11) COLLATE
>SQL_Latin1_General_CP1_CI_AI NOT NULL ,
>[COD_TIP_OBJ] [decimal](18, 0) NOT NULL ,
>[COD_UA] [varchar] (6) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[COD_FP] [varchar] (17) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[COD_ATIVIDADE] [decimal](5, 0) NOT NULL ,
>[COD_SUBNIVEL_ATIV] [decimal](5, 0) NOT NULL ,
>[COD_PLANO] [varchar] (5) COLLATE
>SQL_Latin1_General_CP1_CI_AI NOT NULL ,
>[DATINI_ABRAG] [datetime] NOT NULL ,
>[DATFIM_ABRAG] [datetime] NOT NULL ,
>[CUSTO_PREV_TRANSP] [varchar] (20) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[DATINI_PREP_ATIV] [datetime] NULL ,
>[DATINI_PREVISAO] [datetime] NULL ,
>[CUSTO_PREV_DIARIA] [varchar] (20) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[COMENTARIO_DIRETOR] [varchar] (8000) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[DATFIM_PREVISAO] [datetime] NULL ,
>[CENTRO_RESPONSAVEL] [varchar] (100) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[SIT_DIR_CENTRO] [decimal](1, 0) NULL ,
>[JUSTIF_PONTO] [varchar] (8000) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[SIT_DCI_G] [decimal](1, 0) NULL ,
>[COD_CENTRO_RESP_ATIV] [varchar] (6) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[COD_CONSIG] [varchar] (20) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[OUTROS] [varchar] (100) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL
>) ON [PRIMARY]
>GO
>
>
>
>.
>

Row Size Exceeds - Help!

Hi ,
Can any one of you tell why the below warning message
appears. I have found this when i created a table. Also I
would like to know what are the side effects on this table
& DB, because of this warning.
Warning: The table 'RawMailList' has been created but its
maximum row size (13415) exceeds the maximum number of
bytes per row (8060). INSERT or UPDATE of a row in this
table will fail if the resulting row length exceeds 8060
bytes.
Best Regards
ThirumalSQL Server has a MAX row length of 8060 bytes. You can define a table that
exceeds this as you have done but should you UPDATE/INSERT and go over 8060
bytes in total then that INSERT/UPDATE will fail.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Thirumal" <treddym@.hotmail.com> wrote in message
news:05c701c33eda$39f0a250$a301280a@.phx.gbl...
> Hi ,
> Can any one of you tell why the below warning message
> appears. I have found this when i created a table. Also I
> would like to know what are the side effects on this table
> & DB, because of this warning.
> Warning: The table 'RawMailList' has been created but its
> maximum row size (13415) exceeds the maximum number of
> bytes per row (8060). INSERT or UPDATE of a row in this
> table will fail if the resulting row length exceeds 8060
> bytes.
> Best Regards
> Thirumal|||Thirumal,
You probably have several variable-length columns--type varchar() or
nvarchar(), in each row. For example, if you have three varchar(8000)
columns in a single row, they could account for 24,000 bytes of data in
that row if each contained an 8000-byte string. So long as all the data
in any single row does not exceed 8060 bytes, there won't be a problem,
but if you tried to update the varchar() strings to the point where the row
would contain more than 8060 bytes in all, you would get an error.
The warning is just that, a warning. There is no problem at all so long
as you keep each complete row of the table within the 8060 byte limit.
By the way, the reason for this restriction is that SQL Server uses data
pages of 8K bytes (8192 bytes), and the architecture does not allow a
row to span more than one data page. 8060 bytes of data, together with
the additional information that must be on a data page, fits on one 8K page.
More information simply doesn't fit.
Steve Kass
Drew University
Thirumal wrote:
>Hi ,
>Can any one of you tell why the below warning message
>appears. I have found this when i created a table. Also I
>would like to know what are the side effects on this table
>& DB, because of this warning.
>Warning: The table 'RawMailList' has been created but its
>maximum row size (13415) exceeds the maximum number of
>bytes per row (8060). INSERT or UPDATE of a row in this
>table will fail if the resulting row length exceeds 8060
>bytes.
>Best Regards
>Thirumal
>|||Hi Allan and Steve,
Thanks a ton for clearing my doubts!!
Warm Regards
Thirumal
>--Original Message--
>Thirumal,
> You probably have several variable-length columns--type
varchar() or
>nvarchar(), in each row. For example, if you have three
varchar(8000)
>columns in a single row, they could account for 24,000
bytes of data in
>that row if each contained an 8000-byte string. So long
as all the data
>in any single row does not exceed 8060 bytes, there won't
be a problem,
>but if you tried to update the varchar() strings to the
point where the row
>would contain more than 8060 bytes in all, you would get
an error.
> The warning is just that, a warning. There is no
problem at all so long
>as you keep each complete row of the table within the
8060 byte limit.
> By the way, the reason for this restriction is that SQL
Server uses data
>pages of 8K bytes (8192 bytes), and the architecture does
not allow a
>row to span more than one data page. 8060 bytes of data,
together with
>the additional information that must be on a data page,
fits on one 8K page.
>More information simply doesn't fit.
>Steve Kass
>Drew University
>Thirumal wrote:
>>Hi ,
>>Can any one of you tell why the below warning message
>>appears. I have found this when i created a table. Also
I
>>would like to know what are the side effects on this
table
>>& DB, because of this warning.
>>Warning: The table 'RawMailList' has been created but
its
>>maximum row size (13415) exceeds the maximum number of
>>bytes per row (8060). INSERT or UPDATE of a row in this
>>table will fail if the resulting row length exceeds 8060
>>bytes.
>>Best Regards
>>Thirumal
>>
>.
>

row length exceeds 8060 bytes

Hi All,

I have created a table in sql server 2000 where at the time of creating
it, the row size excced 8K. I understand why I get the warning below:

The table 'tbl_detail' has been created but its maximum row size
(12367) exceeds the maximum number of bytes per row (8060). INSERT or
UPDATE of a row in this table will fail if the resulting row length
exceeds 8060 bytes.

However, when I call a stored procedure from my ASP Code, which returns
me this warning, my ASP page displays the warning and does not move to
the next line.

What can I do not to get this warning? How do I turn off warning
messages? I tried to wrap my stored procedure call code within SET
NOCOUNT ON and SET NOCOUNT OFF but that didn't help.

Any help would be really appreciated,
Thanks,
BorisYou know, I don't know how to turn errors off; wouldn't it be a better
idea to handle the error, rather than turning it off?

Another question I would have is why ignore the error; if your dataset
is that large, have you considered a 1-to-1 join, or perhaps replacing
some of your varchar fields with text?

Just asking.|||Thanks for quick reply,

Well, chaning the table structure would be a lot more difficult, since
that table name is used in a lots of places and within many stored
procedures.

I want to keep the error on, since I am handling errors. It is the
warning that I want to ignore. Now I don't know if errors and warnings
are treated and returned in the same way or not.

Either way, if I don't turn off the warning, how do I handle that
warning? Is there a warning # or something I can check?

Thanks in advance,
Boris

Stu wrote:
> You know, I don't know how to turn errors off; wouldn't it be a better
> idea to handle the error, rather than turning it off?
> Another question I would have is why ignore the error; if your dataset
> is that large, have you considered a 1-to-1 join, or perhaps replacing
> some of your varchar fields with text?
> Just asking.|||Boris (supermanreloaded@.gmail.com) writes:
> I have created a table in sql server 2000 where at the time of creating
> it, the row size excced 8K. I understand why I get the warning below:
> The table 'tbl_detail' has been created but its maximum row size
> (12367) exceeds the maximum number of bytes per row (8060). INSERT or
> UPDATE of a row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> However, when I call a stored procedure from my ASP Code, which returns
> me this warning, my ASP page displays the warning and does not move to
> the next line.
> What can I do not to get this warning? How do I turn off warning
> messages? I tried to wrap my stored procedure call code within SET
> NOCOUNT ON and SET NOCOUNT OFF but that didn't help.

You cannot turn off the warning on the SQL Server side.

I am a little surprised that ASP stops on the warning. Usually ADO
swallows informational messages completely. You could customize the
error handler in the ASP code, to check on the error number, and
ignore this message.

However, there is something fishy here. If the table already exists,
running the stored procedure should not give you the warning. So I
suspect one three things:

1) You are creating a temp table in the SP which also possibly could
exceed 8K. In this case, you could as a last resort split up the
temp table into two.
2) You insert data that exceeds 8K, so you actually get an error, not
a warning.
3) You are dropping and recreating tbl_detail. Dynamically changing the
schema is usuaally poor design.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,

1 and 2 are not true, however, I am creating and dropping table
dynamically. And when my stored procedure tries to an update operation
on that temp_detail table, it issues the warning. Now the table does
get updated, as I have tested it. When I run the stored procedure
outside ASP (in query analyzer), I see the same message. Its just that
it does not fail. The ASP Code however stops at the warning message,
which is surprising to me too.

Since I can not turn off that warning message, I guess I need to do the
error handling, or warning handling in my ASP code. I just need to know
how. If you know how can I do that, please let me know. Any help would
be really appreciated.

As far as changing the table structure and spiliting in two tables, it
is almost impossible. That table name has been used at many different
places. Everyone would have to change the logic in that case.

Thanks,
Boris

Erland Sommarskog wrote:
> Boris (supermanreloaded@.gmail.com) writes:
> > I have created a table in sql server 2000 where at the time of creating
> > it, the row size excced 8K. I understand why I get the warning below:
> > The table 'tbl_detail' has been created but its maximum row size
> > (12367) exceeds the maximum number of bytes per row (8060). INSERT or
> > UPDATE of a row in this table will fail if the resulting row length
> > exceeds 8060 bytes.
> > However, when I call a stored procedure from my ASP Code, which returns
> > me this warning, my ASP page displays the warning and does not move to
> > the next line.
> > What can I do not to get this warning? How do I turn off warning
> > messages? I tried to wrap my stored procedure call code within SET
> > NOCOUNT ON and SET NOCOUNT OFF but that didn't help.
> You cannot turn off the warning on the SQL Server side.
> I am a little surprised that ASP stops on the warning. Usually ADO
> swallows informational messages completely. You could customize the
> error handler in the ASP code, to check on the error number, and
> ignore this message.
> However, there is something fishy here. If the table already exists,
> running the stored procedure should not give you the warning. So I
> suspect one three things:
> 1) You are creating a temp table in the SP which also possibly could
> exceed 8K. In this case, you could as a last resort split up the
> temp table into two.
> 2) You insert data that exceeds 8K, so you actually get an error, not
> a warning.
> 3) You are dropping and recreating tbl_detail. Dynamically changing the
> schema is usuaally poor design.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Boris (supermanreloaded@.gmail.com) writes:
> 1 and 2 are not true, however, I am creating and dropping table
> dynamically.

So this where you would concentrate on changing things. I would
never permit tables being created dynamically in databases I am
responsible for.

If all you want to do is to clear out the table, just use TRUNCATE
TABLE instead.

But what happens if two instances of that ASP page runs at the same
time.

> Since I can not turn off that warning message, I guess I need to do the
> error handling, or warning handling in my ASP code. I just need to know
> how. If you know how can I do that, please let me know. Any help would
> be really appreciated.

You are likely to know more ASP than I do, because I know nothing about
ASP. (It's a tree, isn't it? :-)

But assuming that you use ADO as the client library, you can check
the Errors collection for errors. The Error object has a .Number
property, and this is the error number from SQL Server. Which number
you should check for, I don't know, but that's very easy to find
out.

If you have more detailed questions about ASP programming, you are
definitely better of in an ASP forum.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Cant the table be split into two, then create a VIEW to join the two
tables. If you use the current table's name as the view, then the other
procedures wont know the difference?
I could be way off base..
hth