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
>
>
>
>.
>
Showing posts with label analyzer. Show all posts
Showing posts with label analyzer. Show all posts
Friday, March 23, 2012
Wednesday, March 7, 2012
Rounding real number
Dear All,
I've a problem when input real number. If I key in 6.5, on query
analyzer, the record show 6.499999999 and so.
Does anybody known the solution?
Thanks very much
MichaelDon't use float or real. These are "approximate" numerical datatype (when working with 10 base
systems as we humans tend to do). Use numeric or decimal instead.
http://www.aspfaq.com/show.asp?id=2477
http://www.aspfaq.com/show.asp?id=2503
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer, the record show
> 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael|||Yap
What is your SQL Server's version
DECLARE @.d DECIMAL(18,1),@.w REAL
SET @.d=6.5
SET @.w=6.5
SELECT @.d,@.w
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer,
> the record show 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael
I've a problem when input real number. If I key in 6.5, on query
analyzer, the record show 6.499999999 and so.
Does anybody known the solution?
Thanks very much
MichaelDon't use float or real. These are "approximate" numerical datatype (when working with 10 base
systems as we humans tend to do). Use numeric or decimal instead.
http://www.aspfaq.com/show.asp?id=2477
http://www.aspfaq.com/show.asp?id=2503
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer, the record show
> 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael|||Yap
What is your SQL Server's version
DECLARE @.d DECIMAL(18,1),@.w REAL
SET @.d=6.5
SET @.w=6.5
SELECT @.d,@.w
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer,
> the record show 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael
Saturday, February 25, 2012
Rounding a real number
Hello,
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
HaraldREAL is an inexact numeric type. If you want more precise results then you
should use a precise type such as NUMERIC. Either change the column or CAST
it. On the other hand if you just want to format the value to a certain
number of decimals that's probably easier to do in your client-side app.
--
David Portas
SQL Server MVP
--|||Hello,
I understand, that REAL is not an exact numeric type.
But I think this should work (depth is REAL type):
Query Analyzer:
update dims set depth = 4.4
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
Harald|||This is a multi-part message in MIME format.
--=_NextPart_000_0053_01C4D3E5.B0DEA7D0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=3DCAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald
--=_NextPart_000_0053_01C4D3E5.B0DEA7D0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello,
it works in a SELECT statement, but not =in an UPDATE statement.
Update dims Set depth=3DCAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald
--=_NextPart_000_0053_01C4D3E5.B0DEA7D0--|||> it works in a SELECT statement, but not in an UPDATE statement.
If you put CAST(4.4 AS NUMERIC(9,2)) in a SELECT statement you are
outputting a NUMERIC column not a REAL. Again, REAL is NOT exact. You CANNOT
expect to control precisely the value of the digits to the right of the point
with a REAL. If this is a problem for you then you shouldn't be using REAL.
--
David Portas
SQL Server MVP
--|||Harald,
The data type REAL cannot store the value 4.4. The closest REAL value to
4.4 is what you are seeing. You wouldn't expect an INTEGER column to hold
the value 1.3, and it's the same story here. REAL can only hold certain
numbers, and 4.4 is not one of them.
Steve Kass
Drew University
"Harald Witt" <hwitt@.novasoftware.de> wrote in message
news:OUlBBh90EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I understand, that REAL is not an exact numeric type.
> But I think this should work (depth is REAL type):
> Query Analyzer:
> update dims set depth = 4.4
> Select depth from dims
> Result: 4.4000001
> Any advise?
> Thanks in advance,
> Harald
>
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
HaraldREAL is an inexact numeric type. If you want more precise results then you
should use a precise type such as NUMERIC. Either change the column or CAST
it. On the other hand if you just want to format the value to a certain
number of decimals that's probably easier to do in your client-side app.
--
David Portas
SQL Server MVP
--|||Hello,
I understand, that REAL is not an exact numeric type.
But I think this should work (depth is REAL type):
Query Analyzer:
update dims set depth = 4.4
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
Harald|||This is a multi-part message in MIME format.
--=_NextPart_000_0053_01C4D3E5.B0DEA7D0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=3DCAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald
--=_NextPart_000_0053_01C4D3E5.B0DEA7D0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello,
it works in a SELECT statement, but not =in an UPDATE statement.
Update dims Set depth=3DCAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald
--=_NextPart_000_0053_01C4D3E5.B0DEA7D0--|||> it works in a SELECT statement, but not in an UPDATE statement.
If you put CAST(4.4 AS NUMERIC(9,2)) in a SELECT statement you are
outputting a NUMERIC column not a REAL. Again, REAL is NOT exact. You CANNOT
expect to control precisely the value of the digits to the right of the point
with a REAL. If this is a problem for you then you shouldn't be using REAL.
--
David Portas
SQL Server MVP
--|||Harald,
The data type REAL cannot store the value 4.4. The closest REAL value to
4.4 is what you are seeing. You wouldn't expect an INTEGER column to hold
the value 1.3, and it's the same story here. REAL can only hold certain
numbers, and 4.4 is not one of them.
Steve Kass
Drew University
"Harald Witt" <hwitt@.novasoftware.de> wrote in message
news:OUlBBh90EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I understand, that REAL is not an exact numeric type.
> But I think this should work (depth is REAL type):
> Query Analyzer:
> update dims set depth = 4.4
> Select depth from dims
> Result: 4.4000001
> Any advise?
> Thanks in advance,
> Harald
>
Subscribe to:
Posts (Atom)