November 24, 2008 at 4:29 am
We have an application from a external provider and we have getting this error in our company, when we execute the next sentence:
INSERT INTO R110PresupBaseAgg (CierreCod, CentroCod, BalanceCod, FechaProyCod, MesCod,
BaseCalculoCode, SaldoMedioAggIm, SaldoFinalAggIm, TipoMedPndPrc, TipoFinalPndPrc,
TTIMedPndPrc, AjtBaseCalculo)
SELECT E200EscFechas.CierreCod, R100PresupBaseDtl.CentroCod, R100PresupBaseDtl.BalanceCod,
R100PresupBaseDtl.FechaProyCod, E200EscFechas.MesCod, R100PresupBaseDtl.BaseCalculoCode,
SUM(R100PresupBaseDtl.SaldoMedioIm) AS SaldoMedioAggIm, SUM(R100PresupBaseDtl.SaldoFinalIm)
AS SaldoFinalAggIm, SUM(TipoMedPrc*SaldoMedioIm) AS TipoMedPndPrc,
SUM(TipoFinalPrc*SaldoFinalIm) AS TipoFinalPndPrc, SUM(TTIMedPrc*SaldoMedioIm) AS
TTIMedPndPrc, SUM(0*SaldoMedioIm) AS AjtBaseCalculo
FROM R100PresupBaseDtl LEFT JOIN E200EscFechas ON
(R100PresupBaseDtl.FechaProyCod=E200EscFechas.FechaPrevCod)
GROUP BY E200EscFechas.CierreCod, R100PresupBaseDtl.CentroCod,
R100PresupBaseDtl.BalanceCod, R100PresupBaseDtl.FechaProyCod, E200EscFechas.MesCod,
R100PresupBaseDtl.BaseCalculoCode
HAVING (((E200EscFechas.CierreCod)='OCT08B'))
We have seen in WWW that this error is a bug in SQL 2000, and there is a SP that solve it.
But we have tried this sentence in 2 servers with the SP4 installed, and we have get the same error.
[
a) the fisrt one is: Enterprise Edition, NT 5.0 and version: 8.00.2187 (SP4)
b) the second one is: Enterprise Edition, NT 5.0 and version: 8.00.2039 (SP4)
]
Anyone can help us?
Thank you.
The DDL for the tables is:
--TABLE R100PresupBaseDtl
-------------------------------
CREATE TABLE [dbo].[R100PresupBaseDtl] (
[CentroCod] [char] (4) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[BalanceCod] [char] (4) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[PyGCod] [char] (4) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[FechaProyCod] [smallint] NOT NULL ,
[SaldoMedioIm] [float] NULL ,
[SaldoFinalIm] [float] NULL ,
[TipoMedPrc] [float] NULL ,
[TipoFinalPrc] [float] NULL ,
[TTIMedPrc] [float] NULL ,
[BaseCalculoCode] [smallint] NULL ,
[Id] [int] NOT NULL
) ON [DMPYC_FG_Datos]
GO
ALTER TABLE [dbo].[R100PresupBaseDtl] WITH NOCHECK ADD
CONSTRAINT [X1R100PresupBaseDtl] PRIMARY KEY CLUSTERED
(
[FechaProyCod],
[Id]
) WITH FILLFACTOR = 90 ON [DMPYC_FG_Datos]
GO
ALTER TABLE [dbo].[R100PresupBaseDtl] ADD
CONSTRAINT [DF__R100Presu__Fecha__02084FDA] DEFAULT (0) FOR [FechaProyCod],
CONSTRAINT [DF__R100Presu__Saldo__02FC7413] DEFAULT (0) FOR [SaldoMedioIm],
CONSTRAINT [DF__R100Presu__Saldo__03F0984C] DEFAULT (0) FOR [SaldoFinalIm],
CONSTRAINT [DF__R100Presu__TipoM__04E4BC85] DEFAULT (0) FOR [TipoMedPrc],
CONSTRAINT [DF__R100Presu__TipoF__05D8E0BE] DEFAULT (0) FOR [TipoFinalPrc],
CONSTRAINT [DF__R100Presu__TTIMe__06CD04F7] DEFAULT (0) FOR [TTIMedPrc],
CONSTRAINT [DF__R100Presu__BaseC__07C12930] DEFAULT (0) FOR [BaseCalculoCode],
CONSTRAINT [DF__R100PresupBa__Id__08B54D69] DEFAULT (0) FOR [Id]
GO
-- TABLE E200EscFechas
----------------------------
CREATE TABLE [dbo].[E200EscFechas] (
[CierreCod] [char] (10) COLLATE SQL_Latin1_General_CP850_BIN NOT NULL ,
[FechaPrevCod] [smallint] NOT NULL ,
[Fecha] [datetime] NULL ,
[MesCod] [smallint] NULL
) ON [DMPYC_FG_Datos]
GO
ALTER TABLE [dbo].[E200EscFechas] WITH NOCHECK ADD
CONSTRAINT [X1E200EscFechas] PRIMARY KEY CLUSTERED
(
[CierreCod],
[FechaPrevCod]
) WITH FILLFACTOR = 90 ON [DMPYC_FG_Datos]
GO
ALTER TABLE [dbo].[E200EscFechas] ADD
CONSTRAINT [DF__E200EscFe__Fecha__440B1D61] DEFAULT (0) FOR [FechaPrevCod],
CONSTRAINT [DF__E200EscFe__MesCo__44FF419A] DEFAULT (0) FOR [MesCod]
GO
CREATE INDEX [X2E200EscFechas] ON [dbo].[E200EscFechas]([FechaPrevCod]) WITH FILLFACTOR = 90 ON [DMPYC_FG_Indices]
GO
-- TABLE R110PresupBaseAgg
---------------------------------
CREATE TABLE [dbo].[R110PresupBaseAgg] (
[CierreCod] [char] (10) COLLATE SQL_Latin1_General_CP850_BIN NOT NULL ,
[CentroCod] [char] (4) COLLATE SQL_Latin1_General_CP850_BIN NOT NULL ,
[BalanceCod] [char] (4) COLLATE SQL_Latin1_General_CP850_BIN NOT NULL ,
[FechaProyCod] [smallint] NOT NULL ,
[MesCod] [smallint] NULL ,
[BaseCalculoCode] [smallint] NOT NULL ,
[SaldoMedioAggIm] [float] NULL ,
[SaldoFinalAggIm] [float] NULL ,
[TipoMedPndPrc] [float] NULL ,
[TipoFinalPndPrc] [float] NULL ,
[TTIMedPndPrc] [float] NULL ,
[AjtBaseCalculo] [float] NULL
) ON [DMPYC_FG_Datos]
GO
CREATE CLUSTERED INDEX [X2R110PresupBaseAgg] ON [dbo].[R110PresupBaseAgg]([BaseCalculoCode]) WITH FILLFACTOR = 90 ON [DMPYC_FG_Datos]
GO
ALTER TABLE [dbo].[R110PresupBaseAgg] ADD
CONSTRAINT [X1R110PresupBaseAgg] PRIMARY KEY NONCLUSTERED
(
[CierreCod],
[CentroCod],
[BalanceCod],
[FechaProyCod],
[BaseCalculoCode]
) WITH FILLFACTOR = 90 ON [DMPYC_FG_Indices]
GO
CREATE INDEX [X4R110PresupBaseAgg] ON [dbo].[R110PresupBaseAgg]([FechaProyCod]) WITH FILLFACTOR = 90 ON [DMPYC_FG_Indices]
GO
CREATE INDEX [X3R110PresupBaseAgg] ON [dbo].[R110PresupBaseAgg]([AjtBaseCalculo]) WITH FILLFACTOR = 90 ON [DMPYC_FG_Indices]
GO
November 24, 2008 at 4:59 am
First, I'll give 3 little whines...
- You made tables in filegroups different than PRIMARY. If you do this, please create those filegroups beforehand.
- Please add after the DDL all necessary code to remove everything you created in this script.
- Post together with your DDL, also some sample data. It works fine for me now, since there's not any data in the tables.
Except from that, I see all the data is in float items. If you have any chance to avoid using these in your result table R110PresupBaseAgg, do that. So you might want to explicit convert/cast float into numeric in your insert statement, something like this:
CAST(SaldoMedioAggIm as NUMERIC(25,12))
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply